MySQL中的事件调度基础学习教程
经常需要有一些定时任务在MySQL表上执行，例如统计、迁移、删除无用数据等。之前的作法是利用Linuxcron定时运行脚本，但是发现这样的额外依赖有时并不方便，例如单机多实例部署时，就需要分别手动分别配置不同的cron任务，需要额外配置相应的用户和权限；新环境部署时容易遗漏cron任务等。
MySQL提供了EventScheduler，与Linux下的crontab类似，可以根据时间调度来运行任务，运行一次或多次。
完整的EventSchduler创建语句如下：
CREATE
[DEFINER={user|CURRENT_USER}]
EVENT
[IFNOTEXISTS]
event_name
ONSCHEDULEschedule
[ONCOMPLETION[NOT]PRESERVE]
[ENABLE|DISABLE|DISABLEONSLAVE]
[COMMENT'comment']
DOevent_body;
schedule:
ATtimestamp[+INTERVALinterval]…
|EVERYinterval
[STARTStimestamp[+INTERVALinterval]…]
[ENDStimestamp[+INTERVALinterval]…]
interval:
quantity{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|
WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|
DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}
一、调度Scheduler
MySQL中的调度可以是只运行一次，也可以指定时间间隔重复运行。其定义是在event定义的ONSCHEDULE子句中。该子句格式如下：
ONSCHEDULE ATtimestamp[+INTERVALinterval]… |EVERYinterval [STARTStimestamp[+INTERVALinterval]…] [ENDStimestamp[+INTERVALinterval]…]
其中，timestamp必须包括”年月日时分秒“，它参与表达式计算后，结果是datetime或者timestamp类型。
而时间间隔interval可以如下：
<数字>{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|
WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|
DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}
其含义很清晰，如YEAR年；QUARTER季度；YEAR_MONTH年+月；MINUTE_SECOND分钟+秒。
补充：
YEAR|QUARTER|MONTH|YEAR_MONTH后台都转换成MONTH，其他时间间隔都转换成SECOND
ONSCHEDULE中的时间使用创建时本会话中的时区信息time_zone，这个时区默认是服务端的全局time_zone，也可能后续手动更新掉。这些时间会转化成UTC时间，存储到mysql.event表中。
1.一次运行
AT直接指定时间，或者使用时间表达式计算得出确定的时间点。
示例：
AT'2006-02-1023:59:00′ 指定确切运行时间，本地时区。
ATcurrent_timestamp+INTERVAL'1:15′MINUTE_SECOND 指定1分15秒后运行。
2.多次运行
EVERY设置运行的时间间隔，这里不能再指定[+INTERVALinterval]。
指定STARTS、ENDS是可选的。
STARTS是指定重复运行的第一次是什么时候。不指定的情况下，会在事件创建时运行第一次，即等价于STARTSCURRENT_TIMESTAMP！
ENDS告知MySQL结束重复运行的时间点。不指定的情况下，MySQL会永远重复运行下去。
示例：
EVERY5WEEK 每5周运行一次，创建时运行第一次。
EVERY3DAYSTARTS'2013-12-409:10:00′ 从'2013-12-409:10:00′开始运行第一次，每隔3天运行一次。
EVERY2MONTHSTARTSCURRENT_TIMESTAMP+INTERVAL10MINUTEENDS'2014-12-3123:59:59′10分钟后开始到2014年底，每两个月运行一次。
二、事件Event
1.启用EventScheduler功能
Event是由一个特定的EventScheduler线程执行的，运行过程中可以通过showfullprocesslist查看其当前状态信息，如：
7384313 event_scheduler localhost [NULL] Daemon 3 Waitingonemptyqueue [NULL]
默认事件调度EventScheduler功能是未启用的，需要配置全局参数event_scheduler，本参数可以动态设置，即时生效。
event_scheduler有如下三种取值：
OFF/0关闭，默认值。不运行EventScheduler线程，也就无法进行事件调度。设置为ON可以立即启用。
ON/1启用。
DISABLED禁用。同样不运行EventScheduler线程。只有在MySQL服务启动时设置才有用。当event_scheduler是ON或者OFF时，不能在运行时设置event_scheduler为DISABLED。如果启动时配置了event-scheduler=DISABLED，则运行时就不能设置为ON/OFF。换句话中，可以在MySQL服务启动时设置为DISABLED，然后完全禁用了event_scheduler，不能动态调整。
所以，要启用event_scheduler，运行时执行：
setglobalevent_scheduler=on
要随MySQL服务一起启用，则在/etc/my.cnf中添加
[mysqld] event-scheduler=on
2.创建事件的语法
CREATE
[DEFINER={user|CURRENT_USER}]
EVENT
[IFNOTEXISTS]
event_name
ONSCHEDULEschedule
[ONCOMPLETION[NOT]PRESERVE]
[ENABLE|DISABLE|DISABLEONSLAVE]
[COMMENT'comment']
DOevent_body;
schedule:
ATtimestamp[+INTERVALinterval]...
|EVERYinterval
[STARTStimestamp[+INTERVALinterval]...]
[ENDStimestamp[+INTERVALinterval]...]
interval:
quantity{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|
WEEK|SECOND|YEAR_MONTH|DAY_HOUR|
DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|
HOUR_SECOND|MINUTE_SECOND}
参数详细说明：
DEFINER:定义事件执行的时候检查权限的用户。
ONSCHEDULEschedule:定义执行的时间和时间间隔。
ONCOMPLETION[NOT]PRESERVE:定义事件是一次执行还是永久执行，默认为一次执行，即NOTPRESERVE。
ENABLE|DISABLE|DISABLEONSLAVE:定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLEONSLAVE。
COMMENT'comment':定义事件的注释。
3.更改事件的语法
ALTER
[DEFINER={user|CURRENT_USER}]
EVENTevent_name
[ONSCHEDULEschedule]
[ONCOMPLETION[NOT]PRESERVE]
[RENAMETOnew_event_name]
[ENABLE|DISABLE|DISABLEONSLAVE]
[COMMENT'comment']
[DOevent_body]
4.删除事件的语法
DROPEVENT[IFEXISTS]event_name
5.Do子句
在Do子句中实现事件的具体逻辑，几乎所有可以在存储程序中运行的MySQL语句都可以在event中使用。
1）简单SQL示例：
CREATEEVENTe_hourly ONSCHEDULE EVERY1HOUR COMMENT‘Clearsoutsessionstableeachhour.' DO DELETEFROMsite_activity.sessions;
2）复杂SQL示例：
delimiter| CREATEEVENTe ONSCHEDULE EVERY5SECOND DO BEGIN DECLAREvINTEGER; DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGINEND; SETv=0; WHILEv<5DO INSERTINTOt1VALUES(0); UPDATEt2SETs1=s1+1; SETv=v+1; ENDWHILE; END| delimiter;
3）Do子句中SQL的限制
基本上Do中可以使用任何在存储程序(StoredRoutine)中允许的SQL语句，而存储程序中有些限制，event还有些额外的限制。
StoredRoutine中如下语句不允许：
- LOCKTABLES/UNLOCKTABLES
- LOADDATA与LOADTABLE
支持动态SQL(PREPARE,EXECUTE,DEAALOCATEPREPARE)！但是PREPARE本身有些语句不允许执行。
INSERTDELAYED不会生效
EVENT的限制：
如果Do子句中包含ALTEREVENT子句，虽然能够创建，但是运行时会出错。
不要在Do子句中使用SELECT或SHOW这样仅仅是查询的语句，因为其输出无法从外部获取到。可以使用SELECT…INTO这样的形式将查询结果保存起来。
5.查看EVENT
有如下方式可以查看event的信息：
mysql.event information_schema.events showevents showcreateevent
三、eventschedule其他注意点
MySQL保存了事件创建时的sql_mode作为其运行时的sql_mode;
如果在一个调度区间内任务没有处理完成，新的调度依然会生成，这样就会出现同时又多个任务在运行的情况。如果要避免多个任务同时存在，可以使用GET_LOCK()函数或者行锁、表锁。
四、 Mysql事件实战
测试环境
创建一个用于测试的test表：
CREATETABLE`test`( `id`int(11)NOTNULLAUTO_INCREMENT, `t1`datetimeDEFAULTNULL, `id2`int(11)NOTNULLDEFAULT'0', PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=106DEFAULTCHARSET=utf8
实战1
Ø 创建一个每隔3秒往test表中插入一条数据的事件，代码如下：
CREATEEVENTIFNOTEXISTStestONSCHEDULEEVERY3SECOND
ONCOMPLETIONPRESERVE
DOINSERTINTOtest(id,t1)VALUES('',NOW());
Ø 创建一个10分钟后清空test表数据的事件
CREATEEVENTIFNOTEXISTStest ONSCHEDULE ATCURRENT_TIMESTAMP+INTERVAL1MINUTE DOTRUNCATETABLEtest.aaa;
Ø 创建一个在2012-08-2300:00:00时刻清空test表数据的事件，代码如下：
CREATEEVENTIFNOTEXISTStest ONSCHEDULE ATTIMESTAMP'2012-08-2300:00:00' DOTRUNCATETABLEtest;
Ø 创建一个从2012年8月22日21点45分开始到10分钟后结束，运行每隔3秒往test表中插入一条数据的事件，代码如下：
CREATEEVENTIFNOTEXISTStestONSCHEDULEEVERY3SECOND
STARTS'2012-08-2221:49:00'
ENDS'2012-08-2221:49:00'+INTERVAL10MINUTE
ONCOMPLETIONPRESERVE
DOINSERTINTOtest(id,t1)VALUES('',NOW());
实战2
通常的应用场景是通过事件来定期的调用存储过程，下面是一个简单的示例：
创建一个让test表的id2字段每行加基数2的存储过程，存储过程代码如下：
DROPPROCEDUREIFEXISTStest_add; DELIMITER// CREATEPROCEDUREtest_add() BEGIN DECLARE1_idINTDEFAULT1; DECLARE1_id2INTDEFAULT0; DECLAREerror_statusINTDEFAULT0; DECLAREdatasCURSORFORSELECTidFROMtest; DECLARECONTINUEHANDLERFORNOTFOUNDSETerror_status=1; OPENdatas; FETCHdatasINTO1_id; REPEAT SET1_id2=1_id2+2; UPDATEtestSETid2=1_id2WHEREid=1_id; FETCHdatasINTO1_id; UNTILerror_status ENDREPEAT; CLOSEdatas; END //
事件设置2012-08-2200:00:00时刻开始运行，每隔1调用一次存储过程，40天后结束，代码如下：
CREATEEVENTtestONSCHEDULEEVERY1DAY STARTS'2012-08-2200:00:00' ENDS'2012-08-2200:00:00'+INTERVAL40DAY ONCOMPLETIONPRESERVEDO CALLtest_add();