当前位置: 首页 >> 技术文章 >> MySQL中的事件调度器EVENT
MySQL中的事件调度器EVENT
发布时间:2020-04-01 发布人:巩飞 179

原文链接:https://www.modb.pro/db/23162 


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)

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


除了打开和关闭,还可以禁用,要禁用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}

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


我们通过一个实例来验证下。


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)


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

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)过一会,去表中查询数据。

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: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)