MySQL事件（定时任务）

what's the MySQL 事件

　　MySQL5.1 版本开始引进 event 概念，MySQL 中的事件(event：时间触发器)是用于执行定时或周期性的任务，类似 Linux 中的 crontab，事件可以精确到秒。通过单独或调用存储过程使用，在某一特定的时间点，触发相关的 SQL 语句或存储过程。事件由一个特定的线程来管理的，也就是所谓的事件调度器，但是事件不能直接调用。 

　　总的来说，事件，就是定时任务的管理器。

查看事件调度器是否开启

　　事件由一个特定的线程来管理。启用事件调度器后，拥有 SUPER 权限的账户执行 SHOW PROCESSLIST 就可以看到这个线程了。

SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

 

开启或关闭事件管理器

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
 
-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;
 
-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

注意：如果想要始终开启事件，那么在使用SET GLOBAL开启事件后，还需要在配置文件（Windows系统my.ini、Linux系统my.cnf）中添加 event_scheduler=on 并重启数据库服务。因为如果没有添加，MySQL重启事件后又会回到原来的状态。

 

创建存储过程

　　MySQL 事件一般配合 MySQL 存储过程使用，顶一万 MySQL 存储过程后由 MySQL 事件进行触发调用。

存储过程相关知识点链接：https://www.cnblogs.com/zhuminghui/p/12749283.html

 

事件的主要操作

　　事件的主要操作有：创建、查询、修改、删除、开启和关闭。

创建事件

语法结构

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;

DEFINER    -- 可选，用于定义事件执行时检查权限的用户
IF NOT EXISTS    -- 可选，用于判断要创建的事件是否存在
EVENT event_name    -- 必选，用于指定事件名，event_name的最大长度为64个字符，如果为指定event_name，则默认为当前的MySQL用户名（不区分大小写）
ON SCHEDULE schedule    -- 必选，用于定义执行的时间和时间间隔
ON COMPLETION [NOT] PRESERVE    -- 可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ENABLE | DISABLE | DISABLE ON SLAVE    -- 可选，用于指定事件的一种属性。
                                       -- 其中，关键字ENABLE表示该事件是活动的，也就是调度器检查事件是否必选调用；
                                       -- 关键字DISABLE表示该事件是关闭的，也就是事件的声明存储到目录中，但是调度器不会检查它是否应该调用；
                                       -- 关键字DISABLE ON SLAVE表示事件在从机中是关闭的。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它立即变为活动的。
COMMENT 'comment'    -- 可选，用于定义事件的注释 
DO event_body    -- 必选，用于指定事件启动时所要执行的代码。可以是任何有效的SQL语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN...END复合结构

 

示例

create event second_event       -- 创建名为 second_event 的事件,注意此处没有括号
on schedule every 1 second      -- 创建周期定时的规则，每秒钟执行一次
on completion preserve disable  -- 创建后并不开始生效
do call test_proce();           -- do call test_proce() 是该事件的操作内容，表示调用名为 test_proce() 的存储过程。

 

执行时间相关的例子

on schedule every 1 second         -- 每秒执行1次
on schedule every 2 minute         -- 每两分钟执行1次
on schedule every 3 day            -- 每3天执行1次
ON schedule every 1 day starts date_add(date_add_curdate(), interval 1 day), interval 1 hour)  -- 每天凌晨1点执行
ON schedule every 1 month starts date_add(date_add(date_sub(curdatte(),interval day(curdate())-1 day),interval 1 month),interval 1 hour) -- 每个月的第一天凌晨1点执行

on schedule at current_timestamp()+interval 5 day     -- 5天后执行
on schedule at current_timestamp()+interval 10 minute -- 10分钟后执行
on schedule at '2016-10-01 21:50:00'                  -- 在2016年10月1日，晚上9点50执行
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK -- 每 3 个月，从现在起一周后开始
ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK -- 每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month -- 5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day -- 从现在起每天执行，执行5天

 

有关执行时间需要注意的点

　　在 event 事件中：ON SCHEDULE 计划任务，有两种设定计划任务的方式：

　　（1）AT 时间戳，用来完成单次的计划任务。时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间

　　（2）EVERY 时间（单位）的数量实践单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。可以是任意非空（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参数表示"当这个事件不会再发生的时候"，即当单次计划任务执行完毕后或当重复性的计划任务执行到了ENDS阶段。而PRESERVE的作用是使事件在执行完毕后不会被Drop掉，建议使用该参数，以便于查看EVENT具体信息。

 

 

查看事件

SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

 

修改事件

语法结构

　　ALTER EVENT 语句与 CREATE EVENT 语句基本相同。另外 ALTER EVENT 语句还有一个用法就是让一个事件关闭或再次活动。

ALTER
    [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;

 

删除事件

DROP EVENT IF EXISTS 事件名;

 

开启事件

alter event 事件名 on completion preserve enable; -- 开启定时任务

 

关闭事件

alter event 事件名 on completion preserve disable; -- 关闭定时任务