详细分析Mysql中的定时任务（Event事件）

前言

基本的知识推荐阅读：

1. 详细分析Mysql触发器的基本使用（图文解析）
2. 详细分析SQL Server触发器的基本知识
3. 详细分析Corn表达式（附Demo）

特性	事件定时任务	触发器
触发条件	基于时间	基于表中的 INSERT、UPDATE 或 DELETE 操作
使用场景	定时清理、报表生成、自动化维护	保证数据一致性、操作日志记录、自动级联操作
作用范围	可以跨多个表、数据库中的任意对象	只能作用于与触发器绑定的表
执行频率	按时间间隔执行（如每小时、每天等）	每次有数据变更（如插入、更新、删除）时立即执行
与时间的关系	完全基于外部时间	与时间无关，只与数据操作有关
执行的SQL	可以执行复杂的、跨表的SQL操作	只能与触发操作的表有关，常常用于表的自动操作

基本的Demo示例：

事件定时任务示例
场景：每天凌晨清理表 sessions 中超过 30 天未活跃的会话数据

CREATE EVENT clean_expired_sessions
ON SCHEDULE EVERY 1 DAY
STARTS '2024-09-23 00:00:00'
DODELETE FROM sessions WHERE last_active < NOW() - INTERVAL 30 DAY;

触发器示例
场景：在 users 表中插入一条新记录时，自动在 user_logs 表中记录该操作

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROWINSERT INTO user_logs (user_id, action, log_time)VALUES (NEW.id, 'insert', NOW());

1. 基本知识

MySQL 中的事件定时任务（Event Scheduler）允许你在指定的时间点或时间间隔自动执行SQL语句，类似于其他数据库中的计划任务（Scheduler）或Linux中的cron任务

事件任务可以用于自动化数据库维护、数据归档、统计、清理过期数据等

主要特性：

• 可以在未来某一时间点执行任务，或以固定的时间间隔重复执行
• 任务可以单次执行，也可以重复执行
• 不需要外部工具，如 cron，直接在 MySQL 内部进行调度
• 可以暂停或删除任务

事件调度器是MySQL管理定时任务的模块，它在后台运行并监控所有定义的事件

相关的系统变量：

• event_scheduler： 控制事件调度器是否启用
• ON：开启事件调度器
• OFF： 关闭事件调度器，事件不会自动运行
• DISABLED：调度器被禁用

查看当前事件调度器状态：SHOW VARIABLES LIKE 'event_scheduler';

开启事件调度器：SET GLOBAL event_scheduler = ON;

2. Event事件

创建和管理事件：事件调度器中的每一个任务称为一个事件（Event）

创建事件时，需要指定触发的时间、是否循环执行，以及事件的具体SQL操作

创建单次执行的事件：

CREATE EVENT event_name
ON SCHEDULE AT '2024-09-22 12:00:00'
DOUPDATE my_table SET my_column = 'value' WHERE id = 1;

创建重复执行的事件：

CREATE EVENT event_name
ON SCHEDULE EVERY 1 HOUR
STARTS '2024-09-22 12:00:00'
DODELETE FROM my_table WHERE created_at < NOW() - INTERVAL 1 DAY;

删除事件：

DROP EVENT IF EXISTS event_name;

修改事件：

ALTER EVENT event_name
ON SCHEDULE EVERY 2 HOUR
DOINSERT INTO my_log (log_message) VALUES ('Scheduled task executed');

事件的组成部分

CREATE EVENT：定义事件
ON SCHEDULE： 定义任务的触发时间，AT：设定事件在指定的时间点执行。，EVERY：设定事件以固定的时间间隔执行（可结合 STARTS 和 ENDS 定义任务开始和结束时间）
DO：事件要执行的SQL语句
ENABLE 或 DISABLE：决定事件是否启用
COMMENT：给事件添加注释

事件状态管理

启用或禁用事件：

ALTER EVENT event_name ENABLE;
ALTER EVENT event_name DISABLE;

查看事件状态： 可以通过 INFORMATION_SCHEMA.EVENTS 查看数据库中所有的事件及其详细信息

SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'event_name';

事件的生命周期

• 永久事件：这些事件一旦被创建，会一直存在，直到手动删除或修改
• 一次性事件：执行完一次之后就会自动删除

3. Demo

示例 1: 每天凌晨自动清理过期的会话数据
将在 2024-09-23 00:00:00 开始，每天运行一次。删除表 sessions 中最近 7 天未活跃的会话数据

CREATE EVENT clean_sessions
ON SCHEDULE EVERY 1 DAY
STARTS '2024-09-23 00:00:00'
DODELETE FROM sessions WHERE last_active < NOW() - INTERVAL 7 DAY;

示例 2: 每月自动生成销售报表
每月执行一次，将当月的销售总额插入到 monthly_sales_report 表中，CURRENT_TIMESTAMP 表示事件从当前时间开始执行

CREATE EVENT generate_sales_report
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DOINSERT INTO monthly_sales_report (report_month, total_sales)SELECT DATE_FORMAT(NOW(), '%Y-%m'), SUM(amount) FROM sales WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

示例 3: 一次性执行的临时任务
该事件将在 2024-09-25 14:00:00 执行一次，将最近 30 天登录过的用户状态更新为 ‘active’

CREATE EVENT temp_task
ON SCHEDULE AT '2024-09-25 14:00:00'
DOUPDATE user_data SET status = 'active' WHERE last_login > NOW() - INTERVAL 30 DAY;