MySQL事件調度器event的使用

標籤：目前時間   修改   產生   沒有   主從   span   ble   char   資料   

Q：假設，有一個需求，希望在某一個時刻系統調用一個begin end執行一下；十分鐘以後執行一下begin end。亦或有一個需求，每個多長時間周期性執行begin end。那麼這個時候該怎麼辦呢？

A：

　　在Linux裡面可以使用at、crontab來實現上面的需求；MySQL裡面也有這樣的方法，就是event對象。

　　也被稱為MySQL事件調度器（Event Scheduler），可以在某一個時間點執行一個SQL語句或一個語句塊（BEGIN ... END）；或者每隔固定間隔重複執行。類似於Linux下的at、crontab或Windows下的Task Scheduler。

 

那麼如何使用event，步驟如下：

1、開啟資料庫的event執行調度

> 查看是否開啟定時器

mysql> show variables like ‘%event_scheduler%‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | OFF   |+-----------------+-------+

>開啟

0：off

1：on

mysql> set global event_scheduler=1;mysql> exitBye[[email protected] ~]# mysql -uroot -p123
mysql> show variables like ‘%event_scheduler%‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| event_scheduler | ON    |+-----------------+-------+

注意：

　　如果是設定事件計劃為0 或OFF，即關閉事件計划進程的時候，不會有新的事件執行，但現有的正在啟動並執行事件會執行到完畢。

　　對於線上環境來說，使用even時，注意在主庫上開啟定時器，從庫上關閉定時器，event觸發所有操作均會記錄binlog進行主從同步，從庫上開啟定時器很可能造成卡庫。切換主庫後之後記得將新主庫上的定時器開啟。

 

2、CREATE EVENT建立

CREATE　　[DEFINER = { user | CURRENT_USER }]EVENT　　[IF NOT EXISTS]event_nameON SCHEDULE schedule　　[ON COMPLETION [NOT] PRESERVE]　　[ENABLE | DISABLE | DISABLE ON SLAVE]　　[COMMENT ‘comment‘]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}

詳細解析：

①definer：指明該event的使用者，伺服器在執行該事件時，使用該使用者來檢查許可權。

　　預設使用者為目前使用者，即definer = current_user；

　　如果明確指明了definer，則必須遵循如下規則：

　　　　1.如果沒有super許可權，唯一允許的值就是自己目前使用者，而不能設定為其他使用者。

　　　　2.如果具有super許可權，則可以指定任意存在的使用者；如果指定的使用者不存在，則事件在執行時會報錯。

②if not exists：如果事件已經存在，則不會建立，也不會報錯。

③on schedule子句：指定何時執行該事件，以及如何執行該事件

　　1）at timestamp用於建立單次執行的事件，timestamp執行事件執行的時間(如果指定的時間是過去的時間，則會產生一個warning)，時間可以是具體的時間字串或者是一個datetime類型的運算式(如current_timestamp)：

　　　　如果要指定將來某個時間，直接使用at timestamp，例：at ‘2017-08-08 08:08:08‘；

　　　　如果要指定將來某個時間間隔，可利用interval關鍵字(interval關鍵字可以進行組合，at timestamp + INTERVAL 2 HOUR、 + INTERVAL 30 MINUTE)

　　2）every子句用於建立重複執行的事件，如果每分鐘執行一次，則可以：EVERY 1 MINUTE。

　　　　當然，every子句可以指定一個開始事件和結束時間，通過STARTS和ENDS關鍵字來表示，具體文法與前面類似

　　例如：EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK。

④通常情況下，如果一個事件到期已到期，則會被立即刪除。但是，create event定義中通過on completion preserve子句可以保留已到期的時間。

　　預設：ON COMPLETION NOT PRESERVE，也就是不儲存

⑤預設情況下，enable on slave，事件一旦建立後就立即開始執行；可以通過disable關鍵字來禁用該事件。

⑥comment子句用於給事件添加註釋。

⑦do子句用於指示事件需要執行的操作，可以是一條SQL語句，也可以是被begin...end包括的語句塊，也可以在語句塊中調用預存程序。

基本格式：

    CREATE EVENT event_name

    ON SCHEDULE  <schedule>

    DO <event_body>;

mysql> create event my_event      -> on schedule every 10 second     -> do update myschema.mytable set mycol = mycol + 1; 

樣本：建立一個計劃任務，每分鐘往表t2中添加資料（目前時間）

mysql> show events;Empty set (0.02 sec)mysql> create table t2(id int auto_increment primary key,t_time datetime);mysql> delimiter $$mysql> CREATE EVENT e_daily    -> 　　ON SCHEDULE    -> 　　EVERY 1 MINUTE    -> 　　　　COMMENT ‘Saves total number of sessions then clears the table each day‘    -> 　　DO    -> 　　　　BEGIN    -> 　　　　　　INSERT INTO t2 values (null,current_timestamp);    -> 　　　　END $$mysql> delimiter ;……過一段時間……mysql> select * from t2;+----+---------------------+| id | t_time              |+----+---------------------+|  1 | 2017-04-04 18:02:38 ||  2 | 2017-04-04 18:03:38 ||  3 | 2017-04-04 18:04:38 |…………

 

3、查看建立的計劃任務

mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;+------------+---------------------+| EVENT_NAME | LAST_EXECUTED       |+------------+---------------------+| e_daily    | 2017-04-04 18:02:38 |+------------+---------------------+mysql> show events\G;*************************** 1. row ***************************                  Db: db1                Name: e_daily             Definer: [email protected]           Time zone: SYSTEM                Type: RECURRING          Execute at: NULL      Interval value: 1      Interval field: MINUTE              Starts: 2017-04-04 18:02:38                Ends: NULL              Status: ENABLED          Originator: 0character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci

[[email protected] ~]# tail -1 /var/log/mysqld.log2017-04-04T08:01:16.311514Z 12 [Note] Event Scheduler: scheduler thread started with id 12

通過查看MySQL日誌，查看執行情況 。

 

4、修改alter event

ALTER     [DEFINER = { user | CURRENT_USER }] EVENT event_name     [ON SCHEDULE schedule]     [ON COMPLETION [NOT] PRESERVE]     [RENAME TO new_event_name]     [ENABLE | DISABLE | DISABLE ON SLAVE]     [COMMENT ‘comment‘]     [DO event_body] 

　　alter event語句可以修改事件的一個或多個屬性，文法與create event語句完全相同，唯一不同的是可以對事件重新命名，使用RENAME TO子句。

例如：

　　ALTER EVENT OLDDB.MYEVENT RENAME TO NEWDB.MYEVENT; 

 

5、刪drop event

DROP EVENT [IF EXISTS] event_name;

刪除一個定義的事件。

MySQL事件調度器event的使用