mysql中event的用法詳解

更新時間：2013年06月11日 16:47:09 作者：

本篇文章是對mysql中event的用法進行了詳細的分析介紹，需要的朋友參考下

一、基本概念
mysql5.1版本開始引進event概念。event既“時間觸發(fā)器”，與triggers的事件觸發(fā)不同，event類似與linux crontab計劃任務，用于時間觸發(fā)。通過單獨或調(diào)用存儲過程使用，在某一特定的時間點，觸發(fā)相關(guān)的SQL語句或存儲過程。

二、適用范圍
對于每隔一段時間就有固定需求的操作，如創(chuàng)建表，刪除數(shù)據(jù)等操作，可以使用event來處理。

例如：使用event在每月的1日凌晨1點自動創(chuàng)建下個月需要使用的三張表。

三、使用權(quán)限
單獨使用event調(diào)用SQL語句時，查看和創(chuàng)建需要用戶具有event權(quán)限，調(diào)用該SQL語句時，需要用戶具有執(zhí)行該SQL的權(quán)限。Event權(quán)限的設(shè)置保存在mysql.user表和mysql.db表的Event_priv字段中。

當event和procedure配合使用的時候，查看和創(chuàng)建存儲過程需要用戶具有create routine權(quán)限，調(diào)用存儲過程執(zhí)行時需要使用excute權(quán)限，存儲過程調(diào)用具體的SQL語句時，需要用戶具有執(zhí)行該SQL的權(quán)限。
查看EVENT命令有如下幾種：

（1）查詢mysql.event表；

（2）通過SHOW EVENTS命令；

（3）通過SHOW FULL EVENTS命令；

（4）通過查詢information_schema.events表

（5）SHOW CREATE EVENT。

總之，event的使用頻率較低建議使用root用戶進行創(chuàng)建和維護。

四、基本語法

4.1 開啟定時器
要使event起作用，MySQL的常量GLOBAL event_scheduler必須為on或者是1。
-- 查看是否開啟定時器

復制代碼代碼如下:

SHOW VARIABLES LIKE 'event_scheduler';

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

復制代碼代碼如下:

SET GLOBAL event_scheduler = 1;

當你設(shè)定事件計劃為0 或OFF，即關(guān)閉事件計劃進程的時候，不會有新的事件執(zhí)行，但現(xiàn)有的正在運行的事件會執(zhí)行到完畢

對于我們線上環(huán)境來說，使用event時，注意在主庫上開啟定時器，從庫上關(guān)閉定時器，event觸發(fā)所有操作均會記錄binlog進行主從同步，從庫上開啟定時器很可能造成卡庫。切換主庫后之后記得將新主庫上的定時器打開。
請?zhí)貏e注意！

4.2 創(chuàng)建事件
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不存在時才創(chuàng)建，否則忽略。建議不使用以保證event創(chuàng)建成功。

標注2：event_name

名稱最大長度可以是64個字節(jié)。名字必須是當前Dateabase中唯一的，同一個數(shù)據(jù)庫不能有同名的event。

使用event常見的工作是創(chuàng)建表、插入數(shù)據(jù)、刪除數(shù)據(jù)、清空表、刪除表。

為了避免命名規(guī)范帶來的不便，最好讓事件名稱具有描述整個事件的能力。建議命名規(guī)則如下為：動作名稱_（INTO/FROM_）表名_TIME，例如：
1. 每月創(chuàng)建（清空/刪除）fans表：
create(truncate/drop)_table_fans_month；
2. 每天從fans表插入（刪除）數(shù)據(jù)：
insert(delete)_into(from)_fans_day；
標注3：ON SCHEDULE

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

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

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

在兩種計劃任務中，時間戳可以是任意的TIMESTAMP 和DATETIME 數(shù)據(jù)類型，時間戳需要大于當前時間。

在重復的計劃任務中，時間（單位）的數(shù)量可以是任意非空（Not Null）的整數(shù)式，時間單位是關(guān)鍵詞：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參數(shù)表示"當這個事件不會再發(fā)生的時候"，即當單次計劃任務執(zhí)行完畢后或當重復性的計劃任務執(zhí)行到了ENDS階段。而PRESERVE的作用是使事件在執(zhí)行完畢后不會被Drop掉，建議使用該參數(shù)，以便于查看EVENT具體信息。

標注5：[ENABLE | DISABLE]
參數(shù)Enable和Disable表示設(shè)定事件的狀態(tài)。Enable表示系統(tǒng)將執(zhí)行這個事件。Disable表示系統(tǒng)不執(zhí)行該事件。

可以用如下命令關(guān)閉或開啟事件：

ALTER EVENT event_name ENABLE/DISABLE
標注6：[COMMENT 'comment']

注釋會出現(xiàn)在元數(shù)據(jù)中，它存儲在information_schema表的COMMENT列，最大長度為64個字節(jié)。'comment'表示將注釋內(nèi)容放在單引號之間，建議使用注釋以表達更全面的信息。

標注 7: DO sql_statement

DO sql_statement字段表示該event需要執(zhí)行的SQL語句或存儲過程。這里的SQL語句可以是復合語句，例如：
BEGIN
CREATE TABLE test1;//創(chuàng)建表（需要測試一下）
DROP TABLE test2;//刪除表
CALL proc_test1();//調(diào)用存儲過程
END

使用BEGIN和END標識符將復合SQL語句按照執(zhí)行順序放在之間。當然SQL語句是有限制的，對它的限制跟函數(shù)Function和觸發(fā)器Trigger 中對SQL語句的限制是一樣的，如果你在函數(shù)Function 和觸發(fā)器Trigger 中不能使用某些SQL，同樣的在EVENT中也不能使用。明確的來說有下面幾個：

LOCK TABLES
UNLOCK TABLES
CREATE EVENT
ALTER EVENT
LOAD DATA

4.3 執(zhí)行邏輯
For (已建立事件each event that has been created)
If (事件的狀態(tài)非DISABLE)
And (當前時間在ENDS時間之前)
And (當前時間在STARTS時間之后)
And (在上次執(zhí)行后經(jīng)過的時間)
And (沒有被執(zhí)行)
Then:
建立一個新的線程
傳遞事件的SQL語句給新的線程
(該線程在執(zhí)行完畢后會自動關(guān)閉)

4.4 修改事件
使用ALTER EVENT 來修改事件，具體的ALTER語法如下，與創(chuàng)建事件的語法類似：
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語句來刪除已經(jīng)創(chuàng)建的事件，語法如下：

DROP EVENT
[IF EXISTS]
event_name

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

五、常用實例
每隔一秒自動調(diào)用e_test()存儲過程

復制代碼代碼如下:

CREATE EVENT IF NOT EXISTS e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL e_test();

每個月的一號凌晨1 點執(zhí)行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

您可能感興趣的文章: