MySQL預存程序定時任務

標籤：style   blog   http   color   使用   strong   io   資料   

一、基本概念

mysql5.1版本開始引進event概念。event既“時間觸發器”，與triggers的事件觸發不同，event類似與linux crontab計劃任務，用於時間觸發。通過單獨或調用預存程序使用，在某一特定的時間點，觸發相關的SQL語句或預存程序。

二、適用範圍

對於每隔一段時間就有固定需求的操作，如建立表，刪除資料等操作，可以使用event來處理。
例如：使用event在每月的1日淩晨1點自動建立下個月需要使用的三張表。

三、使用許可權

單獨使用event調用SQL語句時，查看和建立需要使用者具有event許可權，調用該SQL語句時，需要使用者具有執行該SQL的許可權。Event許可權的設定儲存在mysql.user表和mysql.db表的Event_priv欄位中。
當event和procedure配合使用的時候，查看和建立預存程序需要使用者具有create routine許可權，調用預存程序執行時需要使用excute許可權，預存程序調用具體的SQL語句時，需要使用者具有執行該SQL的許可權。 查看EVENT命令有如下幾種：  

（1）查詢mysql.event表；
（2）通過SHOW EVENTS命令；
（3）通過SHOW FULL EVENTS命令；
（4）通過查詢information_schema.events表
（5）SHOW CREATE EVENT。   總之，event的使用頻率較低建議使用root使用者進行建立和維護。

四、基本文法

4.1 開啟定時器

要使event起作用，MySQL的常量GLOBAL event_scheduler必須為on或者是1。

-- 查看是否開啟定時器

SHOW VARIABLES LIKE ‘event_scheduler‘;

-- 開啟定時器 0：off 1：on 

SET GLOBAL event_scheduler = 1; 

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

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

4.2 建立事件

CREATE EVENT 的文法如下：

CREATE EVENT [IF NOT EXISTS] ---------------------------------------------*標註1

event_name -----------------------------------------------------*標註2
ON SCHEDULE schedule ------------------------------------*標註3 

[ON COMPLETION [NOT] PRESERVE] -----------------*標註4

[ENABLE | DISABLE] ----------------------------------------*標註5 

[COMMENT ‘comment‘] --------------------------------------*標註6 

DO sql_statement -----------------------------------------------*標註7

說明：
 
標註1：[IF NOT EXISTS]
 
使用IF NOT EXISTS，只有在同名event不存在時才建立，否則忽略。建議不使用以保證event建立成功。

標註2：event_name
 
名稱最大長度可以是64個位元組。名字必須是當前Dateabase中唯一的，同一個資料庫不能有同名的event。

使用event常見的工作是建立表、插入資料、刪除資料、清空表、刪除表。

為了避免命名規範帶來的不便，最好讓事件名稱具有描述整個事件的能力。建議命名規則如下為：動作名稱_（INTO/FROM_）表名_TIME，例如：

1.  每月建立（清空/刪除）fans表：  

create(truncate/drop)_table_fans_month；

2.  每天從fans表插入（刪除）資料： 

insert(delete)_into(from)_fans_day；

標註3：ON SCHEDULE

ON SCHEDULE 計劃任務，有兩種設定計劃任務的方式：

1. AT 時間戳記，用來完成單次的計劃任務。

2. EVERY 時間（單位）的數量時間單位[STARTS 時間戳記] [ENDS時間戳記]，用來完成重複的計劃任務。

在兩種計劃任務中，時間戳記可以是任意的TIMESTAMP 和DATETIME 資料類型，時間戳記需要大於目前時間。

在重複的計劃任務中，時間（單位）的數量可以是任意非空（Not Null）的整數式，時間單位是關鍵詞：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。

提示: 其他的時間單位也是合法的如：QUARTER, WEEK, YEAR_MONTH,DAY_HOUR,DAY_MINUTE,DAY_SECOND,HOUR_MINUTE,HOUR_SECOND, MINUTE_SECOND，不建議使用這些不標準的時間單位。

標註4： [ON COMPLETION [NOT] PRESERVE]   ON COMPLETION參數表示"當這個事件不會再發生的時候"，即當單次計劃任務執行完畢後或當重複性的計劃任務執行到了ENDS階段。而PRESERVE的作用是使事件在執行完

畢後不會被Drop掉，建議使用該參數，以便於查看EVENT具體資訊。

標註5：[ENABLE | DISABLE] 參數Enable和Disable表示設定事件的狀態。Enable表示系統將執行這個事件。Disable表示系統不執行該事件。

可以用如下命令關閉或開啟事件：

ALTER EVENT event_name  ENABLE/DISABLE

標註6：[COMMENT ‘comment‘]   注釋會出現在中繼資料中，它儲存在information_schema表的COMMENT列，最大長度為64個位元組。‘comment‘表示將注釋內容放在單引號之間，建議使用注釋以表達更全面的資訊。

標註 7: DO sql_statement   DO sql_statement欄位表示該event需要執行的SQL語句或預存程序。這裡的SQL語句可以是複合陳述式，例如：

BEGIN CREATE TABLE test1;//建立表（需要測試一下）DROP TABLE test2;//刪除表CALL proc_test1();//

調用預存程序 END

使用BEGIN和END標識符將複合SQL語句按照執行順序放在之間。當然SQL語句是有限制的，對它的限制跟函數Function和觸發器Trigger 中對SQL語句的限制是一樣的，如果你在函數Function 和觸發器Trigger 中不能使用某些

SQL，同樣的在EVENT中也不能使用。明確的來說有下面幾個：

LOCK TABLES UNLOCK TABLES CREATE EVENT ALTER EVENT LOAD DATA

4.3  執行邏輯

For (已建立事件each event that has been created)

If (事件的狀態非DISABLE)

And (目前時間在ENDS時間之前)

And (目前時間在STARTS時間之後)

And (在上次執行後經過的時間)

And (沒有被執行)

Then: 建立一個新的線程

傳遞事件的SQL語句給新的線程

(該線程在執行完畢後會自動關閉)
4.4 修改事件 使用ALTER EVENT 來修改事件，具體的ALTER文法如下，與建立事件的文法類似：

ALTER EVENT event_nameON SCHEDULE schedule [RENAME TO new_event_name] [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT ‘comment‘] DO sql_statement

4.5 刪除事件 EVENT使用DROP EVENT語句來刪除已經建立的事件，文法如下：

DROP EVENT [IF EXISTS] event_name

  但當一個事件正在運行中時，刪除該事件不會導致事件停止，事件會執行到完畢為止。使用DROP USER和DROP DATABASE 語句同時會將包含其中的事件刪除。

五、常用執行個體
每隔一秒自動調用e_test()預存程序

CREATE EVENT IF NOT EXISTS e_testON SCHEDULE EVERY 1 SECONDON COMPLETION PRESERVEDO CALL e_test();

每個月的一號淩晨1 點執行STAT()預存程序：

CREATE  EVENT  NOT EXISTS  STATON  SCHEDULE  EVERY  1  MONTH  STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)ON  COMPLETION  PRESERVE  ENABLEDOBEGINCALL STAT();END

五、自己編寫的例子 

delimiter //use test //drop table if exists demo //create table demo(     cid int,     createdtime datetime) //drop procedure if exists insertData //create procedure insertData()begin     declare howmanytimes int;     declare counter int default 75;     lable:while(3<=4) Do        set counter= counter+1;                insert  into demo(cid,createdtime) values(counter,now());        if counter/5=20 then                    leave lable;        end if;          end while;end //drop event if exists e_insertData //create event  e_insertDataon  schedule every 20 secondon  completion preservedo call insertData() //

轉自：http://www.jb51.net/article/38319.htm