站長(zhǎng)資訊網(wǎng)本篇文章給大家?guī)砹岁P(guān)于mysql中觸發(fā)器和事件的相關(guān)知識(shí)，其中包括觸發(fā)器使用注意事項(xiàng)、查看和刪除事件、事件使用注意事項(xiàng)等等，希望對(duì)大家有幫助。
觸發(fā)器
我們使用MySQL的過程中可能會(huì)有下邊這些需求：
-
在向
t1表插入或更新數(shù)據(jù)之前對(duì)自動(dòng)對(duì)數(shù)據(jù)進(jìn)行校驗(yàn)，要求m1列的值必須在1~10之間，校驗(yàn)規(guī)則如下：- 如果插入的記錄的
m1列的值小于1，則按1插入。 - 如果
m1列的值大于10，則按10插入。
- 如果插入的記錄的
-
在向
t1表中插入記錄之后自動(dòng)把這條記錄插入到t2表。
也就是我們?cè)趯?duì)表中的記錄做增、刪、改操作前和后都可能需要讓MySQL服務(wù)器自動(dòng)執(zhí)行一些額外的語句，這個(gè)就是所謂的觸發(fā)器的應(yīng)用場(chǎng)景。
創(chuàng)建觸發(fā)器
我們看一下定義觸發(fā)器的語句：
CREATE TRIGGER 觸發(fā)器名 {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON 表名 FOR EACH ROW BEGIN 觸發(fā)器內(nèi)容 END
小貼士：
由大括號(hào)`{}`包裹并且內(nèi)部用豎線`|`分隔的語句表示必須在給定的選項(xiàng)中選取一個(gè)值，比如`{BEFORE|AFTER}`表示必須在`BEFORE`、`AFTER`這兩個(gè)之間選取一個(gè)。
其中{BEFORE|AFTER}表示觸發(fā)器內(nèi)容執(zhí)行的時(shí)機(jī)，它們的含義如下：
| 名稱 | 描述 |
|---|---|
BEFORE |
表示在具體的語句執(zhí)行之前就開始執(zhí)行觸發(fā)器的內(nèi)容 |
AFTER |
表示在具體的語句執(zhí)行之后才開始執(zhí)行觸發(fā)器的內(nèi)容 |
{INSERT|DELETE|UPDATE}表示具體的語句，MySQL中目前只支持對(duì)INSERT、DELETE、UPDATE這三種類型的語句設(shè)置觸發(fā)器。
FOR EACH ROW BEGIN ... END表示對(duì)具體語句影響的每一條記錄都執(zhí)行我們自定義的觸發(fā)器內(nèi)容：
-
對(duì)于
INSERT語句來說，FOR EACH ROW影響的記錄就是我們準(zhǔn)備插入的那些新記錄。 -
對(duì)于
DELETE語句和UPDATE語句來說，FOR EACH ROW影響的記錄就是符合WHERE條件的那些記錄（如果語句中沒有WHERE條件，那就是代表全部的記錄）。
小貼士： 如果觸發(fā)器內(nèi)容只包含一條語句，那也可以省略BEGN、END這兩個(gè)詞兒。
因?yàn)?code>MySQL服務(wù)器會(huì)對(duì)某條語句影響的所有記錄依次調(diào)用我們自定義的觸發(fā)器內(nèi)容，所以針對(duì)每一條受影響的記錄，我們需要一種訪問該記錄中的內(nèi)容的方式，MySQL提供了NEW和OLD兩個(gè)單詞來分別代表新記錄和舊記錄，它們?cè)诓煌Z句中的含義不同：
- 對(duì)于
INSERT語句設(shè)置的觸發(fā)器來說，NEW代表準(zhǔn)備插入的記錄，OLD無效。 - 對(duì)于
DELETE語句設(shè)置的觸發(fā)器來說，OLD代表刪除前的記錄，NEW無效。 - 對(duì)于
UPDATE語句設(shè)置的觸發(fā)器來說，NEW代表修改后的記錄，OLD代表修改前的記錄。
現(xiàn)在我們可以正式定義一個(gè)觸發(fā)器了：
mysql> delimiter $ mysql> CREATE TRIGGER bi_t1 -> BEFORE INSERT ON t1 -> FOR EACH ROW -> BEGIN -> IF NEW.m1 < 1 THEN -> SET NEW.m1 = 1; -> ELSEIF NEW.m1 > 10 THEN -> SET NEW.m1 = 10; -> END IF; -> END $ Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql>
我們對(duì)t1表定義了一個(gè)名叫bi_t1的觸發(fā)器，它的意思就是在對(duì)t1表插入新記錄之前，對(duì)準(zhǔn)備插入的每一條記錄都會(huì)執(zhí)行BEGIN ... END之間的語句，NEW.列名表示當(dāng)前待插入記錄指定列的值。現(xiàn)在t1表中一共有4條記錄：
mysql> SELECT * FROM t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +------+------+ 4 rows in set (0.00 sec) mysql>
我們現(xiàn)在執(zhí)行一下插入語句并再次查看一下t1表的內(nèi)容：
mysql> INSERT INTO t1(m1, n1) VALUES(5, 'e'), (100, 'z'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 10 | z | +------+------+ 6 rows in set (0.00 sec) mysql>
這個(gè)INSERT語句影響的記錄有兩條，分別是(5, 'e')和(100, 'z')，這兩條記錄將分別執(zhí)行我們自定義的觸發(fā)器內(nèi)容。很顯然(5, 'e')被成功的插入到了t1表中，而(100, 'z')插入到表中后卻變成了(10, 'z')，這個(gè)就說明我們的bi_t1觸發(fā)器生效了！
小貼士： 我們上邊定義的觸發(fā)器名`bi_t1`的`bi`是`before insert`的首字母縮寫，`t1`是表名。雖然對(duì)于觸發(fā)器的命名并沒有什么特殊的要求，但是習(xí)慣上還是建議大家把它定義我上邊例子中的形式，也就是`bi_表名`、`bd_表名`、`bu_表名`、`ai_表名`、`ad_表名`、`au_表名`的形式。
上邊只是舉了一個(gè)對(duì)INSERT語句設(shè)置BEFORE觸發(fā)器的例子，對(duì)DELETE和UPDATE操作設(shè)置BEFORE或者AFTER觸發(fā)器的過程是類似的，就不贅述了。
查看和刪除觸發(fā)器
查看當(dāng)前數(shù)據(jù)庫中定義的所有觸發(fā)器的語句：
SHOW TRIGGERS;
查看某個(gè)具體的觸發(fā)器的定義：
SHOW CREATE TRIGGER 觸發(fā)器名;
刪除觸發(fā)器：
DROP TRIGGER 觸發(fā)器名;
這幾個(gè)命令太簡(jiǎn)單了，就不舉例子了啊～
觸發(fā)器使用注意事項(xiàng)
-
觸發(fā)器內(nèi)容中不能有輸出結(jié)果集的語句。
比方說：
mysql> delimiter $ mysql> CREATE TRIGGER ai_t1 -> AFTER INSERT ON t1 -> FOR EACH ROW -> BEGIN -> SELECT NEW.m1, NEW.n1; -> END $ ERROR 1415 (0A000): Not allowed to return a result set from a trigger mysql>
顯示的
ERROR的意思就是不允許在觸發(fā)器內(nèi)容中返回結(jié)果集！ -
觸發(fā)器內(nèi)容中NEW代表記錄的列的值可以被更改，OLD代表記錄的列的值無法更改。
NEW代表新插入或著即將修改后的記錄，修改它的列的值將影響INSERT和UPDATE語句執(zhí)行后的結(jié)果，而OLD代表修改或刪除之前的值，我們無法修改它。比方說如果我們非要這么寫那就會(huì)報(bào)錯(cuò)的：mysql> delimiter $ mysql> CREATE TRIGGER bu_t1 -> BEFORE UPDATE ON t1 -> FOR EACH ROW -> BEGIN -> SET OLD.m1 = 1; -> END $ ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger mysql>
可以看到提示的錯(cuò)誤中顯示在觸發(fā)器中
OLD代表的記錄是不可被更改的。 -
在BEFORE觸發(fā)器中，我們可以使用
SET NEW.列名 = 某個(gè)值的形式來更改待插入記錄或者待更新記錄的某個(gè)列的值，但是這種操作不能在AFTER觸發(fā)器中使用，因?yàn)樵趫?zhí)行AFTER觸發(fā)器的內(nèi)容時(shí)記錄已經(jīng)被插入完成或者更新完成了。比方說如果我們非要這么寫那就會(huì)報(bào)錯(cuò)的：
mysql> delimiter $ mysql> CREATE TRIGGER ai_t1 -> AFTER INSERT ON t1 -> FOR EACH ROW -> BEGIN -> SET NEW.m1 = 1; -> END $ ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger mysql>
可以看到提示的錯(cuò)誤中顯示在AFTER觸發(fā)器中是不允許更改
NEW代表的記錄的。 -
如果我們的
BEFORE觸發(fā)器內(nèi)容執(zhí)行過程中遇到了錯(cuò)誤，那這個(gè)觸發(fā)器對(duì)應(yīng)的具體語句將無法執(zhí)行；如果具體的操作語句執(zhí)行過程中遇到了錯(cuò)誤，那與它對(duì)應(yīng)的AFTER觸發(fā)器的內(nèi)容將無法執(zhí)行。小貼士： 對(duì)于支持事務(wù)的表，不論是執(zhí)行觸發(fā)器內(nèi)容還是具體操作語句過程中出現(xiàn)了錯(cuò)誤，會(huì)把這個(gè)過程中所有的語句都回滾。當(dāng)然，作為小白的我們并不知道啥是個(gè)事務(wù)，啥是個(gè)回滾，這些進(jìn)階內(nèi)容都在《MySQL是怎樣運(yùn)行的：從根兒上理解MySQL》中呢～
事件
有時(shí)候我們想讓MySQL服務(wù)器在某個(gè)時(shí)間點(diǎn)或者每隔一段時(shí)間自動(dòng)地執(zhí)行一些語句，這時(shí)候就需要去創(chuàng)建一個(gè)事件。
創(chuàng)建事件
創(chuàng)建事件的語法如下：
CREATE EVENT 事件名 ON SCHEDULE { AT 某個(gè)確定的時(shí)間點(diǎn)| EVERY 期望的時(shí)間間隔 [STARTS datetime][END datetime] } DO BEGIN 具體的語句 END
事件支持兩種類型的自動(dòng)執(zhí)行方式：
-
在某個(gè)確定的時(shí)間點(diǎn)執(zhí)行。
比方說：
CREATE EVENT insert_t1_event ON SCHEDULE AT '2019-09-04 15:48:54' DO BEGIN INSERT INTO t1(m1, n1) VALUES(6, 'f'); END
我們?cè)谶@個(gè)
事件中指定了執(zhí)行時(shí)間是'2019-09-04 15:48:54'，除了直接填某個(gè)時(shí)間常量，我們也可以填寫一些表達(dá)式：CREATE EVENT insert_t1 ON SCHEDULE AT DATE_ADD(NOW(), INTERVAL 2 DAY) DO BEGIN INSERT INTO t1(m1, n1) VALUES(6, 'f'); END
其中的
DATE_ADD(NOW(), INTERVAL 2 DAY)表示該事件將在當(dāng)前時(shí)間的兩天后執(zhí)行。 -
每隔一段時(shí)間執(zhí)行一次。
比方說：
CREATE EVENT insert_t1 ON SCHEDULE EVERY 1 HOUR DO BEGIN INSERT INTO t1(m1, n1) VALUES(6, 'f'); END
其中的
EVERY 1 HOUR表示該事件將每隔1個(gè)小時(shí)執(zhí)行一次。默認(rèn)情況下，采用這種每隔一段時(shí)間執(zhí)行一次的方式將從創(chuàng)建事件的事件開始，無限制的執(zhí)行下去。我們也可以指定該事件開始執(zhí)行時(shí)間和截止時(shí)間：CREATE EVENT insert_t1 ON SCHEDULE EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54' DO BEGIN INSERT INTO t1(m1, n1) VALUES(6, 'f'); END
如上所示，該事件將從'2019-09-04 15:48:54'開始直到'2019-09-16 15:48:54'為止，中間每隔1個(gè)小時(shí)執(zhí)行一次。
小貼士： 表示事件間隔的單位除了HOUR，還可以用YEAR、QUARTER、MONTH、DAY、HOUR、 MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND這些單位，根據(jù)具體需求選用我們需要的時(shí)間間隔單位。
在創(chuàng)建好事件之后我們就不用管了，到了指定時(shí)間，MySQL服務(wù)器會(huì)幫我們自動(dòng)執(zhí)行的。
查看和刪除事件
查看當(dāng)前數(shù)據(jù)庫中定義的所有事件的語句：
SHOW EVENTS;
查看某個(gè)具體的事件的定義：
SHOW CREATE EVENT 事件名;
刪除事件：
DROP EVENT 事件名;
這幾個(gè)命令太簡(jiǎn)單了，就不舉例子了啊～
事件使用注意事項(xiàng)
默認(rèn)情況下，MySQL服務(wù)器并不會(huì)幫助我們執(zhí)行事件，除非我們使用下邊的語句手動(dòng)開啟該功能：
mysql> SET GLOBAL event_scheduler = ON; Query OK, 0 rows affected (0.00 sec) mysql>
小貼士： event_scheduler其實(shí)是一個(gè)系統(tǒng)變量，它的值也可以在MySQL服務(wù)器啟動(dòng)的時(shí)候通過啟動(dòng)參數(shù)或者通過配置文件來設(shè)置event_scheduler的值。這些所謂的系統(tǒng)變量、啟動(dòng)參數(shù)、配置文件的各種東東并不是我們小白現(xiàn)在需要掌握的，大家忽略它們就好了～
推薦學(xué)習(xí)：mysql視頻教程
