18｜第十八话：基础篇-MySQL之触发器和事件简介

上篇文章简单说明了存储函数和存储过程，本篇文章继续来说一说剩下的触发器和事件。

为了方便说明，定义如下表：

-- 用户表
create table if not exists user(
 uid int primary key auto_increment,
 username varchar(50) not null,
 password varchar(50) not null
)default charset=utf8mb4;

-- 用户信息操作日志表
create table if not exists user_logs(
 id int primary key auto_increment,
 time timestamp,
 log_text varchar(100)
 )default charset=utf8mb4;

一、触发器

假设有一堆的需求需要实现：

需求1：当user表添加一行数据，则会自动在user_log添加日志记录，记录新增用户的信息
需求2：当user表更新一行数据，则会自动在user_log添加日志记录，记录修改前后的用户信息
需求3：当user表删除一行数据，则会自动在user_log添加日志记录，记录删除前的用户信息

当然了，这个需求可以在业务代码中使用多条SQL组合实现，具体实现方式有很多，比较好的解决方式可以使用spring的切面来做这种操作日志的记录，与业务流程解耦，也可以用触发器来实现，不过触发器确实用的少，我们为了了解触发器的使用方法，因此这里决定使用触发器来实现。

我们在对表中的记录做增、删、改操作前和后都可能需要让MySQL服务器自动执行一些额外的语句，这个就是所谓的触发器的应用场景。

创建触发器

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

由大括号{}包裹并且内部用竖线|分隔的语句表示必须在给定的选项中选取一个值，比如{BEFORE|AFTER}表示必须在BEFORE、AFTER这两个之间选取一个。

我们逐一解释下每行的含义：

CREATE TRIGGER 触发器名：创建一个触发器，指定触发器的名字；
{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条件，那就是代表全部的记录）。

好了，下面我们来定义一个触发器：

-- 新增用户信息时的触发器
delimiter $
CREATE TRIGGER user_add_trigger
AFTER
INSERT
ON user
FOR EACH ROW
BEGIN
    insert into user_logs values(NULL,now(),concat('有新用户添加,用户为：',NEW.uid,'---',NEW.username));
END $

含义为：定义一个名字叫做user_add_trigger的触发器，AFTER INSERT ON user表示user表每次插入一条数据之后都会触发触发器中定义的内容，下面就是触发器的内容，表示每次往user表插入一条新的用户数据后，都会插入一条记录到user_logs中，注意到会将新插入的用户信息也保存下来。

这里使用了NEW，是什么含义呢？

因为MySQL服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。
对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。
对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

所以，user_logs中为了记录新插入的用户信息，需要使用NEW来实现。

我们来看下实际效果：

mysql> insert into user(username,password) value ('fossi','fossi123456');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+-----+----------+-------------+
| uid | username | password    |
+-----+----------+-------------+
|   1 | fossi    | fossi123456 |
+-----+----------+-------------+
1 row in set (0.00 sec)

mysql> select * from user_logs;
+----+---------------------+------------------------------------------+
| id | time                | log_text                                 |
+----+---------------------+------------------------------------------+
|  1 | 2022-11-13 22:32:38 | 有新用户添加,用户为：1---fossi           |
+----+---------------------+------------------------------------------+
1 row in set (0.00 sec)

可以看到，完美实现了需求1。下面我们一鼓作气来实现需求2和需求3吧，相信有了需求1的实现经验，需求2和需求3也有了眉目。

下面定义两个新的触发器，分别来处理更新和删除两种情况：

-- 更新用户信息时的触发器
delimiter $
CREATE TRIGGER user_update_trigger
AFTER
UPDATE
ON user
FOR EACH ROW
BEGIN
    insert into user_logs values(NULL,now(),concat('有新用户更新,用户更新前的信息为：',OLD.uid,'---',OLD.username,'，用户更新后的信息为：',NEW.uid,'---',NEW.username));
END $

我们执行一个update语句看一看：

mysql> update user set username='hi fossi' where uid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+-----+----------+-------------+
| uid | username | password    |
+-----+----------+-------------+
|   1 | hi fossi | fossi123456 |
+-----+----------+-------------+
1 row in set (0.00 sec)

mysql> select * from user_logs;
+----+---------------------+---------------------------------------------------------------------------------------------------------+
| id | time                | log_text                                                                                                |
+----+---------------------+---------------------------------------------------------------------------------------------------------+
|  1 | 2022-11-13 22:32:38 | 有新用户添加,用户为：1---fossi                                                                          |
|  2 | 2022-11-13 22:42:07 | 有新用户更新,用户更新前的信息为：1---fossi，用户更新后的信息为：1---hi fossi                            |
+----+---------------------+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到，当对user信息做了更新后，我们的触发器自动往user_logs中插入了一条新的数据，成功地记录了更新前的用户数据和更新后的用户数据。下面来定义一个删除触发器。

-- 删除用户信息时的触发器
delimiter $
CREATE TRIGGER user_delete_trigger
AFTER
DELETE
ON user
FOR EACH ROW
BEGIN
    insert into user_logs values(NULL,now(),concat('有用户被删除,用户为：',OLD.uid,'---',OLD.username));
END $

我们来删除一条user数据，发现成功触发了触发器内容，新增了一条删除日志：

mysql> delete from user where uid=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> select * from user_logs;
+----+---------------------+---------------------------------------------------------------------------------------------------------+
| id | time                | log_text                                                                                                |
+----+---------------------+---------------------------------------------------------------------------------------------------------+
|  1 | 2022-11-13 22:32:38 | 有新用户添加,用户为：1---fossi                                                                          |
|  2 | 2022-11-13 22:42:07 | 有新用户更新,用户更新前的信息为：1---fossi，用户更新后的信息为：1---hi fossi                            |
|  3 | 2022-11-13 22:44:53 | 有用户被删除,用户为：1---hi fossi                                                                       |
+----+---------------------+---------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

好了，说完了触发器的定义和验证后，下面来看看如何查看触发器和删除触发器。

查看数据库中定义的所有触发器：

SHOW TRIGGERS;

查看某个具体的触发器的定义：

SHOW CREATE TRIGGER 触发器名;

删除触发器：

DROP TRIGGER 触发器名;

这些跟上一篇文章中查看和删除存储函数、存储过程都是的，只是关键字变成了TRIGGER而已，故而不再赘述了。

二、触发器的使用注意

1、触发器内容中不能有输出结果集的语句。

delimiter $
CREATE TRIGGER user_add_trigger
AFTER
UPDATE
ON user
FOR EACH ROW
BEGIN
    select NEW.uid,NEW.username;
END $

创建此触发器会报错：ERROR 1415 (0A000): Not allowed to return a result set from a trigge，显示的ERROR的意思就是不允许在触发器内容中返回结果集。

2、触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。

NEW代表新插入或即将修改后的记录，修改它的列的值将影响INSERT和UPDATE语句执行后的结果，而OLD代表修改或删除之前的值，我们无法修改它。比方说如果我们非要这么写那就会报错的：

delimiter $
CREATE TRIGGER user_test_trigger
AFTER
UPDATE
ON user
FOR EACH ROW
BEGIN
    SET OLD.username = 'test';
END $

显示报错：ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger！

3、在BEFORE触发器中，我们可以使用SET NEW.列名 = 某个值的形式来更改待插入记录或者待更新记录的某个列的值，但是这种操作不能在AFTER触发器中使用，因为在执行AFTER触发器的内容时记录已经被插入完成或者更新完成了。

delimiter $
CREATE TRIGGER user_test_trigger
AFTER
UPDATE
ON user
FOR EACH ROW
BEGIN
    SET NEW.username = 'test';
END $

显示报错：ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger！

4、如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

三、事件

有时候我们想让MySQL服务器在某个时间点或者每隔一段时间自动地执行一些语句，这时候可以去创建一个事件来实现。

创建事件的语法如下：

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点| 
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

可以看出，事件支持在某个确定的时间点执行或每隔一段时间执行一次。

十分容易理解，我们结合例子看下具体如何实现即可。

首先需要注意的是，默认情况下，MySQL服务器并不会帮助我们执行事件，除非我们使用下边的语句手动开启该功能：

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

在某个确定的时间点执行，比如我们希望在’2022-11-13 23:22:00’执行任务：

delimiter $
CREATE EVENT userlogs_schedule_test1_event
ON SCHEDULE
AT '2022-11-13 23:22:00'
DO
BEGIN
    insert into user_logs values(NULL,now(),concat('这是一个定时任务，将会在2022-11-13 23:22:00准时执行'));
END $

可以看到，在2022-11-13 23:22:01这个时间点自动插入了一条用户操作日志记录：

mysql> select * from user_logs;
+----+---------------------+---------------------------------------------------------------------+
| id | time                | log_text                                                            |
+----+---------------------+---------------------------------------------------------------------+
|  1 | 2022-11-13 23:22:01 | 这是一个定时任务，将会在2022-11-13 23:22:00准时执行                 |
+----+---------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

哈哈哈，虽然有了点延误，并不是准时在2022-11-13 23:22:00插入的，但是毕竟是新增日志，不用那么精准。

每隔一段时间执行一次，比如每隔10秒插入一条用户日志信息：

delimiter $
CREATE EVENT userlogs_schedule_test2_event
ON SCHEDULE
EVERY 10 SECOND
DO
BEGIN
    insert into user_logs values(NULL,now(),concat('这是一个定时任务，每隔10秒执行一次'));
END $

执行效果如下：

mysql> select * from user_logs;
+----+---------------------+----------------------------------------------------+
| id | time                | log_text                                           |
+----+---------------------+----------------------------------------------------+
|  1 | 2022-11-13 23:25:07 | 这是一个定时任务，每隔10秒执行一次                 |
|  2 | 2022-11-13 23:25:17 | 这是一个定时任务，每隔10秒执行一次                 |
|  3 | 2022-11-13 23:25:27 | 这是一个定时任务，每隔10秒执行一次                 |
+----+---------------------+----------------------------------------------------+
3 rows in set (0.00 sec)

查看当前数据库中定义的所有事件的语句：

SHOW EVENTS;

查看某个具体的事件的定义：

SHOW CREATE EVENT 事件名;

删除事件：

DROP EVENT 事件名;

四、总结

触发器和事件，可以在某些场景下帮助我们做一些自动化的工作，比如自动跑一些简单的定时任务或自动记录一些执行信息等，也算是比较方便的功能了，不过两者在我的实际开发工作中几乎没有使用过，可能原因有：

1、触发器或事件只要定义了，就可以自动执行，但是对于开发人员是隐藏的，如果开发人员不清楚有这么个隐藏的操作，很有可能会造成很多误解；相对来说，存储函数或存储过程需要显式调用，可以让研发人员发现有他们的存在。
2、面临复杂操作时，建议使用代码来实现，代码是比较容易阅读，出现问题时，易于开发人员进行修改和优化；
3、很多程序员可能没有使用过存储过程或触发器，如果mysql大量充斥这些，一方面程序员可能一时无从下手，一方面也增大了运维压力，无疑增加了系统维护的复杂性，也不利于数据库的迁移；

基于等等以上原因，存储函数、存储过程、触发器、事件几乎很少出现于我们的日常开发中，可能会在一些较老的系统中存在，但是我们仍然需要学习他们，因为只有了解他们，当哪一天遇到了才会游刃有余，也可以在一些场景中使用他们，比如不能立即修改代码的情况下要做一些自动化的数据操作等，总之一句话，这个世界千变万化，技多不压身。