MySQL数据库高级（八）——事件

崬城衞

MySQL数据库高级（八）——事件

一、事件简介

1、事件简介
　　事件是用来执行定时任务的一组SQL集，在时间到时会触发。
　　一个事件可调用一次，也可周期性的启动，由一个特定的事件调度器线程来管理的。
　　事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

2、事件的优点
　　A、对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
　　B、可以实现每秒钟执行一个任务，在一些对实时性要求较高的环境下非常实用。

3、事件的缺点
　　定时触发，不可以调用。

4、事件的适用场景
　　对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。

二、事件的操作

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;
　　

　　event_name ：事件名，不能超64个字符，名字必须是当前Dateabase中唯一的，同一个数据库不能有同名的event。创建事件时，可以同时指定Schema，语法结构为：schema_name.eventname。使用event常见的工作是创建表、插入数据、删除数据、清空表、删除表。为了规范事件命名，事件名称需具备描述整个事件的能力。建议命名规则如下：动作名称（INTO/FROM_）表名_TIME，如每天插入表中记录事件命名，insert_into_tablename_day。
　　ON SCHEDULE schedule：计划任务
　　schedule：调度规则，规定事件的执行时间与执行规则。决定event的执行时间和频率（时间必须是将来的时间，过去的时间会出错），有AT和EVERY两种形式。
　　语法结构如下：
　　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}
　　AT timestamp，用来完成单次的计划任务。
　　EVERY n quantity [STARTS timestamp] [ENDS timestamp]，用来完成重复的计划任务。
　　时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。
　　在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，时间单位是关键词：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。
　　AT timestamp一般用于只执行一次，一般可以使用当前时间加上延后的一段时间，例如：AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR；也可以定义一个时间常量，例如：AT '2006-02-10 23:59:00'；EVERY interval一般用于周期性执行，可以设定开始时间和结束时间。
　　ON COMPLETION [NOT] PRESERVE：事件到期后的操作，默认事件到期后会自动删除。如果想保留事件使用ON COMPLETION PRESERVE；如果不想保留事件，可以设置ON COMPLETION NOT PRESERVE。
　　[ENABLE | DISABLE]：参数Enable和Disable表示设定事件的状态。Enable表示系统将执行事件，Disable表示系统不执行事件。
　　[COMMENT 'comment']：增加注释，注释会出现在元数据中，存储在information_schema表的COMMENT列，最大长度为64个字节。'comment'表示将注释内容放在单引号之间，建议使用注释以表达更全面的信息。
　　event_body：事件体，可以是单行SQL语法，或是BEGIN……END语句块，或是存储过程。

2、事件调度器的开启
　　MySQL默认关闭事件调度器。
　　查看事件调度器信息
　　

show global variables like '%event_scheduler%';　　
SHOW PROCESSLIST;
　　

　　开启事件调度器
　　set global event_scheduler=ON;
　　在MySQL的my.cnf配置文件中修改。
　　event_scheduler = 1 #或者ON  

3、事件的查看
　　

SELECT * FROM mysql.event;　　SHOW EVENTS;
　　SELECT * FROM information_schema.events;
　　

4、删除事件
　　DROP EVENT [IF EXISTS] eventname;

5、禁用事件
　　ALTER EVENT eventname DSIABLE;

6、开启事件
　　ALTER EVENT eventname ENABLE;

7、事件的修改
　　

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]
　　

三、事件实例

1、每隔一定时间插入一次记录
　　创建一个表
　　CREATE TABLE event_table(timeline TIMESTAMP);
　　创建一个事件，每隔1秒插入一条记录
　　

CREATE EVENT insert_into_event_table_second　　
ON SCHEDULE EVERY 1 SECOND
　　
DO INSERT INTO schoolDB.event_table VALUES(CURRENT_TIMESTAMP);
　　

　　过一定时间查看插入记录
　　select * from event_table;
　　停止事件
　　alter event insert_into_event_table_second disable;
　　删除事件
　　drop event insert_into_event_table_second;

2、特定时间插入一条记录
　　创建一个事件在特定时间插入一条记录
　　

CREATE EVENT insert_into_event_table_time　　
ON SCHEDULE AT TIMESTAMP '2018-04-11 21:27:30'
　　
DO INSERT INTO schoolDB.event_table values(CURRENT_TIMESTAMP);
　　

　　在特定时间后查看插入记录，已经插入记录
　　select * from event_table;

3、特定时间清空表
　　创建一个事件，特定时间清空表，事件执行完后保留事件
　　

CREATE EVENT truncate_event_table_time　　
ON SCHEDULE AT TIMESTAMP '2018-04-11 21:35:00'
　　
ON COMPLETION PRESERVE
　　
DO TRUNCATE TABLE schoolDB.event_table;
　　

　　时间过后查询表的内容，已经清空
　　select * from event_table;

4、某个事件段内定时清空表
　　创建一个事件，5天后开启，每天定时清空表，一个月后停止执行
　　

CREATE EVENT truncate_event_table_day　　
ON SCHEDULE EVERY 1 DAY
　　
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
　　
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
　　
ON COMPLETION PRESERVE
　　
DO TRUNCATE TABLE schoolDB.event_table;