這篇文章主要介紹了MySQL中的事件調度基礎學習教程,本文介紹了對Event Scheduler的一些基本操作方法,需要的朋友可以參考下

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

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

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

1. CREATE 
2. [DEFINER = { user | CURRENT_USER }] 
3. EVENT 
4. [IF NOT EXISTS] 
5. event_name 
6. ON SCHEDULE schedule 
7. [ON COMPLETION [NOT] PRESERVE] 
8. [ENABLE | DISABLE | DISABLE ON SLAVE] 
9. [COMMENT 'comment'] 
10. DO event_body; 
11.  
12. schedule: 
13. AT timestamp [+ INTERVAL interval] … 
14. | EVERY interval 
15. [STARTS timestamp [+ INTERVAL interval] …] 
16. [ENDS timestamp [+ INTERVAL interval] …] 
17.  
18. interval: 
19. quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
20. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
21. DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} 

一、調度Scheduler

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

1. ON SCHEDULE 
2. AT timestamp [+ INTERVAL interval] … 
3. | EVERY interval 
4. [STARTS timestamp [+ INTERVAL interval] …] 
5. [ENDS timestamp [+ INTERVAL interval] …] 

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

而時間間隔interval可以如下：

1. <數字> {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
2. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | 
3. 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，運行時執行：

1. set global event_scheduler=on 

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

1. [mysqld] 
2. event-scheduler=on 

2.創建事件的語法

1. CREATE 
2. [DEFINER = { user | CURRENT_USER }] 
3. EVENT 
4. [IF NOT EXISTS] 
5. event_name 
6. ON SCHEDULE schedule 
7. [ON COMPLETION [NOT] PRESERVE] 
8. [ENABLE | DISABLE | DISABLE ON SLAVE] 
9. [COMMENT 'comment'] 
10. DO event_body; 
11.  
12. schedule: 
13. AT timestamp [+ INTERVAL interval] ... 
14. | EVERY interval 
15. [STARTS timestamp [+ INTERVAL interval] ...] 
16. [ENDS timestamp [+ INTERVAL interval] ...] 
17. interval: 
18. quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | 
19. WEEK | SECOND | YEAR_MONTH | DAY_HOUR | 
20. DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | 
21. 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.更改事件的語法

1. ALTER 
2. [DEFINER = { user | CURRENT_USER }] 
3. EVENT event_name 
4. [ON SCHEDULE schedule] 
5. [ON COMPLETION [NOT] PRESERVE] 
6. [RENAME TO new_event_name] 
7. [ENABLE | DISABLE | DISABLE ON SLAVE] 
8. [COMMENT 'comment'] 
9. [DO event_body] 

4.刪除事件的語法

1. DROP EVENT [IF EXISTS] event_name 

5.Do子句

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

1)簡單SQL示例：

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

2)復雜SQL示例：

1. delimiter | 
2. CREATE EVENT e 
3. ON SCHEDULE 
4. EVERY 5 SECOND 
5. DO 
6. BEGIN 
7. DECLARE v INTEGER; 
8. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; 
9. SET v = 0; 
10. WHILE v < 5 DO 
11. INSERT INTO t1 VALUES (0); 
12. UPDATE t2 SET s1 = s1 + 1; 
13. SET v = v + 1; 
14. END WHILE; 
15. END | 
16. 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這樣僅僅是查詢的語句，因為其輸出無法從外部獲取到?？梢允褂肧ELECT … INTO 這樣的形式將查詢結果保存起來。

5.查看EVENT

有如下方式可以查看event的信息：

1. mysql.event 
2. information_schema.events 
3. show events 
4. show create event 

三、event schedule其他注意點

MySQL保存了事件創建時的sql_mode作為其運行時的sql_mode;

如果在一個調度區間內任務沒有處理完成，新的調度依然會生成，這樣就會出現同時又多個任務在運行的情況。如果要避免多個任務同時存在，可以使用GET_LOCK()函數或者行鎖、表鎖。

四、 Mysql事件實戰 測試環境

創建一個用于測試的test表：

1. CREATE TABLE `test` ( 
2. `id` int(11) NOT NULL AUTO_INCREMENT, 
3. `t1` datetime DEFAULT NULL, 
4. `id2` int(11) NOT NULL DEFAULT '0', 
5. PRIMARY KEY (`id`) 
6. ) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8 

實戰1

Ø 創建一個每隔3秒往test表中插入一條數據的事件，代碼如下：

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

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

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

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

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

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

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

實戰2

通常的應用場景是通過事件來定期的調用存儲過程，下面是一個簡單的示例：

創建一個讓test表的id2字段每行加基數2的存儲過程，存儲過程代碼如下：

1. DROP PROCEDURE IF EXISTS test_add; 
2. DELIMITER // 
3. CREATE PROCEDURE test_add() 
4. BEGIN 
5. DECLARE 1_id INT DEFAULT 1; 
6. DECLARE 1_id2 INT DEFAULT 0; 
7. DECLARE error_status INT DEFAULT 0; 
8. DECLARE datas CURSOR FOR SELECT id FROM test; 
9. DECLARE CONTINUE HANDLER FOR NOT FOUND SET error_status=1; 
10. OPEN datas; 
11. FETCH datas INTO 1_id; 
12. REPEAT 
13. SET 1_id2=1_id2+2; 
14. UPDATE test SET id2=1_id2 WHERE id=1_id; 
15. FETCH datas INTO 1_id; 
16. UNTIL error_status 
17. END REPEAT; 
18. CLOSE datas; 
19. END 
20. // 

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

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