MySQL 事件调度器的使用

发表于 2023-09-07 阅读次数：评论数：本文字数： 849 阅读时长 ≈ 1 分钟

大纲

前言

在开发过程中经常会遇到这样一个问题，每天或者每月必须定时去执行一条 SQL 语句，下面这篇文章主要给大家介绍如何使用 MySQL 自带的事件调度器定时执行 SQL 语句。

启用事件调度器

查看事件调度器的运行状态

1	show variables like '%sche%';

如果 event_scheduler 的值为 OFF，则需要启用事件调度器（下述方式属于临时启用）

1	set global event_scheduler=1;

或者更改 MySQL 的配置文件（如 my.conf），在 [mysqld] 标记下添加以下内容，然后重启 MySQL 服务器（下述方式属于永久启用）

1 2	[mysqld] event_scheduler = ON

为什么建议在 MySQL 的配置文件中启用事件调度器

这是为了防止在操作系统断电重启后，导致数据库的事件调度器又变回默认关闭的状态 OFF，这样定时事件就不再自动执行了。通过更改 MySQL 的配置文件，将它设置成默认开启状态 ON，这使得在断电重启后 MySQL 依旧会自动正常执行定时事件。

管理定时事件

创建定时事件

创建定时事件，DO 后面的内容是需要定时执行的 SQL 语句，也可以使用 CALL 指令调用存储过程

-- 每天0点执行
CREATE EVENT clear_logging_event_property
ON SCHEDULE EVERY 1 DAY STARTS DATE(CURRENT_DATE + 1) 
DO truncate table logging_event_property;

on schedule at 表示在特定时间执行，on schedule every 表示重复执行

1 2	--- 特定的日期特定的时间点执行定时任务 ON SCHEDULE at '2023-10-05 19:14:10'

1 2	--- 每五分钟执行一次定时任务 ON SCHEDULE EVERY 5 MINUTE STARTS CURDATE()

1 2	--- 每小时执行一次定时任务 ON SCHEDULE EVERY 1 HOUR STARTS CURDATE()

查看定时事件

查看所有定时事件，将显示事件的详细信息，包括事件名称、定时器时间、事件状态和执行操作等

1	show events;

查看所有定时事件的执行历史记录，将显示事件的详细信息，包括事件名称、定时器时间、事件状态、执行操作、最后一次执行的时间等

1	select * from information_schema.events;

开启定时事件

1 2	--- 开启某个定时事件 alter event user_event on completion preserve enable;

关闭定时事件

1 2	--- 关闭某个定时事件 alter event user_event on completion preserve disable;

删除定时事件

1 2	--- 删除某个定时事件 drop event user_event;

常用定时事件案例

一次性执行

满足指定条件时，只会执行一次定时事件，然后定时事件结束执行

在未来指定时间点执行一次

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
    ON SCHEDULE AT TIMESTAMP '2023-09-24 18:26:00'
    ON COMPLETION NOT PRESERVE
DO call user_procedure();

从现在开始 1 小时后执行一次

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ON COMPLETION NOT PRESERVE
DO call user_procedure();

周期性一直执行

定时事件一直周期性地执行

从现在开始每隔 N 天执行一次

DROP EVENT IF EXISTS user_event;    
CREATE EVENT user_event
ON SCHEDULE EVERY 9 DAY STARTS NOW()
DO call user_procedure();

每天凌晨 1 点执行

DROP EVENT IF EXISTS user_event;    
CREATE EVENT user_event
on schedule EVERY 1 DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval 1 hour)  
DO call user_procedure();

每个月的一号凌晨 1 点执行

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
ON schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour)
DO call user_procedure();

每个季度一号的凌晨 1 点执行

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
ON schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour)
DO call user_procedure();

每年 1 月 1 号凌晨 1 点执行

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
ON schedule every 1 quarter starts date_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval 1 quarter),interval 1 hour)
DO call user_procedure();

周期性多次执行

定时事件执行多次后，在满足某个条件时，定时事件结束执行

从现在开始每天执行一次，5 天后停止执行

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
    ON SCHEDULE EVERY 1 DAY
    ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO call user_procedure();

从现在开始 5 天后开始执行，一个月后停止执行

DROP EVENT IF EXISTS user_event;
CREATE EVENT user_event
    ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
DO call user_procedure();

参考资料

如何用 MySQL 自带的定时器定时执行 SQL