第 34 篇 计划任务
《MySQL 入门教程》第 34 篇 计划任务
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 选项。