MySQL數據庫高級（八）——事件

MySQL數據庫高級（八）——事件

一、事件簡介

1、事件簡介

事件是用來執行定時任務的一組SQL集，在時間到時會觸發。
一個事件可調用一次，也可周期性的啟動，由一個特定的事件調度器線程來管理的。
事件取代了原先只能由操作系統的計劃任務來執行的工作，而且MySQL的事件調度器可以精確到每秒鐘執行一個任務，而操作系統的計劃任務（如：Linux下的CRON或Windows下的任務計劃）只能精確到每分鐘執行一次。

2、事件的優點

A、對數據定時性操作不再依賴外部程序，而直接使用數據庫本身提供的功能。
B、可以實現每秒鐘執行一個任務，在一些對實時性要求較高的環境下非常實用。

3、事件的缺點

定時觸發，不可以調用。

4、事件的適用場景

對于每隔一段時間就有固定需求的操作，如創建表，刪除數據等操作，可以使用event來處理。

二、事件的操作

1、事件的創建

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;

event_name ：事件名，不能超64個字符，名字必須是當前Dateabase中唯一的，同一個數據庫不能有同名的event。創建事件時，可以同時指定Schema，語法結構為：schema_name.eventname。使用event常見的工作是創建表、插入數據、刪除數據、清空表、刪除表。為了規范事件命名，事件名稱需具備描述整個事件的能力。建議命名規則如下：動作名稱（INTO/FROM_）表名_TIME，如每天插入表中記錄事件命名，insert_into_tablename_day。
ON SCHEDULE schedule：計劃任務
schedule：調度規則，規定事件的執行時間與執行規則。決定event的執行時間和頻率（時間必須是將來的時間，過去的時間會出錯），有AT和EVERY兩種形式。
語法結構如下：
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}
AT timestamp，用來完成單次的計劃任務。
EVERY n quantity [STARTS timestamp] [ENDS timestamp]，用來完成重復的計劃任務。
時間戳可以是任意的TIMESTAMP 和DATETIME 數據類型，時間戳需要大于當前時間。
在重復的計劃任務中，時間（單位）的數量可以是任意非空（Not Null）的整數式，時間單位是關鍵詞：YEAR，MONTH，DAY，HOUR，MINUTE 或者SECOND。
AT timestamp一般用于只執行一次，一般可以使用當前時間加上延后的一段時間，例如：AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR；也可以定義一個時間常量，例如：AT '2006-02-10 23:59:00'；EVERY interval一般用于周期性執行，可以設定開始時間和結束時間。
ON COMPLETION [NOT] PRESERVE：事件到期后的操作，默認事件到期后會自動刪除。如果想保留事件使用ON COMPLETION PRESERVE；如果不想保留事件，可以設置ON COMPLETION NOT PRESERVE。
[ENABLE | DISABLE]：參數Enable和Disable表示設定事件的狀態。Enable表示系統將執行事件，Disable表示系統不執行事件。
[COMMENT 'comment']：增加注釋，注釋會出現在元數據中，存儲在information_schema表的COMMENT列，最大長度為64個字節。'comment'表示將注釋內容放在單引號之間，建議使用注釋以表達更全面的信息。
event_body：事件體，可以是單行SQL語法，或是BEGIN……END語句塊，或是存儲過程。

2、事件調度器的開啟

MySQL默認關閉事件調度器。
查看事件調度器信息

show global variables like '%event_scheduler%';
SHOW PROCESSLIST;

開啟事件調度器
set global event_scheduler=ON;
在MySQL的my.cnf配置文件中修改。
event_scheduler?=?1?#或者ON??

3、事件的查看

 SELECT * FROM mysql.event;
    SHOW EVENTS;
    SELECT * FROM information_schema.events;

4、刪除事件

DROP EVENT [IF?EXISTS] eventname;

5、禁用事件

ALTER EVENT eventname DSIABLE;

6、開啟事件

ALTER EVENT eventname ENABLE;

7、事件的修改

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]

三、事件實例

1、每隔一定時間插入一次記錄

創建一個表
CREATE TABLE event_table(timeline TIMESTAMP);
創建一個事件，每隔1秒插入一條記錄

CREATE EVENT insert_into_event_table_second
ON SCHEDULE EVERY 1 SECOND 
DO INSERT INTO schoolDB.event_table VALUES(CURRENT_TIMESTAMP);

過一定時間查看插入記錄
select * from event_table;
停止事件
alter event insert_into_event_table_second disable;
刪除事件
drop event insert_into_event_table_second;

2、特定時間插入一條記錄

創建一個事件在特定時間插入一條記錄

CREATE EVENT insert_into_event_table_time
ON SCHEDULE AT TIMESTAMP '2018-04-11 21:27:30'
DO INSERT INTO schoolDB.event_table values(CURRENT_TIMESTAMP);

在特定時間后查看插入記錄，已經插入記錄
select * from event_table;

3、特定時間清空表

創建一個事件，特定時間清空表，事件執行完后保留事件

CREATE EVENT truncate_event_table_time
ON SCHEDULE AT TIMESTAMP '2018-04-11 21:35:00'
ON COMPLETION PRESERVE
DO TRUNCATE TABLE schoolDB.event_table;

時間過后查詢表的內容，已經清空
select * from event_table;

4、某個事件段內定時清空表

創建一個事件，5天后開啟，每天定時清空表，一個月后停止執行

CREATE EVENT truncate_event_table_day
ON SCHEDULE EVERY 1 DAY 
STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ON COMPLETION PRESERVE
DO TRUNCATE TABLE schoolDB.event_table;