一、MySQL event 简介

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”（Event Scheduler）。

在日常开发中会遇到这样的需求：每天或者每小时定时去执行某些代码逻辑操作数据。在 JAVA 开发，一般的操作是在代码中开启一个线程或者使用框架提供的定时任务配置进行任务的执行。

而 MySQL 在5.1.6版本之后，提供了事件调度器的功能，使用它可以定时执行某些特定任务（如：数据分析、数据汇总、数据备份等等），来取代我们之前实现定时任务的方法（如语言自身提供的定时任务、操作系统定时任务）。

此外对于 MySQL 的事件调度器，它在时间维度上可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux 的 crontab 或 Windows 下的任务计划）只能精确到每分钟执行一次。这一特性适合于一些实时性要求较高的需求场景。

 

二、MySQL event 的优缺点

优点：

• 时间维度上精确到每秒执行一个任务，实时性较高。
• 对于数据的定时操作，可以减少对于外部程序的依赖，直接依赖于数据库。

缺点：

• 只可以定时执行，不可主动调用
• 开启 event 功能需要 root 用户权限，在特定场景中的创建和维护可能也需要 root 权限。

 

三、开启和关闭 event

检测MySql事件调度器是否开启

查看事件是否开启：

SHOW VARIABLES LIKE '%event_sche%'; 
或者 
SELECT @@event_scheduler;

查看所有事件任务：

SHOW EVENTS;

开启MySql事件调度器

临时开启（需 root 用户权限）：

SET GLOBAL event_scheduler = 1; 
或者
SET GLOBAL event_scheduler = ON;

永久开启：

在配置 my.cnf 文件中加上 event_scheduler = 1
或者
在启动命令加上 --event_scheduler = 1

开启事件任务：

ALTER EVENT eventName ON COMPLETION PRESERVE ENABLE;

关闭MySql事件调度器

临时关闭：

SET GLOBAL event_scheduler = 0;

永久关闭：

删除配置文件 event_scheduler = 1 的配置
或者
设置 event_scheduler = 0

关闭事件任务：

ALTER EVENT eventName ON COMPLETION PRESERVE DISABLE;

 

四、创建 event

CREATE EVENT [IFNOT EXISTS] event_name
    　　 ON SCHEDULE schedule
    　　 [ONCOMPLETION [NOT] PRESERVE]
    　　 [ENABLE | DISABLE]
    　　 [COMMENT 'comment']
    　　 DO sql_statement;
    
SCHEDUAL:
　　 AT TIMESTAMP [+ INTERVAL INTERVAL]
　　 | EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]
　　
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：可选，用于定义事件执行时检查权限的用户。
• 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复合结构

• TIMESTAMP：表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
• EVERY：用于表示事件在指定时间区间内每隔多长时间发生一次，其中 STARTS 用于指定开始时间；ENDS 用于指定结束时间。
• INTERVAL：表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示4周；使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。

 

五、修改 event

ALTER EVENT event_name
    　　 [ONSCHEDULE schedule]
    　　 [RENAME TOnew_event_name]
    　　 [ON COMPLETION [NOT] PRESERVE]
    　　 [COMMENT 'comment']
    　　 [ENABLE | DISABLE]
    　　 [DO sql_statement]

 

六、删除 event

DROP EVENT [IF EXISTS] event_name;

 

七、操作实例

有一张数据的缓存表：

CREATE TABLE `data_cache`(
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `qualified_name` varchar(512) NULL COMMENT '一行数据唯一标识',
    `line` varchar(1024) NULL COMMENT '一行数据内容',
    `created_by` varchar(32) DEFAULT NULL COMMENT '创建者',
    `created_at` timestamp NULL DEFAULT NULL COMMENT '新增时间',
    `updated_by` varchar(32) DEFAULT NULL COMMENT '更新者',
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT='数据缓存表';

 

每个小时定时执行任务，任务为清理创建时间超过一天的数据：

-- 开启事件
SET GLOBAL event_scheduler = 1;

CREATE EVENT data_cache_clean
ON SCHEDULE EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
DELETE FROM data_cache
WHERE TIMESTAMPDIFF(SECOND, created_at, NOW()) > 86400;
END;

 

八、注意事项

在 MySQL 进行命令行操作创建事件的时候需要注意使用 DELIMITER 分隔符。如上述代码：

CREATE EVENT data_cache_clean
ON SCHEDULE EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
DELETE FROM data_cache
WHERE TIMESTAMPDIFF(SECOND, created_at, NOW()) > 86400;   --第一次出现 ";"
END;   ----第二次出现 ";"

在命令行中直接执行操作的时候将会报错，因为出现了两个 ";"。

所以我们需要使用 DELIMITER 来指定分隔符：

DELIMITER $$
CREATE EVENT data_cache_clean
ON SCHEDULE EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
DELETE FROM data_cache
WHERE TIMESTAMPDIFF(SECOND, created_at, NOW()) > 86400;
END$$
DELIMITER ;