這篇文章將為大家詳細講解有關MySQL中的事件調度器EVENT是怎樣的，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
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)
可以看到，默認情況下，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: 1303306character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 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:00character_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: 1303306CHARACTER_SET_CLIENT: utf8 COLLATION_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: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)
關于MySQL中的事件調度器EVENT是怎樣的就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
