程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL中的事件調度基礎學習教程

MySQL中的事件調度基礎學習教程

編輯:關於MYSQL數據庫

經常需要有一些定時任務在MySQL表上執行,例如統計、遷移、刪除無用數據等。之前的作法是利用Linux cron定時運行腳本,但是發現這樣的額外依賴有時並不方便,例如單機多實例部署時,就需要分別手動分別配置不同的cron任務,需要額外配置相應的用戶和權限;新環境部署時容易遺漏cron任務等。

MySQL提供了Event Scheduler,與Linux下的crontab類似,可以根據時間調度來運行任務,運行一次或多次。

完整的Event Schduler創建語句如下:

CREATE
  [DEFINER = { user | CURRENT_USER }]
  EVENT
  [IF NOT EXISTS]
  event_name
  ON 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}

一、調度Scheduler
MySQL中的調度可以是只運行一次,也可以指定時間間隔重復運行。其定義是在event定義的ON SCHEDULE子句中。該子句格式如下:

ON SCHEDULE
AT timestamp [+ INTERVAL interval] …
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval] …]
  [ENDS timestamp [+ INTERVAL interval] …]

其中,timestamp必須包括”年月日時分秒“,它參與表達式計算後,結果是datetime或者timestamp類型。

而時間間隔interval可以如下:

<數字> {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
      WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
      DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

其含義很清晰,如YEAR 年;QUARTER 季度;YEAR_MONTH 年+月;MINUTE_SECOND 分鐘+秒。

補充:

YEAR | QUARTER | MONTH | YEAR_MONTH 後台都轉換成MONTH,其他時間間隔都轉換成SECOND
ON SCHEDULE中的時間使用創建時本會話中的時區信息time_zone,這個時區默認是服務端的全局time_zone,也可能後續手動更新掉。這些時間會轉化成UTC時間,存儲到mysql.event表中。
1.一次運行
AT直接指定時間,或者使用時間表達式計算得出確定的時間點。

示例:

AT '2006-02-10 23:59:00′   指定確切運行時間,本地時區。
AT current_timestamp + INTERVAL '1:15′ MINUTE_SECOND  指定1分15秒後運行。
2.多次運行
EVERY設置運行的時間間隔,這裡不能再指定[+ INTERVAL interval]。

指定STARTS、ENDS是可選的。

STARTS是指定重復運行的第一次是什麼時候。不指定的情況下,會在事件創建時運行第一次,即等價於STARTS CURRENT_TIMESTAMP!
ENDS告知MySQL結束重復運行的時間點。不指定的情況下,MySQL會永遠重復運行下去。
示例:

EVERY 5 WEEK  每5周運行一次,創建時運行第一次。
EVERY 3 DAY STARTS '2013-12-4 09:10:00′  從'2013-12-4 09:10:00′開始運行第一次,每隔3天運行一次。
EVERY 2 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE ENDS '2014-12-31 23:59:59′ 10分鐘後開始到2014年底,每兩個月運行一次。
二、事件Event
1.啟用Event Scheduler功能
Event是由一個特定的Event Scheduler線程執行的,運行過程中可以通過show full processlist查看其當前狀態信息,如:

7384313     event_scheduler     localhost     [NULL]     Daemon     3     Waiting on empty queue     [NULL]

默認事件調度Event Scheduler功能是未啟用的,需要配置全局參數event_scheduler,本參數可以動態設置,即時生效。

event_scheduler有如下三種取值:

OFF/0 關閉,默認值。不運行Event Scheduler線程,也就無法進行事件調度。設置為ON可以立即啟用。
ON/1 啟用。
DISABLED 禁用。同樣不運行Event Scheduler線程。只有在MySQL服務啟動時設置才有用。當event_scheduler是ON或者OFF時,不能在運行時設置event_scheduler為DISABLED。如果啟動時配置了event-scheduler=DISABLED,則運行時就不能設置為ON/OFF。換句話中,可以在MySQL服務啟動時設置為DISABLED,然後完全禁用了event_scheduler,不能動態調整。
所以,要啟用event_scheduler,運行時執行:

set global event_scheduler=on

要隨MySQL服務一起啟用,則在/etc/my.cnf中添加

[mysqld]
event-scheduler=on

2.創建事件的語法

CREATE
  [DEFINER = { user | CURRENT_USER }]
  EVENT
  [IF NOT EXISTS]
  event_name
  ON 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: 定義事件執行的時候檢查權限的用戶。
ON SCHEDULE schedule: 定義執行的時間和時間間隔。
ON COMPLETION [NOT] PRESERVE: 定義事件是一次執行還是永久執行,默認為一次執行,即NOT PRESERVE。
ENABLE | DISABLE | DISABLE ON SLAVE: 定義事件創建以後是開啟還是關閉,以及在從上關閉。如果是從服務器自動同步主上的創建事件的語句的話,會自動加上DISABLE ON SLAVE。
COMMENT 'comment': 定義事件的注釋。
 
3.更改事件的語法

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]

4.刪除事件的語法

DROP EVENT [IF EXISTS] event_name

5.Do子句
在Do子句中實現事件的具體邏輯,幾乎所有可以在存儲程序中運行的MySQL語句都可以在event中使用。

1)簡單SQL示例:

CREATE EVENT e_hourly
  ON SCHEDULE
   EVERY 1 HOUR
  COMMENT ‘Clears out sessions table each hour.'
  DO
   DELETE FROM site_activity.sessions;

2)復雜SQL示例:

delimiter |
CREATE EVENT e
  ON SCHEDULE
   EVERY 5 SECOND
  DO
   BEGIN
    DECLARE v INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    SET v = 0;
    WHILE v < 5 DO
     INSERT INTO t1 VALUES (0);
     UPDATE t2 SET s1 = s1 + 1;
     SET v = v + 1;
    END WHILE;
  END |
delimiter ;

3)Do子句中SQL的限制

基本上Do中可以使用任何在存儲程序(Stored Routine)中允許的SQL語句,而存儲程序中有些限制,event還有些額外的限制。

Stored Routine中如下語句不允許:

  • LOCK TABLES/UNLOCK TABLES
  • LOAD DATA與LOAD TABLE

支持動態SQL(PREPARE, EXECUTE, DEAALOCATE PREPARE)!但是PREPARE本身有些語句不允許執行。

INSERT DELAYED不會生效
EVENT的限制:

如果Do子句中包含ALTER EVENT子句,雖然能夠創建,但是運行時會出錯。
不要在Do子句中使用SELECT或SHOW這樣僅僅是查詢的語句,因為其輸出無法從外部獲取到。可以使用SELECT … INTO 這樣的形式將查詢結果保存起來。


5.查看EVENT
有如下方式可以查看event的信息:

mysql.event
information_schema.events
show events
show create event


三、event schedule其他注意點
MySQL保存了事件創建時的sql_mode作為其運行時的sql_mode;
如果在一個調度區間內任務沒有處理完成,新的調度依然會生成,這樣就會出現同時又多個任務在運行的情況。如果要避免多個任務同時存在,可以使用GET_LOCK()函數或者行鎖、表鎖。

四、    Mysql事件實戰
測試環境
創建一個用於測試的test表:

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `t1` datetime DEFAULT NULL,
 `id2` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

實戰1
Ø  創建一個每隔3秒往test表中插入一條數據的事件,代碼如下:

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES('',NOW());

Ø  創建一個10分鐘後清空test表數據的事件

CREATE EVENT IF NOT EXISTS test
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO TRUNCATE TABLE test.aaa;

Ø  創建一個在2012-08-23 00:00:00時刻清空test表數據的事件,代碼如下:

CREATE EVENT IF NOT EXISTS test
ON SCHEDULE
AT TIMESTAMP '2012-08-23 00:00:00'
DO TRUNCATE TABLE test;

Ø  創建一個從2012年8月22日21點45分開始到10分鐘後結束,運行每隔3秒往test表中插入一條數據的事件,代碼如下:

CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
STARTS '2012-08-22 21:49:00' 
ENDS '2012-08-22 21:49:00'+ INTERVAL 10 MINUTE
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES('',NOW());

 
 實戰2
通常的應用場景是通過事件來定期的調用存儲過程,下面是一個簡單的示例:
創建一個讓test表的id2字段每行加基數2的存儲過程,存儲過程代碼如下:

DROP PROCEDURE IF EXISTS test_add;
DELIMITER //
CREATE PROCEDURE test_add()
BEGIN
DECLARE 1_id INT DEFAULT 1;
DECLARE 1_id2 INT DEFAULT 0;
DECLARE error_status INT DEFAULT 0;
DECLARE datas CURSOR FOR SELECT id FROM test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1;
OPEN datas;
FETCH datas INTO 1_id;
REPEAT
SET 1_id2=1_id2+2;
UPDATE test SET id2=1_id2 WHERE id=1_id;
FETCH datas INTO 1_id;
UNTIL error_status
END REPEAT;
CLOSE datas;
END
//

事件設置2012-08-22 00:00:00時刻開始運行,每隔1調用一次存儲過程,40天後結束,代碼如下:

CREATE EVENT test ON SCHEDULE EVERY 1 DAY
STARTS '2012-08-22 00:00:00'
ENDS '2012-08-22 00:00:00'+INTERVAL 40 DAY
ON COMPLETION PRESERVE DO
CALL test_add();

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved