mysql 定时任务从5.1版本开始引入,又叫做临时触发器,但是与普通触发器不同的是,普通触发器是通过操作数据表的每行数据而触发的事件,而它是通过时间点或者时间周期触发
先来看一下它的语法:
DELIMITER $$ -- SET GLOBAL event_scheduler = ON$$ -- required for event to execute but not create CREATE /* [DEFINER = { user | CURRENT_USER }] */ EVENT `event1` ON SCHEDULE /* uncomment the example below you want to use */ -- scheduleexample 1: run once -- AT 'YYYY-MM-DD HH:MM.SS'/CURRENT_TIMESTAMP { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } -- scheduleexample 2: run at intervals forever after creation -- EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] -- scheduleexample 3: specified start time, end time and interval for execution /* EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] STARTS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1[HOUR|MONTH|WEEK|DAY|MINUTE|...] } ENDS CURRENT_TIMESTAMP/'YYYY-MM-DD HH:MM.SS' { + INTERVAL 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...] } */ /* [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] */ DO BEGIN (sql_statements) END $$ DELIMITER ;
基本上通过上面的语法说明已经了解到如何使用了,下面针对每种方式可以再写个小栗子
1,在某个时间点触发(执行一次):
DELIMITER $$ CREATE EVENT `bdhp`.`e_e1` ON SCHEDULE /* 只执行一次 */ /* AT '2015-02-03 16:07.00' *//* 某一时间点 */ /* AT '2015-02-03 16:47.20' + INTERVAL 40 second *//* 某一时间点 + 一段时间 */ AT CURRENT_TIMESTAMP + INTERVAL 40 SECOND /* 当前时间点 + 一段时间 */ DO BEGIN INSERT INTO t_sys_log(sn) VALUES ( ' 55555 ' ); END $$ DELIMITER ;
2,按周期执行,无限次数
DELIMITER $$ CREATE EVENT `e_e2` ON SCHEDULE EVERY 1 MINUTE DO BEGIN INSERT INTO t_sys_log(sn) VALUES ( ' 55555 ' ); END $$ DELIMITER ;
3,在某一时间段内,按周期执行(ends 不写则表示从某一时间点开始执行)
DELIMITER $$ CREATE EVENT `e_e3` ON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP ENDS ' 2015-02-03 16:15.00 ' DO BEGIN INSERT INTO t_sys_log(sn, USER_NAME ) VALUES ( ' test ' , ' ttt-55 ' ); END $$ DELIMITER ;