經(jīng)常需要有一些定時(shí)任務(wù)在MySQL表上執(zhí)行，例如統(tǒng)計(jì)、遷移、刪除無(wú)用數(shù)據(jù)等。之前的作法是利用Linux cron定時(shí)運(yùn)行腳本，但是發(fā)現(xiàn)這樣的額外依賴有時(shí)并不方便，例如單機(jī)多實(shí)例部署時(shí)，就需要分別手動(dòng)分別配置不同的cron任務(wù)，需要額外配置相應(yīng)的用戶和權(quán)限；新環(huán)境部署時(shí)容易遺漏cron任務(wù)等。

MySQL提供了Event Scheduler，與Linux下的crontab類似，可以根據(jù)時(shí)間調(diào)度來(lái)運(yùn)行任務(wù)，運(yùn)行一次或多次。

完整的Event Schduler創(chuàng)建語(yǔ)句如下：

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}

一、調(diào)度Scheduler
MySQL中的調(diào)度可以是只運(yùn)行一次，也可以指定時(shí)間間隔重復(fù)運(yùn)行。其定義是在event定義的ON SCHEDULE子句中。該子句格式如下：

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

其中，timestamp必須包括”年月日時(shí)分秒“，它參與表達(dá)式計(jì)算后，結(jié)果是datetime或者timestamp類型。

而時(shí)間間隔interval可以如下：

<數(shù)字> {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 分鐘+秒。

補(bǔ)充：

YEAR | QUARTER | MONTH | YEAR_MONTH 后臺(tái)都轉(zhuǎn)換成MONTH，其他時(shí)間間隔都轉(zhuǎn)換成SECOND
ON SCHEDULE中的時(shí)間使用創(chuàng)建時(shí)本會(huì)話中的時(shí)區(qū)信息time_zone，這個(gè)時(shí)區(qū)默認(rèn)是服務(wù)端的全局time_zone，也可能后續(xù)手動(dòng)更新掉。這些時(shí)間會(huì)轉(zhuǎn)化成UTC時(shí)間，存儲(chǔ)到mysql.event表中。
1.一次運(yùn)行
AT直接指定時(shí)間，或者使用時(shí)間表達(dá)式計(jì)算得出確定的時(shí)間點(diǎn)。

示例：

AT '2006-02-10 23:59:00′   指定確切運(yùn)行時(shí)間，本地時(shí)區(qū)。
AT current_timestamp + INTERVAL '1:15′ MINUTE_SECOND  指定1分15秒后運(yùn)行。
2.多次運(yùn)行
EVERY設(shè)置運(yùn)行的時(shí)間間隔，這里不能再指定[+ INTERVAL interval]。

指定STARTS、ENDS是可選的。

STARTS是指定重復(fù)運(yùn)行的第一次是什么時(shí)候。不指定的情況下，會(huì)在事件創(chuàng)建時(shí)運(yùn)行第一次，即等價(jià)于STARTS CURRENT_TIMESTAMP！
ENDS告知MySQL結(jié)束重復(fù)運(yùn)行的時(shí)間點(diǎn)。不指定的情況下，MySQL會(huì)永遠(yuǎn)重復(fù)運(yùn)行下去。
示例：

EVERY 5 WEEK  每5周運(yùn)行一次，創(chuàng)建時(shí)運(yùn)行第一次。
EVERY 3 DAY STARTS '2013-12-4 09:10:00′  從'2013-12-4 09:10:00′開(kāi)始運(yùn)行第一次，每隔3天運(yùn)行一次。
EVERY 2 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE ENDS '2014-12-31 23:59:59′ 10分鐘后開(kāi)始到2014年底，每?jī)蓚€(gè)月運(yùn)行一次。
二、事件Event
1.啟用Event Scheduler功能
Event是由一個(gè)特定的Event Scheduler線程執(zhí)行的，運(yùn)行過(guò)程中可以通過(guò)show full processlist查看其當(dāng)前狀態(tài)信息，如：

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

默認(rèn)事件調(diào)度Event Scheduler功能是未啟用的，需要配置全局參數(shù)event_scheduler，本參數(shù)可以動(dòng)態(tài)設(shè)置，即時(shí)生效。

event_scheduler有如下三種取值：

OFF/0 關(guān)閉，默認(rèn)值。不運(yùn)行Event Scheduler線程，也就無(wú)法進(jìn)行事件調(diào)度。設(shè)置為ON可以立即啟用。
ON/1 啟用。
DISABLED 禁用。同樣不運(yùn)行Event Scheduler線程。只有在MySQL服務(wù)啟動(dòng)時(shí)設(shè)置才有用。當(dāng)event_scheduler是ON或者OFF時(shí)，不能在運(yùn)行時(shí)設(shè)置event_scheduler為DISABLED。如果啟動(dòng)時(shí)配置了event-scheduler=DISABLED，則運(yùn)行時(shí)就不能設(shè)置為ON/OFF。換句話中，可以在MySQL服務(wù)啟動(dòng)時(shí)設(shè)置為DISABLED，然后完全禁用了event_scheduler，不能動(dòng)態(tài)調(diào)整。
所以，要啟用event_scheduler，運(yùn)行時(shí)執(zhí)行：

set global event_scheduler=on

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

[mysqld]
event-scheduler=on

2.創(chuàng)建事件的語(yǔ)法

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}

參數(shù)詳細(xì)說(shuō)明：
DEFINER: 定義事件執(zhí)行的時(shí)候檢查權(quán)限的用戶。
ON SCHEDULE schedule: 定義執(zhí)行的時(shí)間和時(shí)間間隔。
ON COMPLETION [NOT] PRESERVE: 定義事件是一次執(zhí)行還是永久執(zhí)行，默認(rèn)為一次執(zhí)行，即NOT PRESERVE。
ENABLE | DISABLE | DISABLE ON SLAVE: 定義事件創(chuàng)建以后是開(kāi)啟還是關(guān)閉，以及在從上關(guān)閉。如果是從服務(wù)器自動(dòng)同步主上的創(chuàng)建事件的語(yǔ)句的話，會(huì)自動(dòng)加上DISABLE ON SLAVE。
COMMENT 'comment': 定義事件的注釋。
 
3.更改事件的語(yǔ)法

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.刪除事件的語(yǔ)法

DROP EVENT [IF EXISTS] event_name

5.Do子句
在Do子句中實(shí)現(xiàn)事件的具體邏輯，幾乎所有可以在存儲(chǔ)程序中運(yùn)行的MySQL語(yǔ)句都可以在event中使用。

1）簡(jiǎn)單SQL示例：

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

2）復(fù)雜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中可以使用任何在存儲(chǔ)程序(Stored Routine)中允許的SQL語(yǔ)句，而存儲(chǔ)程序中有些限制，event還有些額外的限制。

Stored Routine中如下語(yǔ)句不允許：

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

支持動(dòng)態(tài)SQL(PREPARE, EXECUTE, DEAALOCATE PREPARE)！但是PREPARE本身有些語(yǔ)句不允許執(zhí)行。

INSERT DELAYED不會(huì)生效
EVENT的限制：

如果Do子句中包含ALTER EVENT子句，雖然能夠創(chuàng)建，但是運(yùn)行時(shí)會(huì)出錯(cuò)。
不要在Do子句中使用SELECT或SHOW這樣僅僅是查詢的語(yǔ)句，因?yàn)槠漭敵鰺o(wú)法從外部獲取到?？梢允褂肧ELECT … INTO 這樣的形式將查詢結(jié)果保存起來(lái)。

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

mysql.event
information_schema.events
show events
show create event

三、event schedule其他注意點(diǎn)
MySQL保存了事件創(chuàng)建時(shí)的sql_mode作為其運(yùn)行時(shí)的sql_mode;
如果在一個(gè)調(diào)度區(qū)間內(nèi)任務(wù)沒(méi)有處理完成，新的調(diào)度依然會(huì)生成，這樣就會(huì)出現(xiàn)同時(shí)又多個(gè)任務(wù)在運(yùn)行的情況。如果要避免多個(gè)任務(wù)同時(shí)存在，可以使用GET_LOCK()函數(shù)或者行鎖、表鎖。

四、    Mysql事件實(shí)戰(zhàn)
測(cè)試環(huán)境
創(chuàng)建一個(gè)用于測(cè)試的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

實(shí)戰(zhàn)1
Ø  創(chuàng)建一個(gè)每隔3秒往test表中插入一條數(shù)據(jù)的事件，代碼如下：

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

Ø  創(chuàng)建一個(gè)10分鐘后清空test表數(shù)據(jù)的事件

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

Ø  創(chuàng)建一個(gè)在2012-08-23 00:00:00時(shí)刻清空test表數(shù)據(jù)的事件，代碼如下：

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

Ø  創(chuàng)建一個(gè)從2012年8月22日21點(diǎn)45分開(kāi)始到10分鐘后結(jié)束，運(yùn)行每隔3秒往test表中插入一條數(shù)據(jù)的事件，代碼如下：

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());

 
 實(shí)戰(zhàn)2
通常的應(yīng)用場(chǎng)景是通過(guò)事件來(lái)定期的調(diào)用存儲(chǔ)過(guò)程，下面是一個(gè)簡(jiǎn)單的示例：
創(chuàng)建一個(gè)讓test表的id2字段每行加基數(shù)2的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程代碼如下：

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
//

事件設(shè)置2012-08-22 00:00:00時(shí)刻開(kāi)始運(yùn)行，每隔1調(diào)用一次存儲(chǔ)過(guò)程，40天后結(jié)束，代碼如下：

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();

最新評(píng)論