《MySQL 入门教程》第 34 篇 计划任务

文章目录

34.1 配置事件调度器34.2 创建计划事件34.3 查看计划事件34.4 修改计划事件34.5 删除计划事件

MySQL 支持定时执行的计划任务，类似于 Unix crontab 或者 Windows 定时任务，被称为事件（Event）或者计划事件。事件是一个存储在数据库服务器中的 SQL 程序，在指定的时间范围内定期执行，调度和执行事件的服务被称为事件调度器（Event Scheduler）。

MySQL 事件有时候也称为“时间触发器”，因为它们是基于特定时间点触发的程序，类似于上一篇介绍的触发器。 MySQL 事件可以用于许多场景，例如优化数据库表、归档数据、生成复杂查询报告、清理日志文件等。

34.1 配置事件调度器

事件调度器负责管理和执行事件，它本质上是一个特殊的线程。我们可以通过SHOW PROCESSLIST命令查看事件调度器线程的信息和状态：

mysql> show processlist\G *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 21 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 8 User: root Host: localhost:59956 db: NULL Command: Query Time: 0 State: starting Info: show processlist 2 rows in set (0.00 sec)

其中的 event_scheduler 代表了事件调度器线程，如果没有显示该记录表示没有启动事件调度器。

MySQL 通过全局系统变量 event_scheduler 控制是否允许和启动事件调度器，它有三种可能的取值：

ON，默认设置，表示启用事件调度器线程，负责事件的调度和执行。OFF，关闭事件调度器线程，SHOW PROCESSLIST 命令不再显示相关信息，计划事件不再执行。DISABLED，禁用事件调度器线程，不但停止了调度器线程，而且无法通过 ON 或者 OFF 设置它的状态。

使用 SHOW 命令可以查看当前的 event_scheduler 设置：

mysql> show global variables like 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | ON | +-----------------+-------+ 1 row in set, 1 warning (0.06 sec)

只要状态不是 DISABLED，就可以通过 SET 语句启动或者关闭事件调度器。例如：

-- 启动事件调度器 SET GLOBAL event_scheduler = ON; SET @@GLOBAL.event_scheduler = 1; -- 关闭事件调度器 SET GLOBAL event_scheduler = OFF; SET @@GLOBAL.event_scheduler = 0;

只有在启动服务的时候才能够将事件调度器设置为 DISABLED，运行时无法从 ON 或者 OFF 设置为 DISABLED；同样也无法在运行时从DISABLED 修改为其他状态。在启动服务时指定以下命令行参数可以禁用事件调度器：

--event-scheduler=DISABLED

或者在 MySQL 配置文件中的 [mysqld] 部分增加以下配置项：

event_scheduler=DISABLED

默认情况下，我们不需要进行任何配置就可以使用 MySQL 计划事件功能。

34.2 创建计划事件

MySQL 提供了CREATE EVENT语句，用于创建计划事件：

CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [COMMENT 'string'] DO event_body;

其中，event_name 是计划事件的名称；ON SCHEDULE 用于指定事件的执行计划，也就是执行的时间和频率；COMMENT 用于为事件增加注释信息；event_body 包含了事件执行的 SQL 语句，可以是简单语句或者由 BEGIN … END 组成的复合语句，甚至存储过程调用。

对于执行计划 schedule，可能的取值有两种：

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

其中，AT timestamp 用于创建一次性执行的事件，指定了该事件发生的具体时间。例如：

CREATE TABLE t_event(id int auto_increment primary key, ts timestamp); CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND DO INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

事件 event1 将会在创建的 10 秒之后插入一条记录到表 t_event 中，随后查询该表可以看到相应的记录：

mysql> select * from t_event; +------+---------------------+ | id | ts | +------+---------------------+ | 1 | 2020-10-07 21:31:29 | +------+---------------------+ 1 row in set (0.00 sec)

对于时间间隔 interval 值，可以使用以下不同的时间单位：

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

默认情况下，一次性事件在执行完成后自动删除。如果想要保留事件定义，可以使用 ON COMPLETION PRESERVE 选项：

CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND ON COMPLETION PRESERVE DO INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

如果没有显式指定，创建事件时默认使用 ON COMPLETION NOT PRESERVE 选项。

EVERY interval 子句可以用于创建一个重复执行的事件，它指定了事件的执行频率和有效期限。例如：

CREATE EVENT event2 ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP ENDS '2020-12-31 23:59:59' DO INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP);

事件 event2 从创建时开始每 5 分钟执行一次，直到 2020 年 12 月 31 日 23:59:59 结束。STARTS 和 ENDS 子句用于定义事件的有效期限，省略时默认从事件创建时开始，并且无限期执行。

默认情况下，事件创建之后处于激活状态。我们也可以使用 DISABLE 选项创建一个被禁用的事件：

CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'string'] DO event_body;

使用 DISABLE 选项创建的事件不会被执行，除非将状态修改为 ENABLE，参考下文的修改计划事件。

34.3 查看计划事件

使用SHOW EVENTS语句可以查看当前数据库中的计划事件：

SHOW EVENTS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

例如：

mysql> show events\G *************************** 1. row *************************** Db: hrdb Name: event1 Definer: root@localhost Time zone: SYSTEM Type: ONE TIME Execute at: 2020-10-07 21:32:45 Interval value: NULL Interval field: NULL Starts: NULL Ends: NULL Status: DISABLED Originator: 1 character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci *************************** 2. row *************************** Db: hrdb Name: event2 Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 5 Interval field: MINUTE Starts: 2020-10-07 21:35:07 Ends: 2020-12-31 23:59:59 Status: ENABLED Originator: 1 character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 2 rows in set (0.03 sec)

另外，MySQL 系统表 INFORMATION_SCHEMA.EVENTS 中存储了更加详细的事件信息。

也可以使用SHOW CREATE EVENT语句查看指定事件的定义。例如：

mysql> show create event event1\G *************************** 1. row *************************** Event: event1 sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`localhost` EVENT `event1` ON SCHEDULE AT '2020-10-07 21:32:45' ON COMPLETION PRESERVE DISABLE DO INSERT INTO t_event(ts) VALUES (CURRENT_TIMESTAMP) character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

34.4 修改计划事件

如果想要修改计划事件的属性和定义，可以使用ALTER EVENT语句：

ALTER EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE] [COMMENT 'string'] [DO event_body]

ALTER EVENT 语句支持的选项和 CREATE EVENT 语句相同，另外它可以通过 RENAME TO 子句修改事件的名称。例如：

ALTER EVENT event2 RENAME TO repeat_event COMMENT 'This is a repeat event.';

34.5 删除计划事件

如果想要删除一个存在的计划事件，可以使用DROP EVENT语句：

DROP EVENT [IF EXISTS] event_name

例如，以下语句可以用于删除事件 event1：

DROP EVENT IF EXISTS event1;

默认情况下，已经过期的事件会自动删除，除非设置了 ON COMPLETION PRESERVE 选项。

不剪发的Tony老师 认证博客专家 数据库架构师 毕业于北京航空航天大学，十多年数据库管理与开发经验，学院签约讲师以及GitChat专栏作者。目前在一家全球性的游戏公司从事数据库架构设计和开发，拥有OCP以及RHCE证书。