Events

🗞️ 返回专题页

事件

在MySQL 5.1中新增了一个特色功能事件调度器（Event Scheduler），简称事件。它可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL的事件可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下是非常实用的。

事件调度器是定时触发执行的，从这个角度上看也可以称作是“临时触发器”。但是它与触发器又有所区别，触发器只针对某个表产生的事件执行一些语句，而事件调度器则是在某一段（间隔）时间执行一些语句。

查看调度器

SHOW PROCESSLIST;
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;

开启调度器

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
 
-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;
 
-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

# 更改文件里的这个,事件调度器启动状态
event_scheduler = on

创建

CREATE
	[DEFINER={user | CURRENT_USER}]
	EVENT [IF NOT EXISTS] event_name
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO event_body;

参数含义：

在ON SCHEDULE子句中，参数schedule的值为一个AS子句，用于指定事件在某个时刻发生，其语法格式如下：

AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

参数说明

（1）timestamp：表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。

（2）EVERY子句：用于表示事件在指定时间区间内每隔多长时间发生一次，其中 SELECT子句用于指定开始时间；ENDS子句用于指定结束时间。

（3）interval：表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示4周；使用“‘1:10’ HOUR_MINUTE”表示1小时10分钟。间隔的距离用DATE_ADD()函数来支配。

interval参数值的语法格式如下：

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

时间间隔设置

（1）每隔5秒钟执行

ON SCHEDULE EVERY 5 SECOND

（2）每隔1分钟执行

ON SCHEDULE EVERY 1 MINUTE

（3）每天凌晨1点执行

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)

（4）每个月的第一天凌晨1点执行

ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)

（5）每 3 个月，从现在起一周后开始

ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK

（6）每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束

ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

示例1

定期汇总销售数据

假设你有一个 sales 表和一个 monthly_sales 表，你希望每月将销售数据汇总到 monthly_sales 表中。

CREATE EVENT monthly_sales_summary
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
    INSERT INTO monthly_sales (product_id, total_sales, month)
    SELECT product_id, SUM(amount), DATE_FORMAT(NOW(), '%Y-%m-01')
    FROM sales
    WHERE sale_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    GROUP BY product_id;

    -- 可选：清理销售表中的数据
    DELETE FROM sales WHERE sale_date < DATE_SUB(NOW(), INTERVAL 1 MONTH);
END;

示例2

自动生成报表

假设你有一个 orders 表，想要每天生成一个订单报表并保存到 daily_reports 表中。

CREATE EVENT daily_order_report
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DECLARE today DATE;
    SET today = CURDATE();

    INSERT INTO daily_reports (report_date, total_orders, total_revenue)
    SELECT today, COUNT(*), SUM(total_amount)
    FROM orders
    WHERE order_date = today;

    -- 如果需要，可以在这里发送邮件或进行其他操作
END;

示例3

监控用户活动

如果你希望定期检查用户在过去一段时间内的活动情况并更新用户状态，可以使用如下事件。

CREATE EVENT user_activity_monitor
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    UPDATE users
    SET status = 'inactive'
    WHERE last_login < NOW() - INTERVAL 30 DAY;
END;

示例4

定时执行复杂的清理任务

假设你有一个包含大量历史记录的 transactions 表，你想根据多个条件来清理数据。

CREATE EVENT complex_cleanup
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
    DELETE FROM transactions
    WHERE transaction_date < NOW() - INTERVAL 1 YEAR
    AND status = 'completed';
    
    -- 可以添加更多复杂的条件和操作
END;

查询事件

/*在MySQL中可以通过查询information_schema.events表，查看已创建的事件。其语句如下：*/

SELECT * FROM information_schema.events; 

修改事件

在MySQL 5.1及以后版本中，事件被创建之后，还可以使用ALTER EVENT语句修改其定义和相关属性。其语法如下：

ALTER
	[DEFINER={user | CURRENT_USER}]
	EVENT [IF NOT EXISTS] event_name
	ON SCHEDULE schedule
	[ON COMPLETION [NOT] PRESERVE]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'comment']
	DO event_body;

ALTER EVENT语句与CREATE EVENT语句基本相同。另外ALTER EVENT语句还有一个用法就是让一个事件关闭或再次活动。

启动与关闭事件

另外ALTER EVENT语句还有一个用法就是让一个事件关闭或再次活动。

/*示例：启动名称为event_user的事件。*/

ALTER EVENT event_user ENABLE;

/*示例：关闭名称为event_user的事件。*/

ALTER EVENT event_user DISABLE;

删除事件

在MySQL 5.1及以后版本中，删除已经创建的事件可以使用DROP EVENT语句来实现。

/*示例：删除名称为event_user的事件。*/

DROP EVENT IF EXISTS event_user;

总结

优点

1. 自动化任务调度：
   • 可以定期执行数据清理、备份、更新统计数据等自动化任务，而无需手动操作。
2. 减少外部依赖：
   • 可以避免依赖操作系统的定时任务（如Linux中的cron或Windows中的任务计划），直接在数据库内部执行调度任务。
3. 数据操作原子性：
   • 由于事件在数据库内部执行，可以更容易地保证事务的一致性和原子性，避免外部调度任务带来的并发问题。
4. 灵活的调度设置：
   • 可以精确设定执行时间、间隔和执行条件，如每天、每周、每月或特定时间点。
5. 减少开发和维护成本：
   • 对于一些简单的任务，不需要开发额外的应用层调度程序，只需在数据库中定义和管理即可。
6. 易于管理和监控：
   • 可以通过MySQL的系统表（如information_schema.EVENTS）查看和管理所有事件，并且可以通过SQL命令启用或禁用事件调度器。

缺点

1. 资源占用：
   • 事件调度器会消耗数据库的资源（如CPU、内存），如果频繁执行复杂的事件，可能会影响数据库的整体性能。
2. 调试和错误处理复杂：
   • 相比于外部脚本，事件调度的调试和错误处理不太直观。发生错误时，调试和日志记录相对困难。
3. 事件的可靠性问题：
   • 当MySQL服务器发生异常重启或崩溃时，事件调度器可能无法按照预期继续执行任务，有可能导致事件遗漏。
4. 局限性：
   • MySQL事件调度器适用于简单的数据库操作。如果需要执行复杂的业务逻辑，通常需要借助外部的应用程序或脚本。
5. 不支持分布式调度：
   • MySQL事件调度器在单个数据库实例上运行，无法实现跨多个数据库实例的调度。对于分布式系统，需要借助其他调度工具（如cron、Airflow等）。
6. 任务依赖关系管理困难：
   • 事件之间的依赖关系管理比较困难，不能像专业调度工具（如Airflow）一样直观地定义和管理任务的依赖关系。

使用场景

MySQL 事件调度器适合在以下场景中使用：

• 定时清理历史数据。
• 定时更新缓存或统计信息。
• 定时生成报表数据。
• 自动化数据库备份。

总的来说，MySQL事件调度器对于简单的、与数据库紧密相关的定时任务是一个方便的工具，但在复杂调度需求下，可能还需要结合其他调度工具来使用。