理解MySQL Event(事件)
2016-10-07 20:20:57 
阿炯
事件(event)是MySQL在相应的时间点调用过程完成指定的操作的数据库对象。一个事件可一次或周期性的调用，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。它与触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。事件调度器(Event Scheduler)是在MySQLv5.1.6中新增的一个功能，它相当于一个定时器，可以在指定的时间点执行一条SQL语句或一个语句块，也可以用于在固定间隔重复执行。事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务(如：Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。且这是MySQL内部执行机制，无需外部依赖。定期运行，无法直接调用，当然可在其中指定要调用的过程或函数，它们是可以手动调用的。
由create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度(event schedule)，表示事件何时启动以及按什么频率启动，第二部分是事件动作(event action )，这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地sql语句，也可以使一个存储过程或者benin...end语句块，这两种情况允许我们执行多条SQL。
一个事件可以是活动(打开)的或停止(关闭)的，活动意味着事件调度器检查事件动作是否必须调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次。
一、相关语法
1、创建语法
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
相关术语：
event_name ：创建的event名字(唯一确定的)，可以是任何合的MySql标识符，不能超64个字符。
ON SCHEDULE：计划任务。
schedule: 决定event的执行时间和频率(注意时间一定要是将来的时间，过去的时间会有警告)，有两种形式 AT和EVERY。
[ON COMPLETION [NOT] PRESERVE]：可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉。
[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节。
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改。
DO event_body: 需要执行的sql语句(可以是复合语句)。CREATE EVENT在存储过程中使用时合法的。
最基本的create event只需要三个部分(某事多久时间做一次)：
1.create event关键字以及一个event名称
2.on schedule子句
3.do子句
definer：说明该event的用户。服务器在执行该事件时，使用该用户来检查权限。默认用户为当前用户，即definer = current_user。如果明确指明了definer，则必须遵循如下规则：
1.如果没有super权限，唯一允许的值就是自己当前用户，而不能设置为其他用户。
2.如果具有super权限，则可以指定任意存在的用户。如果指定的用户不存在，则事件在执行时会报错。
if not exists：如果事件已经存在，则不会创建，也不会报错
on schedule子句指定何时执行该事件，以及如何执行该事件，这个要理解的比较充分：
1、at timestamp用于创建单次执行的事件。timestamp执行了事件执行的时间，必须包括日期和事件两部分，或者是一个datetime类型的表达式，如current_timestamp。如果指定的时间是过去的时间，则会产生一个warning。
如果要指定将来某个时间间隔，如从现在开始的三个半小时之后，则可以使用interval关键字: + INTERVAL '3:30' HOUR_MINUTE。interval关键字可以进行组合，如上例可以这样：+ INTERVAL 3 HOUR + INTERVAL 30 MINUTE。
2. every子句用于创建重复执行的事件。如果每分钟执行一次，则可以：EVERY 1 MINUTE。every子句可以指定一个开始事件和结束时间，通过STARTS和ENDS关键字来表示，例如：
EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK。
通常情况下，如果一个事件过期已过期，则会被立即删除。通过on completion preserve子句可以保留已过期的时间。ON COMPLETION NOT PRESERVE为默认值。默认情况下，事件一旦创建后就立即开始执行。当然也可以通过disable关键字来禁用该事件。
comment子句用于给事件添加注释。
do子句用于指示事件需要执行的操作，可以是一条语句，也可以是被begin...end包括的语句块。如果语句块中包含sql语句，则可能需要修改delimiter。
2、alter event语法
alter event语句可以修改事件的定义和属性。我们可以让一个事件成为停止的或者再次让它活动，也可以修改一个事件的名字或者整个调度。然而当一个使用 ON COMPLETION NOT PRESERVE 属性定义的事件最后一次执行后，事件直接就不存在了，不能修改。
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
alter event语句可以修改事件的一个或多个属性，DEFINER/ON SCHEDULE/ON COMPLETION PRESERVER/ENABLE/COMMENT/DO子句的语法与create event语句完全相同，唯一不同的是可以对事件重命名，使用RENAME TO子句，如：
ALTER EVENT OLDDB.MYEVENT RENAME TO FreeDB.MYEVENT;
事件的开启与关闭本质是使用ALTER语句修改已创建的事件，如关闭一个事件：
ALTER EVENT e_test ON COMPLETION PRESERVE ENABLE;
开启一个事件：
ALTER EVENT e_test ON COMPLETION PRESERVE DISABLE;
alter event test.event_by_minute
on schedule every 30 second
do insert into events_list values('freeoa_now', now());
3、drop event语法
该语句删除一个定义的事件
DROP EVENT [IF EXISTS] event_name
drop event if exists event_name
二、事件调度器的配置
所有事件都是在事件调度线程(event scheduler thread)里面执行的；当提及事件调度器时，通常指该线程。如果用户有PROCESS权限，可以通过SHOW PROCESSLIST命令来查看该线程及其状态：
mysql> show processlist\G
系统变量event_scheduler决定事件调度器是否启用。它有三个值：
OFF：停止事件调度器。事件调度线程停止运行，同时也不会显示在SHOW PROCESSLIST的输出中。所有的调度时间也不会被执行。OFF是event_scheduler的默认值。
当事件调度器停止后，可以通过设置event_scheduler为ON来启动它。
ON：启动时间调度器。事件调度线程开始运行，并执行所有的调度事件。此时可以通过SHOW PROCESSLIST命令的输出中看到该线程及其状态。通过设置event_scheduler为OFF可以停止该线程。
DISABLED：禁用事件调度器。SHOW PROCESSLIST命令的输出中看不到该线程。也不能通过设置event_scheduler为ON来启动该线程。如果要设置事件调度器为DISABLED，唯一的方法是在服务器启动时，使用如下选项：
--event-scheduler=DISABLED
或者在配置文件(/etc/my.cnf)的[mysqld]段中添加如下语句：
event_scheduler=DISABLED
不能在服务器运行时设置event_scheduler为DISABLED。通过如果在服务器启动时设置了event_scheduler为DISABLED，也不能动态改变event_scheduler的值为ON或OFF。
event_scheduler的值也支持数字形式：1为ON；0为OFF。示例如下：
set global event_scheduler = on|1
set @@global.event_scheduler = on|1
set global event_scheduler = off|0
set @@global.event_scheduler = off|0
另外要注意的是，启动服务器时如果指定了--skip-grant-tables选项，则event_scheduler则自动被设置为DISABLED。命令行或配置文件的设置都会被覆盖。
三、查看事件调度器
1、查询mysql数据库的event表：
mysql> select * from mysql.event \G
2、查询information_schema数据库的events表：
mysql> select * from information_schema.events \G
3、使用show create event命令可以查看事件创建信息(要进入到相应的库)
mysql> show create event evt_insert \G
4、使用show events命令。其语法格式如下：
SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]
可以查看某一个数据库中的所有事件，或者符合某一pattern的事件。
mysql> show events \G
有5个状态变量可以查看与event相关的操作：
Com_create_event：服务器启动后执行create event语句的次数
Com_alter_event：服务器启动后执行alter event语句的次数
Com_drop_event：服务器启动后执行drop event语句的次数
Com_show_create_event：服务器启动后执行show create event语句的次数
Com_show_events：服务器启动后执行show events语句的次数
可以通过如下语句来查看与event相关的所有状态变量：
mysql> show status like '%event%';
通过show events只能查看当前库中创建的事件，事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件可以查看到。如果两个事件需要在同一时刻调用，mysql会确定调用他们的顺序，如果要指定顺序，需要确保一个事件至少在另一个事件1秒后执行。对于递归调度的事件，结束日期不能在开始日期之前。select可以包含在一个事件中，然而他的结果消失了，就好像没执行过。
要保证创建的事件能正常执行，首先应该开启事件调度器，可以通过以下3种方式查看调度器状态：
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;
查看某个事件的执行情况：
SELECT * FROM information_schema.EVENTS;
以上会输出当关Schema中所有的事件信息，可以先通过DESC information_schema.EVENTS;查看输出字段，再查看所需要的信息。如只想看事件名及最后执行时间：
SELECT EVENT_NAME, LAST_EXECUTED FROM information_schema.EVENTS;
四、事件调度器的权限
事件的创建、修改、删除需要event权限。通过grant语句可以将某一个数据库的event权限赋予某一用户，如：
GRANT EVENT ON myschema.* TO freeoa@'192.168.8.%';
如果要赋予用户所有数据库的event权限，可以：
GRANT EVENT ON *.* TO freeoa@'192.168.8.%';
event权限属于数据库级别，因此不能将该权限用户表级别，否则报错：
mysql> GRANT EVENT ON myschema.mytable TO freeoa@'192.168.8.%';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
事件调度器在执行事件的语句块时，使用的是其定义者的权限。如果定义事件时使用了insert语句，但是该定义者并没有insert权限，则该事件执行时会报错，同时其insert失败。
查看information_schema.events表时会发现事件存在并且其状态为enabled，但其last_executed列为NULL
mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='freeoa_store_ts' AND EVENT_SCHEMA='myschema'\G
通过revoke语句类收回用户的event权限，如：
REVOKE EVENT ON myschema.* FROM freeoa@'192.168.8.%';
虽然收回了用户的event权限，但用户已经定义的事件并不会被删除或disable。通过如果删除一个用户，或者重命名一个用户，那么该用户定义的时间也不会自动删除，或者更改其定义者为新用户名。
用户的event权限存储在mysql.user和mysql.db两张表的event_priv字段中。event_priv字段的值为Y或N。N为默认值。只有当用户具有全局event权限(即grant event on *.*)时，mysql.user.event_priv字段值才为Y。对于数据库级别的event权限，grant语句会在mysql.db表中增加一条记录，并将其event_priv字段设为Y。用户不需要自己手动修改者两张表，因为grant和revoke语句会执行相同的操作。
五、提供一些示例
定期清空用户会话表，该表是Memory存储引擎，该表数据并不重要，带有临时性质，数据库重启后数据内容清空。但该表需要控制记录数量，因此需要自行定时清理这期数据。
创建存储过程：
delimiter //
CREATE DEFINER=`admin`@`192.168.20.%` PROCEDURE `freeoa`.`delete_cookie_user_record`()
BEGIN
delete from cookie_user where create_time < (now() - interval 2 month);
END//
delimiter ;
创建事件：
create event event_del_cookie_user_record ON SCHEDULE EVERY 1 DAY STARTS '2016-10-01 00:00:01' DO CALL delete_cookie_user_record();
一个最简单的示例，将myschema.mytable表的mycol列，每小时自增1：
CREATE EVENT myevent
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO UPDATE myschema.mytable SET mycol = mycol + 1;
这样，我们就创建一个名为myevent的事件，它会在事件创建后每小时执行一次。设置的执行规则等价于：
CREATE EVENT myevent
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO UPDATE myschema.mytable SET mycol = mycol + 1;
如果需要间隔一定时间再开启事务，如1天后开启：
CREATE EVENT myevent
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO UPDATE myschema.mytable SET mycol = mycol + 1;
DO执行的SQL可以是一个语句块，如：
DELIMITER //
CREATE EVENT e
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE v INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t1 VALUES (0);
UPDATE t2 SET s1 = s1 + 1;
SET v = v + 1;
END WHILE;
END //
DELIMITER ;
参考来源：
Chapter 20 Stored Programs and Views
该文章最后由 阿炯 于 2020-07-18 10:08:55 更新，目前是第 2 版。