MySQL 事件调度器的使用、用于定时自动执行 SQL 语句

前言

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

启用事件调度器

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

1	show variables like‘%sche%’;

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

1	setglobal 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 EVERY1DAY STARTS DATE(CURRENT_DATE+1)

DO truncatetable logging_event_property;

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

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

1 2	— 每五分钟执行一次定时任务 ON SCHEDULE EVERY5MINUTE STARTS CURDATE()

1 2	— 每小时执行一次定时任务 ON SCHEDULE EVERY1HOUR 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 ATTIMESTAMP‘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 ATCURRENT_TIMESTAMP+INTERVAL1HOUR

ON COMPLETION NOT PRESERVE

DO call user_procedure();

周期性一直执行

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

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

DROP EVENT IF EXISTS user_event;

CREATE EVENT user_event

ON SCHEDULE EVERY9DAY STARTS NOW()

DO call user_procedure();

每天凌晨 1 点执行

DROP EVENT IF EXISTS user_event;

CREATE EVENT user_event

on schedule EVERY1DAY STARTS date_add(date( ADDDATE(curdate(),1)),interval1hour)

DO call user_procedure();

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

DROP EVENT IF EXISTS user_event;

CREATE EVENT user_event

ON schedule every1month starts date_add(date_add(date_sub(curdate(),intervalday(curdate())-1day),interval1month),interval1hour)

DO call user_procedure();

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

DROP EVENT IF EXISTS user_event;

CREATE EVENT user_event

ON schedule every1 quarter starts date_add(date_add(date(concat(year(curdate()),‘-‘,elt(quarter(curdate()),1,4,7,10),‘-‘,1)),interval1 quarter),interval1hour)

DO call user_procedure();

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

DROP EVENT IF EXISTS user_event;

CREATE EVENT user_event

ON schedule every1 quarter starts date_add(date_add(date(concat(year(curdate()),‘-‘,elt(quarter(curdate()),1,4,7,10),‘-‘,1)),interval1 quarter),interval1hour)