MySQL 计划事件
简介:在本教程中,您将了解MySQL事件调度程序以及如何创建MySQL事件以自动执行数据库任务。
MySQL事件是基于预定义的计划运行的任务,因此有时它被称为计划事件。MySQL事件也称为“时间触发器”,因为它是由时间触发的,而不是像触发器那样的表更新。MySQL事件类似于UNIX中的cron作业或Windows中的任务调度程序。
您可以在许多情况下使用MySQL事件,例如优化数据库表,清理日志,归档数据或在非高峰时间生成复杂报告。
MySQL事件调度程序配置
MySQL使用一个称为事件调度线程的特殊线程来执行所有调度事件。您可以通过执行以下命令来查看事件调度程序线程的状态:
SHOW PROCESSLIST;
+------+------+-----------+-----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+-----------+---------+------+-------+------------------+
| 2040 | root | localhost | mysqldemo | Query | 0 | init | SHOW PROCESSLIST |
+------+------+-----------+-----------+---------+------+-------+------------------+
1 row in set (0.01 sec)
默认情况下,未启用事件调度程序线程。要启用和启动事件调度程序线程,您需要执行以下命令:
SET GLOBAL event_scheduler = ON;
现在,要查看事件调度程序线程的状态,请SHOW PROCESSLIST再次执行 命令。
SHOW PROCESSLIST;
+------+-----------------+-----------+-----------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------+-----------+---------+------+------------------------+------------------+
| 2040 | root | localhost | mysqldemo | Query | 0 | init | SHOW PROCESSLIST |
| 2048 | event_scheduler | localhost | NULL | Daemon | 5 | Waiting on empty queue | NULL |
+------+-----------------+-----------+-----------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
要禁用和停止事件调度程序线程的事件,请执行SET GLOBAL命令,其中event_scheduler的值为OFF:
SET GLOBAL event_scheduler = OFF;
创建新的MySQL事件
创建事件与创建其他数据库对象(如存储过程或触发器)类似。事件是一个包含SQL语句的命名对象。
存储过程只在调用时才执行; 一个触发时与表相关联的事件被执行,例如插入,更新,或删除而事件可以一次或更多规则的间隔执行发生的事件。
要创建和计划新事件,请使用以下 CREATE EVENT语句:
CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_body
让我们更详细地研究一下这个陈述。
首先,在CREATE EVENT子句后指定事件名称 。事件名称在数据库模式中必须是唯一的。
其次,你在ON SCHEDULE条款之后加上一个时间表 。如果事件是一次性事件,则使用以下语法:AT timestamp [+ INTERVAL]。如果事件是重复事件,则使用以下EVERY子句:EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
第三,将SQL语句放在DO关键字之后。请注意,您可以在事件正文中调用存储过程。如果您有复合SQL语句,可以将它们包装在一个 BEGIN END块中。
让我们看几个创建事件的例子来理解上面的语法。
要创建和计划将消息插入名为messages的表的新一次性事件,请执行以下步骤:
首先,使用 CREATE TABLE语句创建一个messages命名的新表,如下所示:
CREATE TABLE IF NOT EXISTS messages (
id INT PRIMARY KEY AUTO_INCREMENT,
message VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
其次,使用以下CREATE EVENT语句创建事件 :
CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 1',NOW());
三,检查messages表; 你会看到我们有1条记录。这意味着事件在创建时执行。
SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message | created_at |
+----+--------------------+---------------------+
| 1 | Test MySQL Event 1 | 2019-08-24 00:58:51 |
+----+--------------------+---------------------+
1 row in set (0.00 sec)
要显示数据库模式的所有事件,请使用以下语句:
SHOW EVENTS FROM mysqldemo;
mysql> SHOW EVENTS FROM mysqldemo;
Empty set (0.00 sec)
我们没有看到任何行返回,因为事件在过期时会自动删除。在我们的例子中,它是一次性事件,并在执行完成时过期。
要更改此行为,可以使用 ON COMPLETION PRESERVE子句。以下语句创建另一个一次性事件,事件在创建时间为1分钟后执行,并且在执行后不会被删除。
CREATE EVENT test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL Event 2',NOW());
等待1分钟,检查消息表,添加了另一条记录:
SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message | created_at |
+----+--------------------+---------------------+
| 1 | Test MySQL Event 1 | 2019-08-24 00:58:51 |
| 2 | Test MySQL Event 2 | 2019-08-24 01:03:51 |
+----+--------------------+---------------------+
2 rows in set (0.00 sec)
如果我们再次执行 SHOW EVENTS 语句,我们会看到事件是因为ON COMPLETION PRESERVE子句的效果 :
SHOW EVENTS FROM mysqldemo;
+-----------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval value | Interval field | Starts | Ends | Status | Originator | character_set_client | collation_connection | Database Collation |
+-----------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| mysqldemo | test_event_02 | root@localhost | SYSTEM | ONE TIME | 2019-08-24 01:02:21 | NULL | NULL | NULL | NULL | DISABLED | 1 | utf8 | utf8_general_ci | utf8mb4_general_ci |
+-----------+---------------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
以下语句创建一个每分钟执行一次的重复事件,并在创建时间后的1小时内过期:
CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO messages(message,created_at)
VALUES('Test MySQL recurring Event',NOW());
请注意,我们使用STARTS和ENDS子句来定义事件的有效期。您可以通过等待几分钟并检查messages表来测试此重复事件。
SELECT * FROM messages;
+----+----------------------------+---------------------+
| id | message | created_at |
+----+----------------------------+---------------------+
| 1 | Test MySQL Event 1 | 2019-08-24 00:58:51 |
| 2 | Test MySQL Event 2 | 2019-08-24 01:03:51 |
| 3 | Test MySQL recurring Event | 2019-08-24 01:06:16 |
| 4 | Test MySQL recurring Event | 2019-08-24 01:07:16 |
...
删除MySQL事件
要删除现有事件,请使用以下 DROP EVENT语句:
DROP EVENT [IF EXIST] event_name;
例如,要删除 test_event_03事件,请使用以下语句:
DROP EVENT [IF EXIST] test_event_03;
在本教程中,您了解了MySQL事件,如何从数据库模式创建和删除事件。在下一个教程中,我们将向您展示如何更改现有事件。