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

开控制是否开启。不配置则默认为关闭。

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

开启指定事件：ALTEREVENT 具体事件名称 ONCOMPLETIONPRESERVEENABLE;
关闭指定事件：ALTEREVENT 具体的事件名称 ONCOMPLETIONPRESERVEDISABLE;
查看事件：SHOWEVENTS; 或者 SELECT*FROMINFORMATION_SCHEMA.'EVENTS';
删除指定事件：DROPEVENTIFEXISTS 具体事件名称;

三、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'DOINSERTINTO`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+INTERVAL1MINUTEDOINSERTINTO`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'ENABLEDOBEGIN-- 具体执行的SQLINSERTINTO`template_backend`.`message`(`message`,`message_date`)values('批量插入第一条数据',now());INSERTINTO`template_backend`.`message`(`message`,`message_date`)values('批量插入第二条数据',now());INSERTINTO`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 10SECONDON COMPLETION PRESERVE -- 保存事件DOINSERTINTO`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 1MINUTE STARTS '2024-03-22 10:10:00'ON COMPLETION PRESERVE
DOINSERTINTO`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 1MINUTE STARTS CURRENT_TIMESTAMP+INTERVAL5MINUTEON COMPLETION PRESERVE
DOINSERTINTO`template_backend`.`message`(`message`,`message_date`)values('延迟5分钟后,每隔1分钟执行',now());

查看message表预期数据:

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

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

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