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

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

EVENT根據(jù)計(jì)劃執(zhí)行特定操作。操作由SQL語句組成，語句可以是BEGIN…END語句塊。EVENT可以是一次性的，也可以是重復(fù)性的。一次性EVENT只執(zhí)行一次，周期性EVENT以固定的間隔重復(fù)其操作，并且可以為周期性EVENT指定開始日期和時(shí)間、結(jié)束日期和時(shí)間。（默認(rèn)情況下，定期EVENT在創(chuàng)建后立即開始，并無限期地繼續(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沒有打開，通過設(shè)置event_scheduler參數(shù)來打開或者關(guān)閉EVENT。打開后就會(huì)多一個(gè)event_scheduler，這個(gè)就是事件調(diào)度器線程。

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

--event-scheduler=DISABLED

event_scheduler=DISABLED

MySQL 5.7中創(chuàng)建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}

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

我們通過一個(gè)實(shí)例來驗(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: gftestName: insert_date_testevent Definer: root@% Time zone: +08:00Type: RECURRING Execute at: NULL Interval value: 3 Interval field: SECOND Starts: 2020-03-26 13:53:10Ends: NULL Status: ENABLED Originator: 1303306character_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)

3）過一會(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: gftestname: insert_date_testeventbody: 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:10ends: 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:10ENDS: 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)

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