MySQL触发器

最新推荐文章于 2022-03-30 15:33:20 发布

原创最新推荐文章于 2022-03-30 15:33:20 发布 · 309 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了MySQL触发器的基本概念，包括其用途、限制及潜在问题。触发器能够在执行INSERT、UPDATE或DELETE操作时触发特定的动作，有助于实现数据一致性、业务逻辑等。文章还提供了一个实例，演示如何在班级学生表中添加新学生时同步更新学校学生表。

触发器可以让你在执行 INSERT、UPDATE或者 DETELE 的时候，执行一些特定的操作。可以在MySQL中指定是在 SQL 语句执行前还是在执行后触发。触发器本身没有返回值，不过它们可以读取或者改变触发 SQL 语句所影响的数据。所以，可以使用触发器实现一些强制限制，或者某些业务逻辑。

使用触发器可以减少客户端和服务器之间的通信，所以触发器可以简化应用逻辑，还可以提高性能。另外还可以用于自动更新反范式化数据或者汇总表数据。

MySQL触发器的实现非常简单，所以功能也有限。使用时要注意以下几点：

对每个表的每一个事件，最多只能定义一个触发器（换句话说，不能再AFTER INSERT 上定义两个触发器）。
MySQL 只支持“基于行的触发”，也就是说，触发器始终是针对一条记录的，而不是针对整个 SQL 语句的，如果变更的数据集非常大的话，效率会非常低。

下面这些触发器本身的限制也适用于 MySQL：

触发器可以掩盖服务器背后的工作，一个简单的 SQL 语句背后，因为触发器，可能包含了很多看不见的工作。例如，触发器可能会更新另一个相关表，那么这个触发器会让这条 SQL 语句影响的记录数翻倍。
触发器的问题很难排查，如果某个性能问题和触发器相关，会很难分析和定位。
触发器可能导致死锁和锁等待。

如果仅考虑性能，那么MySQL 触发器的实现中对服务器限制最大的就是它的“基于行的触发” 设计。因为性能的原因，很多时候无法使用触发器来维护汇总和缓存表。

触发器并不能一定保证更新的原子性。例如，一个触发器在更新 MyISAM 表的时候，如果遇到什么错误，是没办法执行回滚操作的。假设你在一个 MyISAM 表上建立一个 AFTER UPDATE 的触发器，用来更新另一个表。如果触发器在更新第二个表时遇到错误导致更新失败，那么第一个表的更新并不会回滚。

在InnoDB 表上的触发器是在同一个事务中完成的，所以它们执行的操作是原子的，原操作和触发器会同时失败和成功。不过，在 InnoDB 表上建触发器去检查数据的一致性，需要特别小心 MVCC ，稍不小心，你可能会获得错误的结果。

触发器其实非常有用，尤其是实现一些约束、系统维护任务，以及更新反范式化数据的时候。

还可以使用触发器来记录数据变更日志。比如需要先断开连接，然后修改数据，最后再将所有的修改重新合并回去的情况。一个简单的例子是，一组用户各自在自己的个人电脑上工作，但他们的操作都需要同步到一台主数据库上，然后主数据库会将他们所有人的操作都分发给每个人。实习这个系统需要做两次同步操作。触发器就是构建整个系统的一个好办法。每个人的电脑上都可以使用一个触发器来记录每一次数据的修改，并将其发送到主数据库中。然后，再用 MySQL 的复制将主数据库上的所有操作都复制一份到本地并应用。这里需要注意的是，如果触发器基于有自增主键的记录，并且使用的是基于语句的复制，那么自增长可能会在复制中出现不一致。

下面我们进行简单的小实验：

先看触发器的格式

DELIMITER //

CREATE TRIGGER triggername

BEFORE|AFTER INSERT|UPDATE ON tablename

FOR EACH ROW

BEGIN

    SQL语句；

END;

//

DELIMITER ;

举个栗子：

建一个班级学生表，一个学校学生表；实现这样一个触发器，每次班级新增学生时，学校学生表同样插入一条学生信息。

先建班级学生表和学校学生表（简单）：

mysql> create table stu_class(
    -> id int primary key,
    -> name varchar(30) not null,
    -> sclass varchar(30) not null
    -> );

mysql> create table stu_school(
    -> id int primary key,
    -> name varchar(30) not null,
    -> sclass varchar(30) not null
    -> );

再创建一个触发器：

mysql>DELIMITER //
    -> create trigger trigger_stu 
    -> before insert on stu_class 
    -> for each row 
    -> begin 
    -> insert into stu_school values(new.id,new.name,new.sclass); 
    -> end;
    -> //

mysql>DELIMITER ;

插入一条样本数据：

insert into stu_class values(1,'liming','chinese132');

查看学校表：

mysql> select * from stu_school;
+----+--------+------------+
| id | name   | sclass     |
+----+--------+------------+
|  1 | liming | chinese132 |
+----+--------+------------+
1 row in set (0.00 sec)