如何解析MySQL基礎中的觸發器和事件

2024-07-24 12:33:36

字體：大中小

來源：轉載

供稿：網友

　　本篇文章給大家分享的是有關如何分析MySQL基礎中的觸發器和事件，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

　　觸發器
　　我們使用MySQL的過程中可能會有下邊這些需求：

　　在向t1表插入或更新數據之前對自動對數據進行校驗，要求m1列的值必須在1~10之間，校驗規則如下：
　　如果插入的記錄的m1列的值小于1，則按1插入。
　　如果m1列的值大于10，則按10插入。
　　在向t1表中插入記錄之后自動把這條記錄插入到t2表。
　　也就是我們在對表中的記錄做增、刪、改操作前和后都可能需要讓MySQL服務器自動執行一些額外的語句，這個就是所謂的觸發器的應用場景。

　　創建觸發器
　　我們看一下定義觸發器的語句：

　　CREATE TRIGGER 觸發器名
　　{BEFORE|AFTER}
　　{INSERT|DELETE|UPDATE}
　　ON 表名
　　FOR EACH ROW
　　BEGIN
　　    觸發器內容
　　END
　　小貼士：

　　由大括號`{}`包裹并且內部用豎線`|`分隔的語句表示必須在給定的選項中選取一個值，比如`{BEFORE|AFTER}`表示必須在`BEFORE`、`AFTER`這兩個之間選取一個。

　　其中{BEFORE|AFTER}表示觸發器內容執行的時機，它們的含義如下：

　　名稱描述
　　BEFORE 表示在具體的語句執行之前就開始執行觸發器的內容
　　AFTER 表示在具體的語句執行之后才開始執行觸發器的內容
　　{INSERT|DELETE|UPDATE}表示具體的語句，MySQL中目前只支持對INSERT、DELETE、UPDATE這三種類型的語句設置觸發器。

　　FOR EACH ROW BEGIN ... END表示對具體語句影響的每一條記錄都執行我們自定義的觸發器內容：

　　對于INSERT語句來說，FOR EACH ROW影響的記錄就是我們準備插入的那些新記錄。
　　對于DELETE語句和UPDATE語句來說，FOR EACH ROW影響的記錄就是符合WHERE條件的那些記錄（如果語句中沒有WHERE條件，那就是代表全部的記錄）。
　　小貼士：如果觸發器內容只包含一條語句，那也可以省略BEGN、END這兩個詞兒。

　　因為MySQL服務器會對某條語句影響的所有記錄依次調用我們自定義的觸發器內容，所以針對每一條受影響的記錄，我們需要一種訪問該記錄中的內容的方式，MySQL提供了NEW和OLD兩個單詞來分別代表新記錄和舊記錄，它們在不同語句中的含義不同：

　　對于INSERT語句設置的觸發器來說，NEW代表準備插入的記錄，OLD無效。
　　對于DELETE語句設置的觸發器來說，OLD代表刪除前的記錄，NEW無效。
　　對于UPDATE語句設置的觸發器來說，NEW代表修改后的記錄，OLD代表修改前的記錄。
　　現在我們可以正式定義一個觸發器了：

　　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>
　　我們對t1表定義了一個名叫bi_t1的觸發器，它的意思就是在對t1表插入新記錄之前，對準備插入的每一條記錄都會執行BEGIN ... END之間的語句，NEW.列名表示當前待插入記錄指定列的值。現在t1表中一共有4條記錄：

　　mysql> SELECT * FROM t1;
　　+------+------+
　　| m1   | n1   |
　　+------+------+
　　|    1 | a    |
　　|    2 | b    |
　　|    3 | c    |
　　|    4 | d    |
　　+------+------+
　　4 rows in set (0.00 sec)

　　mysql>
　　我們現在執行一下插入語句并再次查看一下t1表的內容：

　　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>
　　這個INSERT語句影響的記錄有兩條，分別是(5, 'e')和(100, 'z')，這兩條記錄將分別執行我們自定義的觸發器內容。很顯然(5, 'e')被成功的插入到了t1表中，而(100, 'z')插入到表中后卻變成了(10, 'z')，這個就說明我們的bi_t1觸發器生效了！

　　小貼士：我們上邊定義的觸發器名`bi_t1`的`bi`是`before insert`的首字母縮寫，`t1`是表名。雖然對于觸發器的命名并沒有什么特殊的要求，但是習慣上還是建議大家把它定義我上邊例子中的形式，也就是`bi_表名`、`bd_表名`、`bu_表名`、`ai_表名`、`ad_表名`、`au_表名`的形式。

　　上邊只是舉了一個對INSERT語句設置BEFORE觸發器的例子，對DELETE和UPDATE操作設置BEFORE或者AFTER觸發器的過程是類似的，就不贅述了。

　　查看和刪除觸發器
　　查看當前數據庫中定義的所有觸發器的語句：

　　SHOW TRIGGERS;
　　查看某個具體的觸發器的定義：

　　SHOW CREATE TRIGGER 觸發器名;
　　刪除觸發器：

　　DROP TRIGGER 觸發器名;
　　這幾個命令太簡單了，就不舉例子了啊～

　　觸發器使用注意事項
　　觸發器內容中不能有輸出結果集的語句。比方說：
　　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的意思就是不允許在觸發器內容中返回結果集！
　　觸發器內容中NEW代表記錄的列的值可以被更改，OLD代表記錄的列的值無法更改。NEW代表新插入或著即將修改后的記錄，修改它的列的值將影響INSERT和UPDATE語句執行后的結果，而OLD代表修改或刪除之前的值，我們無法修改它。比方說如果我們非要這么寫那就會報錯的：
　　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>
　　可以看到提示的錯誤中顯示在觸發器中OLD代表的記錄是不可被更改的。
　　在BEFORE觸發器中，我們可以使用SET NEW.列名 = 某個值的形式來更改待插入記錄或者待更新記錄的某個列的值，但是這種操作不能在AFTER觸發器中使用，因為在執行AFTER觸發器的內容時記錄已經被插入完成或者更新完成了。比方說如果我們非要這么寫那就會報錯的：
　　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>
　　可以看到提示的錯誤中顯示在AFTER觸發器中是不允許更改NEW代表的記錄的。
　　如果我們的BEFORE觸發器內容執行過程中遇到了錯誤，那這個觸發器對應的具體語句將無法執行；如果具體的操作語句執行過程中遇到了錯誤，那與它對應的AFTER觸發器的內容將無法執行。
　　小貼士：對于支持事務的表，不論是執行觸發器內容還是具體操作語句過程中出現了錯誤，會把這個過程中所有的語句都回滾。當然，作為小白的我們并不知道啥是個事務，啥是個回滾，這些進階內容都在《MySQL是怎樣運行的：從根兒上理解MySQL》中呢～
　　事件
　　有時候我們想讓MySQL服務器在某個時間點或者每隔一段時間自動地執行一些語句，這時候就需要去創建一個事件。

　　創建事件
　　創建事件的語法如下：

　　CREATE EVENT 事件名
　　ON SCHEDULE
　　{
　　    AT 某個確定的時間點|
　　    EVERY 期望的時間間隔 [STARTS datetime][END datetime]
　　}
　　DO
　　BEGIN
　　    具體的語句
　　END
　　事件支持兩種類型的自動執行方式：

　　在某個確定的時間點執行。比方說：
　　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
　　我們在這個事件中指定了執行時間是'2019-09-04 15:48:54'，除了直接填某個時間常量，我們也可以填寫一些表達式：
　　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)表示該事件將在當前時間的兩天后執行。
　　每隔一段時間執行一次。比方說：
　　CREATE EVENT insert_t1
　　ON SCHEDULE
　　EVERY 1 HOUR
　　DO
　　BEGIN
　　    INSERT INTO t1(m1, n1) VALUES(6, 'f');
　　END
　　其中的EVERY 1 HOUR表示該事件將每隔1個小時執行一次。默認情況下，采用這種每隔一段時間執行一次的方式將從創建事件的事件開始，無限制的執行下去。我們也可以指定該事件開始執行時間和截止時間：
　　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個小時執行一次。
　　小貼士：表示事件間隔的單位除了HOUR，還可以用YEAR、QUARTER、MONTH、DAY、HOUR、 MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND這些單位，根據具體需求選用我們需要的時間間隔單位。
　　在創建好事件之后我們就不用管了，到了指定時間，MySQL服務器會幫我們自動執行的。

　　查看和刪除事件
　　查看當前數據庫中定義的所有事件的語句：

　　SHOW EVENTS;
　　查看某個具體的事件的定義：

　　SHOW CREATE EVENT 事件名;
　　刪除事件：

　　DROP EVENT 事件名;
　　這幾個命令太簡單了，就不舉例子了啊～

　　事件使用注意事項
　　默認情況下，MySQL服務器并不會幫助我們執行事件，除非我們使用下邊的語句手動開啟該功能：

　　mysql> SET GLOBAL event_scheduler = ON;
　　Query OK, 0 rows affected (0.00 sec)

　　mysql>
　　小貼士： event_scheduler其實是一個系統變量，它的值也可以在MySQL服務器啟動的時候通過啟動參數或者通過配置文件來設置event_scheduler的值。這些所謂的系統變量、啟動參數、配置文件的各種東東并不是我們小白現在需要掌握的，大家忽略它們就好了～

　　以上就是如何分析MySQL基礎中的觸發器和事件，小編相信有部分知識點可能是我們日常工作會見到或用到的。

（編輯：武林網）

上一篇：plsql能連接mysql嗎

下一篇：MySQL字符集和比較規則是啥