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

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

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

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

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

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)

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

启动MySQL时用命令行参数

--event-scheduler=DISABLED

在MySQL配置文件中配置参数

event_scheduler=DISABLED

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

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}

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

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)

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: 1303306character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)

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命令查看。

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:00character_set_client: utf8collation_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: 1303306CHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci1 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: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)

更多相关文章

随机推荐