快捷導(dǎo)航

MySQL定時任務(wù)(EVENT事件)如何配置詳解

更新時間：2021年02月07日 10:16:32 作者：_陳哈哈

這篇文章主要介紹了MySQL定時任務(wù)(EVENT事件)如何配置詳解，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習或者工作具有一定的參考學(xué)習價值，需要的朋友們下面隨著小編來一起學(xué)習學(xué)習吧

一、事件（EVENT）是干什么的

自MySQL5.1.6起，增加了一個非常有特色的功能 - 事件調(diào)度器（Event Scheduler），可以用做定時執(zhí)行某些特定任務(wù)（例如：刪除記錄、數(shù)據(jù)統(tǒng)計報告、數(shù)據(jù)備份等等），來取代原先只能由操作系統(tǒng)的計劃任務(wù)來執(zhí)行的工作。
值得一提的是MySQL的事件調(diào)度器可以精確到每秒鐘執(zhí)行一個任務(wù)，而操作系統(tǒng)的計劃任務(wù)（如：Linux的cron）只能精確到每分鐘執(zhí)行一次。對于一些對數(shù)據(jù)實時性要求比較高的應(yīng)用（例如：股票、賠率、比分等）就非常適合。

事件有時也可以稱為臨時觸發(fā)器（temporal triggers），因為事件調(diào)度器是基于特定時間周期觸發(fā)來執(zhí)行某些任務(wù)，而觸發(fā)器（Triggers）是基于某個表所產(chǎn)生的事件觸發(fā)的，區(qū)別也就在這里。

二、開啟“事件”功能

使用“事件”功能之前必須確保event_scheduler已開啟

1、查詢功能是否開啟：

-- 方法一
SELECT @@event_scheduler;
-- 方法二
SHOW VARIABLES LIKE 'event%';

顯示 “ON”說明功能已開啟；如下圖：

mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON    |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

2、開啟、關(guān)閉功能命令：

-- 開啟功能命令：
SET GLOBAL event_scheduler = 1;
SET GLOBAL event_scheduler = ON;
-- 關(guān)閉功能命令：
SET GLOBAL event_scheduler = 0;
SET GLOBAL event_scheduler = OFF;

當然，通過命令開啟當數(shù)據(jù)庫重啟后會自動關(guān)閉；

持久化開啟方式：將event_scheduler=1寫到my.cnf配置文件中;如下圖：

在這里插入圖片描述

常用的事件操作命令：

關(guān)閉指定事件： ALTER EVENT 事件名稱 ON COMPLETION PRESERVE DISABLE;
開啟指定事件：ALTER EVENT 事件名稱 ON COMPLETION PRESERVE ENABLE;
查看當前事件：SHOW EVENTS ;

三、事件 - SQL語法創(chuàng)建

下面是EVENT事件創(chuàng)建語句，乍一看挺復(fù)雜的，讓我們拆開解讀一下

CREATE EVENT [IFNOT EXISTS] event_name
 　　 ON SCHEDULE schedule(調(diào)度時間設(shè)置)
 　　 [ON COMPLETION [NOT] PRESERVE]
 　　 [ENABLE | DISABLE | DISABLE ON SLAVE]
 　　 [COMMENT 'comment']
 　　 DO sql_statement;

SQL語法	說明
DEFINER	可選項，給指定用戶使用權(quán)限
IF NOT EXISTS	可選項，用于判斷要創(chuàng)建的事件是否存在
EVENT event_name	必選項，指定事件名稱，event_name的最大長度為64個字符，如果為指定event_name，則默認為當前的MySQL用戶名（不區(qū)分大小寫）
ON SCHEDULE schedule	必選項，這里的schedule用于定義執(zhí)行的時間和時間間隔，在下面我們詳細講解
ON COMPLETION [NOT] PRESERVE	可選項，配置事件執(zhí)行完一次后的處理方式；當為on completion preserve 的時候,當event到期了,event會被disable,但是該event還是會存在當為on completion not preserve的時候,當event到期的時候,該event會被自動刪除掉.
ENABLE、DISABLE、DISABLE ON SLAVE	可選項，用于指定事件的一種屬性。 ENABLE表示該事件是開啟的，也就是調(diào)度器檢查事件是否必選調(diào)用； DISABLE表示該事件是關(guān)閉的，也就是事件的聲明存儲到目錄中，但是調(diào)度器不會檢查它是否應(yīng)該調(diào)用； DISABLE ON SLAVE表示事件在從機中是關(guān)閉的。如果不指定這三個選擇中的任意一個，則在一個事件創(chuàng)建之后，它立即變?yōu)榛顒拥摹?/td>
COMMENT ‘comment'	可選項，用于定義事件的注釋
DO event_body	必選項，用于指定事件啟動時所要執(zhí)行的代碼。可以是任何有效的SQL語句、存儲過程或者一個計劃執(zhí)行的事件。如果包含多條語句，可以使用BEGIN…END復(fù)合結(jié)構(gòu)

schedule 調(diào)度時間配置語法：調(diào)度時間配置包括AT 和 EVERY兩種

AT timestamp [+ INTERVAL interval] ...
 | EVERY interval
 [STARTS timestamp [+ INTERVAL interval] ...]
 [ENDS timestamp [+ INTERVAL interval] ...]


-- INTERVAL中包含的時間單位如下:
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
 WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
 DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

1、創(chuàng)建 - 單次定時執(zhí)行事件

AT TIMESTAMP 時間字符串 [+ INTERVAL INTERVAL]

AT TIMESTAMP表示該事件只執(zhí)行一次，TIMESTAMP表示一個具體的時間點，后面可以加上一個時間間隔，表示在這個時間間隔后事件發(fā)生。[+ INTERVAL INTERVAL]表示延遲觸發(fā)時間；

需要注意的是，TIMESTAMP和具體字符串連用，如果不是具體字符串（如CURRENT_TIMESTAMP取當前時間等），則不加TIMESTAMP;

示例 1：往demo_1119表中插入一行數(shù)據(jù)，執(zhí)行時間：2020-11-20 00:00:00

CREATE EVENT demo_event2 
ON SCHEDULE AT TIMESTAMP '2020-11-20 00:00:00' 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

結(jié)果查詢：

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name  | createTime   |
+-----+-----------+---------------------+
| 145 | 陳哈哈 | 2020-11-20 00:00:00 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

示例 2：往demo_1119表中插入一行數(shù)據(jù)，執(zhí)行時間：當前時間往后5個小時；

CREATE EVENT demo_event2 
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 HOUR
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

2、創(chuàng)建 - 循環(huán)定時執(zhí)行事件

EVERY INTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

EVERY表示循環(huán)執(zhí)行該事件，其中STARTS子句用于指定開始時間；ENDS子句用于指定結(jié)束時間。

示例 1：從當前開始，每10秒往demo_1119表中插入一行數(shù)據(jù)

CREATE EVENT demo_event3 
ON SCHEDULE EVERY 10 SECOND 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name  | createTime   |
+-----+-----------+---------------------+
| 145 | 陳哈哈 | 2020-11-19 11:10:39 |
| 146 | 陳哈哈 | 2020-11-19 11:10:49 |
| 147 | 陳哈哈 | 2020-11-20 11:10:59 |
| 148 | 陳哈哈 | 2020-11-20 11:11:09 |
| 149 | 陳哈哈 | 2020-11-20 11:11:19 |
| 150 | 陳哈哈 | 2020-11-20 11:11:29 |
| 151 | 陳哈哈 | 2020-11-20 11:11:39 |
+-----+-----------+---------------------+
9 rows in set (0.00 sec)

示例 2：從2020-11-20 12:00:00開始，每10分鐘往demo_1119表中插入一行數(shù)據(jù)

CREATE EVENT demo_event4 
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-11-20 12:00:00' 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

mysql> select * from demo_1119;
+-----+-----------+---------------------+
| id | name  | createTime   |
+-----+-----------+---------------------+
| 152 | 陳哈哈 | 2020-11-20 12:00:00 |
| 153 | 陳哈哈 | 2020-11-20 12:10:00 |
| 154 | 陳哈哈 | 2020-11-20 12:20:00 |
| 155 | 陳哈哈 | 2020-11-20 12:30:00 |
| 156 | 陳哈哈 | 2020-11-20 12:40:00 |
+-----+-----------+---------------------+
5 rows in set (0.00 sec)

示例 3：從當前時間一小時后開始，每10分鐘往demo_1119表中插入一行數(shù)據(jù)；已經(jīng)測試過可用，我就不再貼冗余查詢的數(shù)據(jù)咯~

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP+INTERVAL 1 HOUR 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

示例4：從當前時間一天后開始，每1小時往demo_1119表中插入一行數(shù)據(jù)，三天后結(jié)束

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 HOUR 
STARTS CURRENT_TIMESTAMP+INTERVAL 1 DAY 
ENDS CURRENT_TIMESTAMP+INTERVAL 3 DAY 
ON COMPLETION PRESERVE 
DO INSERT INTO `demo_1119` (`id`, `name`, `createTime`) VALUES (null, '陳哈哈', NOW())

示例5：每天零點定時清一下demo_1119表數(shù)據(jù)

CREATE EVENT demo_event5 
ON SCHEDULE EVERY 1 DAY STARTS '2020-11-20 00:00:00' 
ON COMPLETION PRESERVE 
DO TRUNCATE table `demo_1119`

3、修改事件

修改事件語句跟創(chuàng)建語句如出一轍，語法如下：

ALTER EVENT event_name
 　　 [ONSCHEDULE schedule]
 　　 [old_NAME TO new_NAME]
 　　 [ON COMPLETION [NOT] PRESERVE]
 　　 [COMMENT 'comment']
 　　 [ENABLE | DISABLE]
 　　 [DO sql_statement]

關(guān)閉事件任務(wù)： ALTER EVENT 事件名稱 ON COMPLETION PRESERVE DISABLE;

開啟事件任務(wù)：ALTER EVENT 事件名稱 ON COMPLETION PRESERVE ENABLE;

4、刪除事件

DROP EVENT [IF EXISTS] event_name

四、事件 - 用Navicat創(chuàng)建（推薦）

很多小伙伴喜歡在命令行敲SQL語句，會有種專業(yè)感，但也可能是被領(lǐng)導(dǎo)道德綁架了~~

就像有些領(lǐng)導(dǎo)們覺得自己員工用Google就很cool😎😎，很帶勁；用百度查 csdn 就很 low。但作為菜狗的我還是喜歡用我的Navicat小工具和度娘。好了廢話不多說，我們來看看Navicat是如何創(chuàng)建EVENT事件的，GO！

如下圖，右鍵點擊創(chuàng)建新的事件

在這里插入圖片描述

創(chuàng)建事件中的定義一欄是寫執(zhí)行SQL的，可以包括一條或多條SQL語句、存儲過程等，計劃一欄是定義事件觸發(fā)時間的。如下圖，我在執(zhí)行過程中定義了一條插入語句。

在這里插入圖片描述

當然也可以同時寫多條SQL，中間帶分號。以BEGIN開頭，END結(jié)尾即可。

在這里插入圖片描述

打開計劃欄，是不是覺得很熟悉！在上個模塊我們都見過。為了使小伙伴們加深印象，我們在回顧一下吧。

在這里插入圖片描述

參數(shù)說明：

AT：表示該事件只執(zhí)行一次，可以設(shè)置一個具體的時間，也可以如圖中CURRENT_TIMESTAMP代表當前時間，后面可以加上一個時間間隔interval，表示在這個時間多久以后后事件發(fā)生，表示延遲觸發(fā)時間；

EVERY：循環(huán)執(zhí)行該事件，其中STARTS子句用于指定開始時間；
ENDS子句用于指定結(jié)束時間。
interval：表示從現(xiàn)在開始時間延遲多久以后的一個時間點，其值由一個數(shù)值和單位構(gòu)成。例如，使用“4 WEEK”表示4周后；使用“‘1:10' HOUR_MINUTE”表示1小時10分鐘后。間隔的距離用DATE_ADD()函數(shù)來支配。

INTERVAL中包含的時間單位如下：

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND |
YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

好了，讓我們通過一些實例來加深印象：示例 1：往demo_1119表中插入一行數(shù)據(jù)，執(zhí)行時間：2020-11-20 00:00:00

在這里插入圖片描述示例