程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中的事宜調劑基本進修教程

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}

1、調劑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歲尾，每兩個月運轉一次。
2、事宜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

3、event schedule其他留意點
MySQL保留了事宜創立時的sql_mode作為其運轉時的sql_mode;
假如在一個調劑區間內義務沒有處置完成，新的調劑仍然會生成，如許就會湧現同時又多個義務在運轉的情形。假如要防止多個義務同時存在，可使用GET_LOCK()函數或許行鎖、表鎖。

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