一、基本概念
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_name
ON 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_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();
每個月的一號凌晨1 點執行STAT()存儲過程:
復制代碼 代碼如下:
CREATE EVENT NOT EXISTS STAT
ON 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 ENABLE
DO
BEGIN
CALL STAT();
END