mysql 触发器_MySQL 5.7 深度解析（连载三）：触发器特征

我们知道，MySQL 触发器一直以来在功能和性能上都没有做任何更新，直到MySQL 5.7的发布。 虽然新版本只是在功能上做了相关更新，但是也足以让大家有小小的兴奋。现在来说说他主要的更新点。

在MySQL 5.6.x 之前，MySQL 一张表只能有六个触发器，分别为before/after这两个动作，针对insert/update/delete 这三种操作。

那么如果想在一张表上同一个动作并且同一种操作上做一系列的数据库业务处理，就必须把这些业务放在一个事务块里面，写一个非常大的触发器。这会使后期维护极其费劲。

在MySQL 5.7中，可以对一张表上的同一个动作并且是同一个操作定义多个不同的触发器。例如，我们可以对T1这张表定义两个”BEFORE UPDATE”触发器，默认的，当对T1做UPDATE前，这两个”BEFORE UPDATE"触发器会按照创建的顺序依次被触发。同时我们可以通过"trigger_order"语句指定FOLLOW或PRECEDES，即一个触发器“跟随”在另一个之后触发，或一个触发器“提前”另一个触发。

对触发器做这样的顺序拆分，虽然在性能上没啥大的区分，但是每个触发器功能上相对简单了许多，对后期维护就简单多了。

现在我们来举个例子，由于MySQL直到目前最新版也没有“角色“这个功能，如果我们想记录一堆用户的更新日志，只能把所有逻辑写到一个触发器里面逐个条件来判断，在5.6中，就只能写一个大的触发器来对用户操作记录日志，在这个触发器里面判断操作用户的角色，并写不同的日志表。

从MySQL5.7 开始就可以每个用户一个触发器来做，这样代码简单明了，可读性也很强，虽然可能会有些许冗余代码。

示例基础表结构：

CREATE TABLE t1 (

id INT ,rank INT, nickname VARCHAR(64)

) ENGINE INNODB;

ALTER TABLE t1 ADD PRIMARY KEY (id), ADD KEY idx_rank (rank));

用户记录日志表(管理员):

CREATE TABLE t1_log_admin (

id INT(11) NOT NULL, rank INT(11) DEFAULT NULL,

nickname VARCHAR(64) DEFAULT NULL,

update_time DATETIME DEFAULT NULL,

update_user VARCHAR(64) DEFAULT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8;

用户记录日志表(普通人员):

CREATE TABLE t1_log_normal LIKE t1_log_admin;

下面是在MySQL 5.6里，用简单的一个触发器来做用户更新的记录。

DELIMITER $$

USE `t_girl`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_update`$$

CREATE

/*!50017 DEFINER = 'root'@'localhost' */

TRIGGER `tr_t1_update` AFTER UPDATE ON `t1`

FOR EACH ROW BEGIN

DECLARE v_rank INT DEFAULT 0;

DECLARE v_nickname VARCHAR(64) DEFAULT '';

IF new.rank <> old.rank THEN

SET v_rank = new.rank;

ELSE

SET v_rank = old.rank;

END IF;

IF new.nickname <> old.nickname THEN

SET v_nickname = new.nickname;

ELSE

SET v_nickname = old.nickname;

END IF;

IF USER() LIKE 'root%' OR USER() LIKE 'ytt%' OR USER() LIKE 'david%' THEN

INSERT INTO t1_log_admin VALUES (old.id,v_rank,v_nickname,NOW(),USER());

ELSE

INSERT INTO t1_log_normal VALUES (old.id,v_rank,v_nickname,NOW(),USER());

END IF;

END;

$$

DELIMITER ;

此时我们用管理员用户来更新：

UPDATE t1 SET rank = 100, nickname = 'ytt' WHERE id = 1;

我们看到结果更新为：

mysql> select * from t1_log_admin;

+----+------+----------+---------------------+----------------+

| id | rank | nickname | update_time | update_user |

+----+------+----------+---------------------+----------------+

| 1 | 100 | ytt | 2015-11-10 10:22:46 | root@localhost |

+----+------+----------+---------------------+----------------+

1 row in set (0.00 sec)

接下来我们在换入普通用户来更新：

UPDATE t1 SET rank = 102, nickname = 'lily' WHERE id = 2;

mysql> select * from t1_log_normal;

+----+------+----------+---------------------+----------------+

| id | rank | nickname | update_time | update_user |

+----+------+----------+---------------------+----------------+

| 2 | 102 | lily | 2015-11-10 10:26:46 | lily@localhost |

+----+------+----------+---------------------+----------------+

1 row in set (0.00 sec)

以上UPDATE AFTER触发器是在MySQL 5.6下的使用方式，每张表只可以有一个UPDATE AFTER触发器，需要在这个触发器里面写大量的业务逻辑判断，从而决定不同的用户对数据的更改记录在不同的log日志表中。这还是一个非常简单的逻辑，可以想象如果是复杂的业务逻辑，将是一个非常庞大的触发器，无论是对触发器做变更管理，还是数据库运维，都会是一个不小的挑战。

在MySQL 5.7里，我们可以把每个用户都分开来做成自己的触发器，即可以创建两个触发器分别执行：普通用户更新数据后的触发器和管理员用户更新数据后的触发器。这样就把一个复杂的业务逻辑拆分开，写在不同的触发器里按顺序执行，从而减小了对变更和运维的难度。

管理员更新记录触发器：

DELIMITER $$

USE `t_girl`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_update_admin`$$

CREATE

/*!50017 DEFINER = 'root'@'localhost' */

TRIGGER `tr_t1_update_admin` AFTER UPDATE ON `t1`

FOR EACH ROW BEGIN

DECLARE v_rank INT DEFAULT 0;

DECLARE v_nickname VARCHAR(64) DEFAULT '';

IF new.rank <> old.rank THEN

SET v_rank = new.rank;

ELSE

SET v_rank = old.rank;

END IF;

IF new.nickname <> old.nickname THEN

SET v_nickname = new.nickname;

ELSE

SET v_nickname = old.nickname;

END IF;

IF USER() LIKE 'root%' OR USER() LIKE 'ytt%' OR USER() LIKE 'david%' THEN

INSERT INTO t1_log_admin VALUES (old.id,v_rank,v_nickname,NOW(),USER());

END IF;

END;

$$

DELIMITER ;

普通用户更新记录触发器：

DELIMITER $$

USE `t_girl`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr_t1_update_normal`$$

CREATE

/*!50017 DEFINER = 'root'@'localhost' */

TRIGGER `tr_t1_update_normal` AFTER UPDATE ON `t1`

FOR EACH ROW BEGIN

DECLARE v_rank INT DEFAULT 0;

DECLARE v_nickname VARCHAR(64) DEFAULT '';

IF new.rank <> old.rank THEN

SET v_rank = new.rank;

ELSE

SET v_rank = old.rank;

END IF;

IF new.nickname <> old.nickname THEN

SET v_nickname = new.nickname;

ELSE

SET v_nickname = old.nickname;

END IF;

IF USER() LIKE 'lily%' OR USER() LIKE 'lucy%' OR USER() LIKE 'simon%' THEN

INSERT INTO t1_log_normal VALUES (old.id,v_rank,v_nickname,NOW(),USER());

END IF;

END;

$$

DELIMITER ;

此时我们再用管理员用户来更新：

UPDATE t1 SET rank = 99, nickname = 'root' WHERE id =1;

mysql> select * from t1_log_admin;

+----+------+----------+---------------------+----------------+

| id | rank | nickname | update_time | update_user |

+----+------+----------+---------------------+----------------+

| 1 | 99 | root | 2015-11-10 10:35:42 | root@localhost |

+----+------+----------+---------------------+----------------+

1 row in set (0.00 sec)

接下来我们在换入普通用户来更新：

UPDATE t1 SET rank = 110,nickname = 'lily' WHERE id = 3;

mysql> select * from t1_log_normal;

+----+------+----------+---------------------+----------------+

| id | rank | nickname | update_time | update_user |

+----+------+----------+---------------------+----------------+

| 3 | 110 | lily | 2015-11-10 10:36:37 | lily@localhost |

+----+------+----------+---------------------+----------------+

1 row in set (0.00 sec)

在结尾，我要说明的是，虽然MySQL5.7 触发器现在功能上有所增强，但性能上并没有提升。按照数据库设计的一般性原理来说，触发器会降低OLTP在线事物处理的性能，特别是会增加后期数据进行水平扩展的难度。因而我建议大家只有在非常必要时适当的使用它，做到不滥用。

本文作者：爱可生资深数据库专家 杨涛涛