触发器可以让你在执行 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)