mysql 存储过程 睡眠_MySQL 存储过程 定时任务

drop procedure if existsmove_track_data;

delimiter//

create proceduremove_track_data()begin

declare i int default 0;

starttransaction;set i = 1;case

when i = 1 then

select 'is 1';when i = 2 then

select 'is 2';else

select 'is 3';end case;commit;end;//call move_track_data();

delimiter ;select * from score_driving_track where trackid in( select trackid from score_driving_track where trackid % 10 = 1);insert into score_driving_track_0 select * from score_driving_track where trackid in( select trackid from score_driving_track where trackid % 10 =0);

####################################################################################################3

set time_zone = '+8:00';set GLOBAL event_scheduler = 1;usetest;drop event if existsupload_to_sdmp;

DELIMITER//

createevent upload_to_sdmpon schedule every 1 week starts timestamp '2015-10-12 02:00:00'do call move_score_driving_track_data();drop procedure if existsmove_score_driving_track_data;

delimiter//

create proceduremove_score_driving_track_data()begin

declare i int default 0;declare t_error integer default 0;declare continue handler for sqlexception set t_error = 1;

starttransaction;set i = 0;while i < 10doset @sqlTrackids = concat('select group_concat(distinct trackid) into @trackids from score_driving_track where trackid % 10 =', i);select @sqlTrackids;prepare sqlTrackidsNormal from @sqlTrackids;executesqlTrackidsNormal;select @trackids;set @sqlInsert = concat('insert into score_driving_track_', i, 'select * from score_driving_track where find_in_set(trackid,''', @trackids, ''')');select @sqlInsert;prepare sqlInsertNormal from @sqlInsert;executesqlInsertNormal;set @sqlDelete = concat('delete from score_driving_track', 'where find_in_set(trackid ,''', @trackids, ''')');select @sqlDelete;prepare sqlDeleteNormal from @sqlDelete;

#executesqlDeleteNormal;set i = i + 1;end while;if t_error = 1 then

rollback;else

commit;end if;end;//call move_score_driving_track_data();SELECT @@global.sort_buffer_size;select * from score_driving_track where trackid >= 2270;select * fromscore_driving_track_0;select * fromscore_driving_track_1;select * fromscore_driving_track_2;select * fromscore_driving_track_3;select * fromscore_driving_track_4;select * fromscore_driving_track_5;select * fromscore_driving_track_6;select * fromscore_driving_track_7;select * fromscore_driving_track_8;select * fromscore_driving_track_9;delete fromscore_driving_track_0;delete fromscore_driving_track_1;delete fromscore_driving_track_2;delete fromscore_driving_track_3;delete fromscore_driving_track_4;delete fromscore_driving_track_5;delete fromscore_driving_track_6;delete fromscore_driving_track_7;delete fromscore_driving_track_8;delete fromscore_driving_track_9;SET GLOBAL group_concat_max_len=1024000;

show variableslike"group_concat_max_len";

#####################################################################################

call move_score_driving_track_data();SELECT @@global.sort_buffer_size;SET GLOBAL group_concat_max_len=1024000;

show variableslike"group_concat_max_len";select group_concat(distinct trackid) from score_driving_track where trackid % 10 = 0;SELECT @@global.group_concat_max_len;

show warnings;

#[mysqld]#explicit_defaults_for_timestamp=true

#group_concat_max_len=102400

select distinct trackid from score_driving_track where trackid % 10 = 0;select group_concat(distinct trackid) from score_driving_track where trackid % 10 = 0 and trackid < 50;

delimiter ;select concat('score_driving_track_', 1);select * fromscore_driving_track_0;delete fromscore_driving_track_0;

##############################################################################set time_zone = '+8:00';set GLOBAL event_scheduler = 1;usetest;drop event if existsupload_to_sdmp;

DELIMITER//

createevent upload_to_sdmpon schedule every 1 second #starts timestamp '2014-07-30 10:00:00'dobegin

insert into b values(3,'222');insert into b values(2,'222');end

//DELIMITER ;select * fromb;select group_concat(trackid) into @temp from score_driving_track where trackid < 100;select @temp;select * from score_driving_track where find_in_set(trackid, @temp);select REPLACE(@temp,'''''''','');select CHAR_LENGTH(@temp)-CHAR_LENGTH(REPLACE(@temp,'','')) + 1;select * from score_driving_track where trackid in (select group_concat(trackid) from score_driving_track where trackid < 50);select * from score_driving_track where find_in_set(trackid, (select group_concat(distinct trackid) from score_driving_track where trackid < 50));select * from users;