带你了解MySQL中的事件调度器EVENT

MySQL中的事件调度器，EVENT，也叫定时任务，类似于Unix crontab或Windows任务调度程序。

EVENT由其名称和所在的schema唯一标识。

EVENT根据计划执行特定操作。操作由SQL语句组成，语句可以是BEGIN…END语句块。EVENT可以是一次性的，也可以是重复性的。一次性EVENT只执行一次，周期性EVENT以固定的间隔重复其操作，并且可以为周期性EVENT指定开始日期和时间、结束日期和时间。（默认情况下，定期EVENT在创建后立即开始，并无限期地继续，直到它被禁用或删除。）

EVENT由一个特殊的事件调度器线程执行，用SHOW PROCESSLIST可以查看。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

root@ database -one 13:44: [gftest]> show variables like '%scheduler%' ; + -----------------+-------+ | Variable_name  | Value | + -----------------+-------+ | event_scheduler | OFF  | + -----------------+-------+ 1 row in set (0.01 sec)   root@ database -one 13:46: [gftest]> show processlist; + --------+------+----------------------+-----------+---------+------+----------+------------------+ | Id   | User | Host         | db    | Command | Time | State  | Info       | + --------+------+----------------------+-----------+---------+------+----------+------------------+ ...... + --------+------+----------------------+-----------+---------+------+----------+------------------+ 245 rows in set (0.00 sec)   root@ database -one 13:46: [gftest]> set global event_scheduler=1; Query OK, 0 rows affected (0.00 sec)   root@ database -one 13:47: [gftest]> show variables like '%scheduler%' ; + -----------------+-------+ | Variable_name  | Value | + -----------------+-------+ | event_scheduler | ON  | + -----------------+-------+ 1 row in set (0.01 sec)   root@ database -one 13:47: [gftest]> show processlist; + --------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ | Id   | User      | Host         | db    | Command | Time | State         | Info       | + --------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ ...... | 121430 | event_scheduler | localhost      | NULL   | Daemon |  33 | Waiting on empty queue | NULL       | ...... + --------+-----------------+----------------------+-----------+---------+------+------------------------+------------------+ 246 rows in set (0.01 sec)

可以看到，默认情况下，MySQL的EVENT没有打开，通过设置event_scheduler参数来打开或者关闭EVENT。打开后就会多一个event_scheduler，这个就是事件调度器线程。

除了打开和关闭，还可以禁用，要禁用EVENT，请使用以下两种方法之一：

• 启动MySQL时用命令行参数

--event-scheduler=DISABLED

• 在MySQL配置文件中配置参数

event_scheduler=DISABLED

MySQL 5.7中创建EVENT的完整语法如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE    [DEFINER = user ]    EVENT    [IF NOT EXISTS]    event_name    ON SCHEDULE schedule    [ ON COMPLETION [ NOT ] PRESERVE]    [ENABLE | DISABLE | DISABLE ON SLAVE]    [COMMENT 'string' ]    DO event_body;   schedule:    AT timestamp [+ INTERVAL interval] ...   | EVERY interval    [STARTS timestamp [+ INTERVAL interval] ...]    [ENDS timestamp [+ INTERVAL interval] ...]   interval:    quantity { YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |         WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |         DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

详细说明可以参考官网https://dev.mysql.com/doc/refman/5.7/en/create-event.html

我们通过一个实例来验证下。
1）创建一张表。

?

1 2 3 4 5

root@ database -one 13:47: [gftest]> create table testevent(id int auto_increment primary key ,create_time datetime); Query OK, 0 rows affected (0.01 sec)   root@ database -one 13:50: [gftest]> select * from testevent; Empty set (0.00 sec)

2）创建一个EVENT，每3秒往表中插一条记录。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

root@ database -one 13:50: [gftest]> create event insert_date_testevent on schedule every 3 second do    -> insert into testevent(create_time) values (now()); Query OK, 0 rows affected (0.01 sec)   root@ database -one 13:53: [gftest]> show events \G *************************** 1. row ***************************           Db: gftest          Name : insert_date_testevent         Definer: root@%        Time zone: +08:00          Type: RECURRING       Execute at : NULL     Interval value: 3     Interval field: SECOND         Starts: 2020-03-26 13:53:10          Ends: NULL         Status: ENABLED       Originator: 1303306 character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.00 sec)

3）过一会，去表中查询数据。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

root@ database -one 13:53: [gftest]> select * from testevent; + ----+---------------------+ | id | create_time     | + ----+---------------------+ | 1 | 2020-03-26 13:53:10 | | 2 | 2020-03-26 13:53:13 | | 3 | 2020-03-26 13:53:16 | | 4 | 2020-03-26 13:53:19 | | 5 | 2020-03-26 13:53:22 | | 6 | 2020-03-26 13:53:25 | | 7 | 2020-03-26 13:53:28 | | 8 | 2020-03-26 13:53:31 | | 9 | 2020-03-26 13:53:34 | | 10 | 2020-03-26 13:53:37 | | 11 | 2020-03-26 13:53:40 | | 12 | 2020-03-26 13:53:43 | | 13 | 2020-03-26 13:53:46 | | 14 | 2020-03-26 13:53:49 | | 15 | 2020-03-26 13:53:52 | | 16 | 2020-03-26 13:53:55 | + ----+---------------------+ 16 rows in set (0.00 sec)

从表里数据可以看到，创建的插数定时任务已经在正常运行了。

EVENT的详细信息除了用show event命令，还可以从mysql.event或information_schema.events中查询，也可以用show create event命令查看。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

root@ database -one 00:09: [gftest]> select * from mysql.event \G *************************** 1. row ***************************           db: gftest          name : insert_date_testevent          body: insert into testevent(create_time) values (now())         definer: root@%       execute_at: NULL     interval_value: 3     interval_field: SECOND         created: 2020-03-26 13:53:10        modified: 2020-03-26 13:53:10      last_executed: 2020-03-26 16:09:37         starts: 2020-03-26 05:53:10          ends: NULL         status: ENABLED      on_completion: DROP        sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION         comment:       originator: 1303306        time_zone: +08:00 character_set_client: utf8 collation_connection: utf8_general_ci      db_collation: utf8_general_ci        body_utf8: insert into testevent(create_time) values (now()) 1 row in set (0.00 sec)   root@ database -one 00:09: [gftest]> select * from information_schema.events \G *************************** 1. row ***************************      EVENT_CATALOG: def      EVENT_SCHEMA: gftest       EVENT_NAME: insert_date_testevent         DEFINER: root@%        TIME_ZONE: +08:00       EVENT_BODY: SQL    EVENT_DEFINITION: insert into testevent(create_time) values (now())       EVENT_TYPE: RECURRING       EXECUTE_AT: NULL     INTERVAL_VALUE: 3     INTERVAL_FIELD: SECOND        SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION         STARTS: 2020-03-26 13:53:10          ENDS: NULL         STATUS: ENABLED      ON_COMPLETION: NOT PRESERVE         CREATED: 2020-03-26 13:53:10      LAST_ALTERED: 2020-03-26 13:53:10      LAST_EXECUTED: 2020-03-27 00:10:22      EVENT_COMMENT:       ORIGINATOR: 1303306 CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci   DATABASE_COLLATION: utf8_general_ci 1 row in set (0.02 sec)   root@ database -one 00:10: [gftest]> show create event insert_date_testevent \G *************************** 1. row ***************************          Event: insert_date_testevent        sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION        time_zone: +08:00      Create Event: CREATE DEFINER=`root`@`%` EVENT `insert_date_testevent` ON SCHEDULE EVERY 3 SECOND STARTS '2020-03-26 13:53:10' ON COMPLETION NOT PRESERVE ENABLE DO insert into testevent(create_time) values (now()) character_set_client: utf8 collation_connection: utf8_general_ci   Database Collation: utf8_general_ci 1 row in set (0.00 sec)

以上就是带你了解MySQL中的事件调度器EVENT的详细内容，更多关于MySQL 事件调度器EVENT的资料请关注服务器之家其它相关文章！

原文链接：https://cloud.tencent.com/developer/article/1608131