http://blog.csdn.net/mer1234567/article/details/7514855
http://blog.csdn.net/zlp5201/article/details/38309095
注意点:1,5
1,Mysql配置文件中my.ini添加:event_scheduler = ON (mysql启动后自动开启event调度,避免数据库重启或宕机导致的调度停止)
2,临时开关:SET GLOBAL event_scheduler = 1/0;
3:查看已开启的调度:SHOW VARIABLES LIKE 'event_scheduler';
4,语法:
1 CREATE EVENT [IFNOT EXISTS] event_name
2 ONSCHEDULE schedule
3 [ONCOMPLETION [NOT] PRESERVE]
4 [ENABLE | DISABLE]
5 [COMMENT 'comment']
6 DO sql_statement;
schedule:
AT TIMESTAMP [+ INTERVAL INTERVAL]
| EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
INTERVAL:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
5:测试:如需要调整系统时间进行测试,
需要顺序执行:1,调整系统时间;2,创建事件调度。(如先创建调度,在修改时间,则修改的系统时间无效。推测调度器创建的时候会锁定自己的时间戳,然后根据设定的时间间隔来进行调度,后修改的系统时间对调度无影像)
6:例子:每天11:59:59 执行插入操作:
a:配置文件添加 event_scheduler = ON
b:create event if not exists e_test
on schedule EVERY 1 day
STARTS TIMESTAMP '2007-07-20 23:59:59' #设置当前时间点之前的时间,确保重启后自动执行。
on completion preserve
do
INSERT into temp(id,name,now_time) values(null,5,now());
7:删除:drop event s_test;
8:更多例子 :http://blog.csdn.net/mer1234567/article/details/7514855