MySQL定时任务Event详解

---

基本概念

MySQL5.1开始，增加了一个十分有用的功能：事件调度器（event scheduler），该功能可以定时单次执行或者多次执行某些任务，比如日志数据的删除、数据统计报告、数据备份等。原来只能通过程序或者Crontab结合脚本做的事情，现在可以使用Event来做。并且MySQL提供的事件调度器可以精确到秒，而操作系统的计划任务比如Cron只能精确到分钟。

---

一、Event事件使用权限

用户需要要调用某个Event，需要查看用户是否拥有执行Event的权限。Event权限的设置保存在mysql.user表和mysql.db表中的event_priv字段。N表示没有执行权限，Y表示拥有执行Event的权限。如果您对MySQL的权限管理还不是很清楚，您可以参考文章:Mysql用户权限分配详解

---

二、开启\关闭Event事件

查询Event事件功能是否开启，ON则表示开启，OFF表示未开启。

SELECT @@event_scheduler; -- 方法一
SHOW VARIABLES LIKE '%event_scheduler%'; -- 方法二

关闭Event事件功能可以使用以下指令：

SET GLOBAL event_scheduler = 1; -- 方法一
SET GLOBAL event_scheduler = on; -- 方法二

开启Event事件功能可以使用以下指令：

SET GLOBAL event_scheduler = 0;
SET GLOBAL event_scheduler = off;

除了可以通过指令关闭和开启Event事件功能之外，还可以通过配置文件my.cnf配置是否开启Event功能，只需要在my.cnf中配置event_scheduler=1或者event_scheduler=0开控制是否开启。不配置则默认为关闭。

常用指定某个事件开启\关闭\查询\删除指令：

开启指定事件：ALTER EVENT 具体事件名称 ON COMPLETION PRESERVE ENABLE;
关闭指定事件：ALTER EVENT 具体的事件名称 ON COMPLETION PRESERVE DISABLE;
查看事件：SHOW EVENTS; 或者 SELECT * FROM INFORMATION_SCHEMA.'EVENTS';
删除指定事件：DROP EVENT IF EXISTS 具体事件名称;

---

三、Event事件定义格式

以下是创建一个事件调度的大体结构格式，其中使用[]括号括起来的内容是可选项，可以填或者不填。

CREATE EVENT [IF NOT EXISTS] 事件名称
ON SCHEDULE 调度策略
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT '备注内容']
DO sql_statement;

参数详细说明：

参数	解释
IF NOT EXISTS	这个是判断是否存在相同的Event名称，如果不存在才创建
调度策略	调度策略是整个调度中最重要的，用于定义这个事件何时触发，是单次调度还是多次调度，具体使用方法将在后续案列中演示
ON COMPLETION PRESERVE	是指当本次事件调度执行完成后，会保留该事件。如果您不配置，则默认是ON COMPLETION NOT PRESERVE ，表示执行完成后自动删除该事件。如果是周期性调度的话，需要配置成该类型
ON COMPLETION NOT PRESERVE	本次调度执行完成后自动删除该事件
ENABLE \ DISABLE \ DISABLE ON SLAVE	用于指定事件状态，ENABLE表示该事件是启动状态，DISABLE 表示未启动，DISABLE ON SLAVE表示对于从数据库则不启动该事件。如果不指定这三个选择中的任意一个，则在一个事件创建之后，它默认是ENABLE状态
SQL_STATEMENT	用于指定事件启动时所要执行的代码。可以是任何有效的sql语句、存储过程或者一个计划执行的事件。如果包含多条语句，可以使用BEGIN…END复合结构

调度策略配置语法：
调度策略有两种方式，单次和周期性循环执行，单次执行的关键字AT，后面可以接指定的执行时间字符串，比如 AT TIMESTAMP '2024-3-20 10:14:00' 表示在2024年3月20号早上10点14分整执行一次任务。周期性执行的关键字EVERY，比如EVERY 10 SECOND则表示每隔10秒执行一次任务。时间单位除了SECOND，还有 YEAR、QUARTER、MONTH、DAY、HOUR、MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE 、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND等等。

---

四、事件调度使用案例

4.1 准备工作

在演示案例之前，我们先创建一个message表，用于查看演示效果。

CREATE TABLE message (
id bigint(255) NOT NULL AUTO_INCREMENT,
message varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
message_date datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

在创建好之后，开始编写带一个Event调度任务。

---

4.2 创建单次定时执行事件

首先先确保您已经开启事件功能！否则事件即使创建也不会执行！！！然后通过指令SHOW EVENTS; 查看当前是否存在任何定时任务。

---

4.2.1 创建指定时间单次执行事件任务

以下SQL语句表示创建一个单次执行事件，事件名称叫singleEvent，指定在时间2024-3-21 14:20:00往message表里插入一条数据。我的message表是放在template_backend库下，这里请修改为您message表具体的库名

CREATE EVENT singleEvent
ON SCHEDULE
AT TIMESTAMP '2024-3-21 14:20:00'
DO
INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('指定时间单次执行任务', now())

执行完以上SQL语句后，可以通过指令SHOW EVENTS;查看当前有效事件。可以查看我们刚刚定义的事件。
DB：表示用作与哪个库，Name是事件名称。
Definer：表示哪个用户权限执行。
Type：ONE TIME表示只执行一次。
status：ENABLE表示当前事件为可用状态。

等到时间到2024-3-21 14:20:00之后，再次通过指令SHOW EVENTS;查看当前有效事件发现事件singleEvent已经被删除，而message表中也插入了我们预期的数据。

---

4.2.2 创建延迟时间单次执行事件任务

以下SQL语句表示创建一个单次延迟执行事件，事件名称叫delaySingleEvent，延迟事件为从当前事件开始往后延迟一分钟执行，往message中插入一条数据。

CREATE EVENT delaySingleEvent
ON SCHEDULE 
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO 
INSERT INTO  `template_backend`.`message` (`message`, `message_date`) values ('单次延迟一分钟执行事件', now())

执行以上SQL，并通过SHOW EVENTS; 查看事件是否创建成功:

等待一分钟后，再通过SHOW EVENTS; 指令查看可以发现事件被删除，表message也插入预期的数据:

---

4.2.3 创建单次执行事件任务[多SQ执行]

以下SQL语句表示创建一个单次执行事件，只是一次性执行多个SQL语句。事件名称叫specifiedBatchTimeEvent，指定在时间2024-3-22 09:42:00往message表里插入三条数据

CREATE EVENT specifiedBatchTimeEvent
ON SCHEDULE
AT TIMESTAMP '2024-3-22 09:42:00'
ENABLE
DO BEGIN
-- 具体执行的SQL
INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('批量插入第一条数据', now());
INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('批量插入第二条数据', now());
INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('批量插入第三条数据', now());
END;

执行完以上SQL后通过SHOW EVENTS;可以查看事件是否创建成功，可以看到事件已经被创建并且状态为ENABLED，并计划在2024-03-22 09:42:00执行。

等到2024-03-22 09:42:00后，查看表message可以看到我们预期的插入数据:

---

4.3 创建循环定时执行事件

4.3.1 从当前开始每10秒执行一次事件

以下SQL用于创建一个循环事件，每个EVERY 10 SECOND表示该事件每个十秒钟就执行一次，执行的操作是往message表写数据。

CREATE EVENT loopEvent
ON SCHEDULE
EVERY 10 SECOND
ON COMPLETION PRESERVE -- 保存事件
DO
INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('每十秒执行任务', now())

执行以上SQL后，可以通过SHOW EVENTS;查看事件是否被创建成功。

Type: RECURRING表示该事件是一个循环事件
Interval value：表示调度周期
Interval field: 表示调度周期的单位
Starts:表示事件从哪个时间点开始执行

通过查看Message表可以看到我们预想的数据，每个十秒插入一次。

如果您想停止该事件运行，您可以使用指令ALTER EVENT 事件名称 DISABLE。比如停止以上事件可以使用指令：ALTER EVENT loopEvent DISABLE，如果你想删除某个事件，可以使用指令：DROP EVENT IF EXISTS 事件名称;

---

4.3.2 指定某个时间开始，每隔1分钟执行一次

CREATE EVENT DelayedSchedulingEvent
ON SCHEDULE
EVERY 1 MINUTE STARTS '2024-03-22 10:10:00'
ON COMPLETION PRESERVE
DO INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('指定时间2024-03-22 10:10:00后,每隔1分钟执行', now());

---

4.3.3 从当前时间延迟指定时间，每隔5分钟执行一次

以下SQL用于创建一个循环事件，每个EVERY 1 MINUTE表示该事件每一分钟就执行一次，CURRENT_TIMESTAMP + INTERVAL 5 MINUTE表示从当前时间开始延迟五分钟再执行。

CREATE EVENT DelayedSchedulingEvent
ON SCHEDULE
EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
ON COMPLETION PRESERVE
DO INSERT INTO `template_backend`.`message` (`message`, `message_date`) values ('延迟5分钟后,每隔1分钟执行', now());

查看message表预期数据:

---

4.3.3 每天执行一次，开始时间为明天凌晨1点整

以下SQL用于创建一个循环事件，从第二天的凌晨一天开始，以后每天执行一次，删除message表中日志大于15天的日志。

DROP EVENT IF EXISTS DelayedSchedulingEvent;
CREATE EVENT logClearEvent
ON SCHEDULE
EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
ON COMPLETION PRESERVE -- 执行完成一次后保存该事件
ENABLE -- DISABLE ON SLAVE --只有在主从中才使用，否则使用该指令,会默认数据是SAVLE数据 禁止SLAVE使用
DO BEGIN
-- 执行的具体SQL
DELETE FROM DELETE FROM `template_backend`.`message`  WHERE message.message_date <NOW() - INTERVAL 15 DAY;
END;