mysql定時(shí)器event詳解
一. 事件簡介
事件(event)是
MySQL在相應(yīng)的時(shí)刻調(diào)用的過程式數(shù)據(jù)庫對象。一個(gè)事件可調(diào)用一次,也可周期性的啟動(dòng),它由一個(gè)特定的線程來管理的,也就是所謂的“事件調(diào)度器”。
事件和觸發(fā)器類似,都是在某些事情發(fā)生的時(shí)候啟動(dòng)。當(dāng)數(shù)據(jù)庫上啟動(dòng)一條語句的時(shí)候,觸發(fā)器就啟動(dòng)了,而事件是根據(jù)調(diào)度事件來啟動(dòng)的。由于他們彼此相似,所以事件也稱為臨時(shí)性觸發(fā)器。
事件取代了原先只能由操作系統(tǒng)的計(jì)劃任務(wù)來執(zhí)行的工作,而且MySQL的事件調(diào)度器可以精確到每秒鐘執(zhí)行一個(gè)任務(wù),而操作系統(tǒng)的計(jì)劃任務(wù)(如:Linux下的CRON或Windows下的任務(wù)計(jì)劃)只能精確到每分鐘執(zhí)行一次。
二. 事件的優(yōu)缺點(diǎn)
2.1 優(yōu)點(diǎn)
一些對數(shù)據(jù)定時(shí)性操作不再依賴外部程序,而直接使用數(shù)據(jù)庫本身提供的功能。
可以實(shí)現(xiàn)每秒鐘執(zhí)行一個(gè)任務(wù),這在一些對實(shí)時(shí)性要求較高的環(huán)境下就非常實(shí)用了。
2.2 缺點(diǎn)
定時(shí)觸發(fā),不可以調(diào)用。
三. 創(chuàng)建事件
查看是否開啟evevt與開啟evevt。
MySQL evevt功能默認(rèn)是關(guān)閉的,可以使用下面的語句來看evevt的狀態(tài),如果是OFF或者0,表示是關(guān)閉的。
mysql> show VARIABLES LIKE 'event_schedu%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
開啟event功能:
mysql> SET GLOBAL event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show VARIABLES LIKE 'event_schedu%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
創(chuàng)建event舉例:
mysql> use test-_T
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
先創(chuàng)建存儲(chǔ)過程:
mysql> delimiter //
mysql> create procedure del_proc()
-> begin
-> INSERT INTO req(entname) SELECT t.`NAME` FROM (SELECT NAME, min(ZCSJ) AS mydate FROM test-_T.t_user_sign GROUP BY NAME) t WHERE t.mydate > NOW() - 36 * 60 * 1000 and t.`NAME` not in(SELECT entname from req);
-> UPDATE req SET `STATUS` = '0' where `STATUS` is null;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
執(zhí)行存儲(chǔ)過程的內(nèi)容:
mysql> call del_proc();
-> //
Query OK, 2 rows affected, 1 warning (0.29 sec)
創(chuàng)建事件deldata_event(其作用:每隔一分鐘自動(dòng)調(diào)用del_proc()存儲(chǔ)過程的內(nèi)容)
mysql> create event deldata_event
-> on schedule every 1 minute
-> on completion preserve disable
-> do call del_proc();
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
開啟事件deldata_event
mysql> alter event deldata_event on completion preserve enable;
-> //
Query OK, 0 rows affected (0.00 sec)
關(guān)閉事件deldata_event
mysql>alter event deldata_event on completion preserve disable;
查看自己創(chuàng)建的event:
mysql> select * from mysql.event;
+--------------+---------------+-----------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+--------------------------------------------+---------+------------+-----------+----------------------+----------------------+-----------------+-----------------+
| db | name | body | definer | execute_at | interval_value | interval_field | created | modified | last_executed | starts | ends | status | on_completion | sql_mode | comment | originator | time_zone | character_set_client | collation_connection | db_collation | body_utf8 |
+--------------+---------------+-----------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+--------------------------------------------+---------+------------+-----------+----------------------+----------------------+-----------------+-----------------+
| Test-_T | deldata_event | call del_proc() | mdba@% | NULL | 1 | MINUTE | 2017-05-09 10:21:57 | 2017-05-09 10:22:11 | 2017-05-09 02:24:57 | 2017-05-09 02:21:57 | NULL | ENABLED | PRESERVE | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | | 15 | SYSTEM | utf8 | utf8_general_ci | utf8_general_ci | call del_proc() |
+--------------+---------------+-----------------+---------+------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+------+---------+---------------+--------------------------------------------+---------+------------+-----------+----------------------+----------------------+-----------------+-----------------+
1 row in set (0.00 sec)
MySQL中查看Event的執(zhí)行情況
通過執(zhí)行如下的語句:
SELECT * FROM information_schema.EVENTS;
例如:
mysql> SELECT * FROM information_schema.events \G
*************************** 1. row ***************************
EVENT_CATALOG: def
EVENT_SCHEMA: ixinnuo_sjcj
EVENT_NAME: deldata_event
DEFINER: mdba@%
TIME_ZONE: SYSTEM
EVENT_BODY: SQL
EVENT_DEFINITION: call del_proc()
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: MINUTE
SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
STARTS: 2017-05-09 10:21:57
ENDS: NULL
STATUS: ENABLED
ON_COMPLETION: PRESERVE
CREATED: 2017-05-09 10:21:57
LAST_ALTERED: 2017-05-09 10:22:11
LAST_EXECUTED: 2017-05-11 08:43:57
EVENT_COMMENT:
ORIGINATOR: 15
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
可以看到相應(yīng)的庫中的作業(yè)信息,其中LAST_EXECUTED字段會(huì)反映出相應(yīng)的作業(yè)最近一次的執(zhí)行時(shí)間