這篇文章為大家帶來了關於mysql中觸發器和事件的相關知識,其中包括觸發器使用注意事項、查看和刪除事件、事件使用注意事項等等,希望對大家有幫助。
觸發器
我們使用MySQL
的過程中可能會有下邊這些需求:
也就是我們在對錶中的記錄做增、刪、改操作前後都可能需要讓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
表示对具体语句影响的每一条记录都执行我们自定义的触发器内容:
小贴士: 如果触发器内容只包含一条语句,那也可以省略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 CREATE 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 CREATE EVENT 事件名;
登入後複製
删除事件:
这几个命令太简单了,就不举例子了啊~
事件使用注意事项
默认情况下,MySQL
服务器并不会帮助我们执行事件,除非我们使用下边的语句手动开启该功能:
mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)
mysql>
登入後複製
小贴士: event_scheduler其实是一个系统变量,它的值也可以在MySQL服务器启动的时候通过启动参数或者通过配置文件来设置event_scheduler的值。这些所谓的系统变量、启动参数、配置文件的各种东东并不是我们小白现在需要掌握的,大家忽略它们就好了~
推荐学习:mysql视频教程
以上是一起聊聊MySQL基礎之觸發器和事件的詳細內容。更多資訊請關注PHP中文網其他相關文章!