MySQL触发器总结

69 阅读 0 评论 46 点赞

我是靠谱客的博主纯情钥匙，最近开发中收集的这篇文章主要介绍MySQL触发器总结，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近在做需求时用到了触发器来解决一些数据同步问题，特此对其总结一番。

首先来看看触发器语法（关键字用大写字母表示）：

CREATE TRIGGER tigger_name
AFTER
UPDATE ON table_name
FOR EACH ROW
BEGIN
      IF (...) and (...) THEN #这里有一点要特别注意，条件判断相等是应该写 = ，而不是 == 
          IF (...) or (...) THEN 
              要执行的sql
          ELSE
              要执行的sql
          END IF;
      ELSE
          IF (...) or (...) THEN
              要执行的sql
          END IF;
      END IF;
END

这里对于触发器基本的使用就不进行过多的叙述，记录一个我在使用触发器过程中遇到的一个问题，相信这个问题对于大多数使用过触发器的人来说都遇到过：触发器的循环调用

问题背景
假设现在有一个需求(我就遇到了这样的需求)，需要将表table1中col1字段的修改同步到表table2，同时需要将表table2中的col2字段的修改同步到table1。
如果我们定义以下两个触发器：

在table1中定义：

CREATE TRIGGER `sync_col1_to_table2` 
AFTER 
UPDATE ON `table1`
FOR EACH ROW 
BEGIN
    UPDATE `table2` SET `col1` = NEW.`col1` WHERE XXX;
END

在table2中定义：

CREATE TRIGGER `sync_col2_to_table1` 
AFTER 
UPDATE ON `table2`
FOR EACH ROW 
BEGIN
    UPDATE `table1` SET `col2` = NEW.`col2` WHERE XXX;
END

表面看起来似乎很美好，但是这时候如果我们尝试去修改table1或者table2中的任一字段，数据库会报错：

Error: Can't update table 'table2' in stored function/trigger 
because it is already used by statement which invoked this stored function/trigger.

这个错误的意思就是，在执行修改的时候触发一个触发器，而触发器中会执行另外一个UPDATE语句，会接着触发另一个触发器… 从而形成循环触发，因此执行失败。
产生这个错误的原因是触发器的操作粒度过大，我们在触发器中仅仅想更改表中的一个字段，但是直接使用UPDATE语句的操作粒度是一整行，因此导致循环触发。
拿第一个触发器来讲，我们的本意是，当table1中的col2字段更新时，不应该触发UPDATEtable2SETcol1= NEW.col1WHERE XXX;这个操作(也就是我们期望字段粒度的触发器) ，但是事实上，table2中的任何一个字段被UPDATE，都会触发预先设定好的UPDATE类型触发器，也就是刚才所说的行粒度。

解决方案
为了解决这个问题，我们需要请出SQL中的分支控制语句，也就是我们常用的IF ELSE语句，通过IF语句来判断某一个字段的值是否更新，选择性的执行对应的UPDATE操作，从而实现字段粒度的触发器。

SQL代码如下：
table1中：

CREATE TRIGGER `sync_col1_to_table2` 
AFTER 
UPDATE ON `table1`
FOR EACH ROW 
BEGIN
    IF NEW.col1 != OLD.col1 THEN
        UPDATE `table2` SET `col1` = NEW.`col1` WHERE XXX;
    END IF;
END

table2中：

CREATE TRIGGER `sync_col2_to_table1` 
AFTER 
UPDATE ON `table2`
FOR EACH ROW 
BEGIN
    IF NEW.col2 != OLD.col2 THEN
        UPDATE `table1` SET `col2` = NEW.`col2` WHERE XXX;
    END IF;
END

这样一来，当table1中的col1字段有更新时，触发器经过判断，新旧字段值不一样，因此调用UPDATEtable2SETcol1= NEW.col1WHERE XXX;语句更新table2中的col1字段，这一调用同时会触发table2上的触发器，但是这个触发器经过IF语句的判断，发现新旧col2的值是一样的，因此不会调用下面的UPDATE语句，如此便实现了字段粒度的触发器，不会再造成表间循环触发的问题。
下面再来说说触发器的优缺点：

优点：

1、可以使用简单的配置来实现复杂的功能，这些功能如果想要在应用层面去实现往往需要比较多的代码量。

2、触发器能保证数据的一致性，举个栗子：当你要更新五个表的数据时，使用触发器不会出现只更新了两个表，另外三个表没有更新的情况（这点和事务的原子性相似），而如果用代码来实现这一系列更新操作则有可能会出现这种情况，比如，代码执行完第二条更新语句时，数据库突然挂了，那这个时候就出现如上问题了。

综上，触发器可以用来处理流水线式的业务需求，可以节省很多工作量，但触发器的使用也会带来很多问题，比如上面提到的触发器的循环调用。

缺点：

1、基于锁的操作中，触发器可能会导致锁等待或死锁。触发器执行失败，原来执行的SQL语名也会执行失败。而因为触发器导致的失败结果和失败原因，往往很难排查。

2、由于MySQL仅支持行触发模式，假如数据库系统中现在有一条触发器的功能是：当前表有新数据插入时，将数据同时插入另外一张表，此时，如果我们使用INSERT语句一次性向当前表中插入一千条数据(使用类似于INSERT INTO cur_table(id,name) VALUES(1,’周一‘) (2,'周二')....(1000,'周一千')的批量插入语句)，由于行触发的缘故，我们设定的插入触发器将会被触发1000次，这将带来严重的性能问题！因此，在对性能有要求的场景下不要使用触发器，如果一定要使用，务必保证触发器中的语句的开销不会很大，并且不会被频繁触发！
3、触发器会将业务和数据库捆绑在一起，使得系统的耦合度变高，当业务需求有变更时，系统可能变得难以维护。因此，在业务需求不稳定或者维护人员频繁变动的场景下尽量不要使用触发器。

4、MySQL触发器可能会关联到另外一张表或几张表的操作。因此，会导致数据库服务器负荷也会相应的增加一倍或几倍，如果出现因为触发器问题导致的性能问题，会很难定位问题位置和原因。

5、触发器不容易被注意，给后期维护带来困难。在数据因为触发器报错引起问题时，需要花时间去维护，在不知有触发器的前提下，将很难查找原因。