帶你了解MySQL中的事件調(diào)度器EVENT
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)文章!
相關(guān)文章
使用mysqladmin檢測(cè)MySQL運(yùn)行狀態(tài)的教程
這篇文章主要介紹了使用mysqladmin檢測(cè)MySQL運(yùn)行狀態(tài)的教程,包括mysqladmin工具簡(jiǎn)單的awk使用,需要的朋友可以參考下2015-06-06
mysql count詳解及函數(shù)實(shí)例代碼
這篇文章主要介紹了mysql count詳解及函數(shù)實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下2017-01-01
Mysql中基本語(yǔ)句優(yōu)化的十個(gè)原則小結(jié)
這篇文章主要給大家總結(jié)介紹了Mysql中基本語(yǔ)句優(yōu)化的十個(gè)原則,通過(guò)學(xué)習(xí)與記住它們,在構(gòu)造sql時(shí)可以養(yǎng)成良好的習(xí)慣,文中介紹的相對(duì)比較詳細(xì)與簡(jiǎn)單明了,需要的朋友們可以參考借鑒,下面來(lái)一起看看吧。2017-06-06
MySQL?數(shù)據(jù)庫(kù)整合攻略之表操作技巧與詳解
本文詳細(xì)介紹了MySQL數(shù)據(jù)庫(kù)中表的創(chuàng)建、查看、修改和刪除等操作技巧,感興趣的朋友一起看看吧2024-11-11
MySQL主從復(fù)制原理解析與最佳實(shí)踐過(guò)程
這篇文章主要介紹了MySQL主從復(fù)制原理解析與最佳實(shí)踐過(guò)程,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-05-05

