MySQL事件调度器Event Scheduler

        数据库中经常有需要定时执行一些相关的任务的需求，在MSSQL中SQL Agnet，在Mysql5.1以前，一般需要通过OS的调度计划（windows的计划任务和linux的crontab）来实现，Mysql5.1以后增加了一个事件调度器，可以实现定时调度的功能。

一、使用权限 

       单独使用event调用SQL语句时，查看和创建需要用户具有event权限，调用该SQL语句时，需要用户具有执行该SQL的权限。Event权限的设置保存在mysql.user表和mysql.db表的Event_priv字段中。
 
       当event和procedure配合使用的时候，查看和创建存储过程需要用户具有create routine权限，调用存储过程执行时需要使用excute权限，存储过程调用具体的SQL语句时，需要用户具有执行该SQL的权限

        某个用户创建的event ，在该用户被删除后，将无法继续执行，event的定义用户名在information_schema.events表的DEFINER字段中。

二、启用Event Scheduler

查看当前是否已启用Event Scheduler的方法：
1)     SHOW VARIABLES LIKE 'event_scheduler';
2)     SELECT @@event_scheduler;
3)     SHOW PROCESSLIST;

启用Event Scheduler的方法如下：

1)     SET GLOBAL event_scheduler = 1 / ON;

2)     SET @@global.event_scheduler = 1 / ON;

键值1或者ON表示开启；0或者OFF表示关闭；

以上命令都是即时生效的，无需重启服务

开启事件计划以后，可以通过SHOWPROCESSLIST\G看到有一个相应的常驻进程：

三、创建Event

创建event的语法如下：

DELIMITER $$
-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    
CREATE /*[DEFINER = { user | CURRENT_USER }]*/EVENT `event_schema`.`event_name`
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 ;

说明：

event_schema：数据库名称

event_name ：event名称（event_schema.event_name 须唯一）；

schedule有两种形式 AT和EVERY

AT 时间戳，单次任务

EVERY，用来完成重复的计划任务。

如果指定了[STARTS 时间戳] [ENDS时间戳]，则是在指定的限期内，根据INTERVAL重复执行计划任务。

如果未指定则一直重复执行计划任务。

INTERVAL的时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

提示: 其他的时间单位也是合法的如：QUARTER,WEEK,YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND,MINUTE_SECOND，但不建议使用这些不标准的时间单位。

[ON COMPLETION [NOT] PRESERVE]：可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION  PRESERVE则不会drop掉 。

[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该计划事件。
DO sql_statement: 需要执行的sql语句。

四、启用/禁用/删除事件

ALTER EVENT evnet_name ENABLE;

ALTER EVENT evnet_name DISABLE;

DROP EVENT evnet_name ;

五、使用示例：  

mysql>create table test.t1 (id int,dt datetime);

mysql>insert into test.t1 select 1,now();

例1:

#每分钟往t1表中插入一次记录

DELIMITER $$
CREATE EVENT IF NOT EXISTS e_test1
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE
DO BEGIN
INSERT INTO t1 SELECT (SELECT MAX(id) FROM test.t1)+1 AS id ,NOW();
END$$
DELIMITER ;

例2:

#2天后删除id小于1000的记录

DELIMITER $$
CREATE EVENT IF NOT EXISTS e_test2
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 DAY
ON COMPLETION PRESERVE
DO BEGIN
DELETE FROM test.t1 WHERE id<1000;

END$$

DELIMITER ;

例3:

#每个月1号凌晨1点清空表

DELIMITER $$
CREATE EVENT IF NOT EXISTS e_test3
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)

ON COMPLETION PRESERVE
DO BEGIN
TRUNCATE TABLE test.t1;

END$$

DELIMITER ;

例4:

#2014年1月1日零点清空表

DELIMITER $$
CREATE EVENT IF NOT EXISTS e_test4
ON SCHEDULE AT ‘2014-01-01 00:00:00’

ON COMPLETION PRESERVE
DO BEGIN
TRUNCATE TABLE test.t1;

END$$

DELIMITER ;

例5:

#2天后开始，1个月后停止

DELIMITER $$
CREATE EVENT IF NOT EXISTS e_test5
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH

ON COMPLETION PRESERVE
DO BEGIN
TRUNCATE TABLE test.t1;

END$$

DELIMITER ;

Mysql Event 调度历史记录