文章目录
- 基本概念
- 一、Event事件使用权限
- 二、开启\关闭Event事件
- 三、Event事件定义格式
- 四、事件调度使用案例
- 4.1 准备工作
- 4.2 创建单次定时执行事件
- 4.2.1 创建指定时间单次执行事件任务
- 4.2.2 创建延迟时间单次执行事件任务
- 4.2.3 创建单次执行事件任务[多SQ执行]
- 4.3 创建循环定时执行事件
- 4.3.1 从当前开始每10秒执行一次事件
- 4.3.2 指定某个时间开始,每隔1分钟执行一次
- 4.3.3 从当前时间延迟指定时间,每隔5分钟执行一次
- 4.3.3 每天执行一次,开始时间为明天凌晨1点整
基本概念
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;