帶你了解MySQL中的事件調(diào)度器EVENT

更新時(shí)間：2020年08月13日 10:45:46 作者：數(shù)據(jù)和云

這篇文章主要介紹了帶你了解MySQL中的事件調(diào)度器EVENT，幫助大家更好的理解和學(xué)習(xí)MySQL，感興趣的朋友可以了解下

MySQL中的事件調(diào)度器，EVENT，也叫定時(shí)任務(wù)，類(lèi)似于Unix crontab或Windows任務(wù)調(diào)度程序。

EVENT由其名稱和所在的schema唯一標(biāo)識(shí)。

EVENT根據(jù)計(jì)劃執(zhí)行特定操作。操作由SQL語(yǔ)句組成，語(yǔ)句可以是BEGIN…END語(yǔ)句塊。EVENT可以是一次性的，也可以是重復(fù)性的。一次性EVENT只執(zhí)行一次，周期性EVENT以固定的間隔重復(fù)其操作，并且可以為周期性EVENT指定開(kāi)始日期和時(shí)間、結(jié)束日期和時(shí)間。（默認(rèn)情況下，定期EVENT在創(chuàng)建后立即開(kāi)始，并無(wú)限期地繼續(xù)，直到它被禁用或刪除。）

EVENT由一個(gè)特殊的事件調(diào)度器線程執(zhí)行，用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)

可以看到，默認(rèn)情況下，MySQL的EVENT沒(méi)有打開(kāi)，通過(guò)設(shè)置event_scheduler參數(shù)來(lái)打開(kāi)或者關(guān)閉EVENT。打開(kāi)后就會(huì)多一個(gè)event_scheduler，這個(gè)就是事件調(diào)度器線程。

除了打開(kāi)和關(guān)閉，還可以禁用，要禁用EVENT，請(qǐng)使用以下兩種方法之一：

啟動(dòng)MySQL時(shí)用命令行參數(shù)

--event-scheduler=DISABLED

在MySQL配置文件中配置參數(shù)

event_scheduler=DISABLED

MySQL 5.7中創(chuàng)建EVENT的完整語(yǔ)法如下：

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}

詳細(xì)說(shuō)明可以參考官網(wǎng)https://dev.mysql.com/doc/refman/5.7/en/create-event.html

我們通過(guò)一個(gè)實(shí)例來(lái)驗(yàn)證下。
1）創(chuàng)建一張表。

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）創(chuàng)建一個(gè)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）過(guò)一會(huì)，去表中查詢數(shù)據(jù)。

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)

從表里數(shù)據(jù)可以看到，創(chuàng)建的插數(shù)定時(shí)任務(wù)已經(jīng)在正常運(yùn)行了。

EVENT的詳細(xì)信息除了用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)

以上就是帶你了解MySQL中的事件調(diào)度器EVENT的詳細(xì)內(nèi)容，更多關(guān)于MySQL 事件調(diào)度器EVENT的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: