mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

16.4 MySQL事件调度器

MySQL事件调度器（Event Scheduler）是一种在数据库内部实现定时任务的功能，类似于操作系统中的 cron 计划任务。它允许你根据预定义的时间表自动执行 SQL 语句或存储过程，非常适合用于定期数据清理、统计汇总、缓存刷新等维护操作。

一、什么是事件调度器？

事件调度器是一个在后台运行的线程，它负责管理和执行已定义的事件。一个事件就是一个命名的数据库对象，包含一个或多个将在指定时间执行的 SQL 语句。

事件调度器的特点

完全在数据库内部运行，无需依赖外部脚本或操作系统计划任务。
支持一次性事件和重复性事件。
可以精确到秒级执行。
提供灵活的时间调度，支持 STARTS、ENDS 等子句定义时间窗口。
可以启用或禁用事件，以及设置事件是否保留。

二、启用事件调度器

默认情况下，MySQL 的事件调度器可能是关闭的。在使用事件之前，需要先检查并开启它。

查看调度器状态

SHOW VARIABLES LIKE 'event_scheduler%';

如果值为 OFF 或 0，则表示调度器未启用。

启用调度器

可以在 MySQL 运行时动态启用：

SET GLOBAL event_scheduler = ON;

或者设置为 1：

SET GLOBAL event_scheduler = 1;

要使设置永久生效，需要在 MySQL 配置文件（如 my.cnf 或 my.ini）的 [mysqld] 部分添加：

event_scheduler = ON

注意事项

开启事件调度器需要 SUPER 权限（MySQL 8.0 中可能需要 SYSTEM_VARIABLES_ADMIN 权限）。
调度器线程在 MySQL 进程内运行，消耗少量资源，但通常可以忽略不计。

三、创建事件的基本语法

创建事件使用 CREATE EVENT 语句，其基本语法如下：

CREATE EVENT [IF NOT EXISTS] 事件名
ON SCHEDULE 调度规则
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '描述信息']
DO
    SQL语句;

调度规则详解

调度规则有两种形式：一次性执行和重复执行。

1. 一次性执行（AT）

ON SCHEDULE AT '2025-12-31 23:59:59'

事件将在指定的时间点执行一次。

也可以使用表达式，如 AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR，表示一小时后执行。

2. 重复执行（EVERY）

ON SCHEDULE EVERY 间隔 [STARTS 开始时间] [ENDS 结束时间]

间隔由数值和时间单位组成，如 1 DAY、2 HOUR、30 MINUTE、1 WEEK、1 YEAR 等。
STARTS 可选，指定第一次执行的时间，默认为创建时刻。
ENDS 可选，指定停止执行的时间，超过后事件不再执行。

选项说明

ON COMPLETION [NOT] PRESERVE：指定事件执行完毕后是否保留。默认是 ON COMPLETION NOT PRESERVE，即执行一次后自动删除。对于重复事件，通常希望保留，可指定 ON COMPLETION PRESERVE。
ENABLE | DISABLE：创建时事件默认是启用状态，也可以显式指定为 DISABLE 使其创建后暂不生效。
COMMENT：为事件添加注释，便于管理。

四、示例：创建事件

示例1：一次性事件（清理临时数据）

假设需要在一小时后删除临时表中的所有数据。

CREATE EVENT clear_temp_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    DELETE FROM temp_data;

示例2：重复事件（每天凌晨归档日志）

每天凌晨 3:00 将 logs 表中 7 天前的数据移到 logs_archive 表。

CREATE EVENT archive_old_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 03:00:00'
ON COMPLETION PRESERVE
DO
BEGIN
    -- 将旧数据插入归档表
    INSERT INTO logs_archive SELECT * FROM logs WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
    -- 删除原表旧数据
    DELETE FROM logs WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY);
END;

注意：如果 DO 子句包含多条 SQL 语句，必须使用 BEGIN ... END 块，并临时修改分隔符（但在 CREATE EVENT 中，由于是在客户端提交，通常不需要修改分隔符，只需确保语句块正确即可）。实际上，在 MySQL 命令行中执行多语句事件时，可以使用 DELIMITER 技巧，与存储过程类似。

示例3：每隔10分钟更新汇总表

每10分钟统计一次订单总额并存入统计表。

CREATE EVENT update_order_summary
ON SCHEDULE EVERY 10 MINUTE
STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
    UPDATE summary_table SET total_orders = (SELECT COUNT(*) FROM orders);

五、查看事件

查看当前数据库中的事件：

SHOW EVENTS;

该命令会显示事件名称、状态、调度规则、最后一次执行时间等信息。

要查看某个具体事件的定义，可以使用：

SHOW CREATE EVENT 事件名;

六、修改事件

使用 ALTER EVENT 语句可以修改事件的属性，包括调度规则、状态、注释等。

语法

ALTER EVENT 事件名
[ON SCHEDULE 新调度规则]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO 新事件名]
[ENABLE | DISABLE]
[COMMENT '新描述']
[DO 新SQL语句];

示例

禁用某个事件：

ALTER EVENT clear_temp_table DISABLE;

修改事件的执行间隔：

ALTER EVENT archive_old_logs
ON SCHEDULE EVERY 2 DAY
STARTS '2025-01-01 03:00:00';

重命名事件：

ALTER EVENT clear_temp_table RENAME TO clean_temp;

七、删除事件

不再需要的事件可以删除：

DROP EVENT [IF EXISTS] 事件名;

八、事件调度器的权限管理

创建、修改、删除事件需要 EVENT 权限。
查看事件需要 SHOW EVENTS 权限或对 mysql 数据库的 SELECT 权限。
开启全局调度器需要 SUPER 或 SYSTEM_VARIABLES_ADMIN 权限。

可以通过授权语句赋予用户事件权限：

GRANT EVENT ON 数据库名.* TO '用户名'@'主机';

九、实际应用场景

定期清理过期数据：例如删除 30 天前的日志记录，避免表过大。
生成统计报表：每天凌晨汇总前一天的销售数据，存入报表表。
数据归档：将历史数据从在线表移到归档表。
缓存更新：定期刷新物化视图或缓存表。
发送提醒：结合 SELECT ... INTO OUTFILE 生成数据文件，或调用外部程序（通过 sys_exec() 等，但需谨慎）。

十、注意事项与性能考量

时区问题：事件调度器使用 MySQL 服务器的时区设置。如果数据库时区变更，已定义的事件时间可能会偏移。建议统一使用时区，或明确指定时间。
精确度：事件调度器不是实时的实时调度器，它的精度受系统负载影响，一般误差在几秒内，不适合对时间要求极其严格的任务。
资源占用：频繁执行的事件（如每分钟执行）可能会消耗数据库资源，应评估对性能的影响。
事件失败处理：如果事件执行过程中发生错误，默认不会重试。可以在事件体内编写错误处理逻辑（如使用 DECLARE CONTINUE HANDLER）。
事件与事务：事件内的 SQL 语句默认自动提交，如果需要事务，可以使用 START TRANSACTION 显式控制。
事件调度器状态：如果 MySQL 重启，调度器状态取决于配置文件中的设置。确保已正确配置为 ON。

小结

MySQL 事件调度器提供了强大的定时任务功能，完全集成在数据库内部。
使用 CREATE EVENT 创建事件，通过 ON SCHEDULE 定义执行计划。
可以创建一次性事件或重复事件，并设置开始和结束时间。
使用 ALTER EVENT 修改现有事件，DROP EVENT 删除事件。
合理利用事件可以简化数据库维护工作，提高自动化水平。