drop procedure if existsmove_track_data; delimiter // create proceduremove_track_data() begin declare i int default 0; start transaction; 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_sdmp on 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; start transaction; set i = 0; while i < 10do set @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 variables like"group_concat_max_len"; ##################################################################################### call move_score_driving_track_data(); SELECT @@global.sort_buffer_size; SET GLOBAL group_concat_max_len=1024000; show variables like"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_sdmp on schedule every 1 second #starts timestamp '2014-07-30 10:00:00'do begin 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;
MySQL 存储过程 定时任务
免责声明:文章转载自《MySQL 存储过程 定时任务》仅用于学习参考。如对内容有疑问,请及时联系本站处理。
上篇基于AngularJs的上传控件-angular-file-uploadCSS命名规范下篇
宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=