4. SQL编程之MySQL 触发器

SQL编程之MySQL 触发器先介绍一下触发器trigger的基本概念:

一个触发器一定是绑定在某个table上面的,不能像function和procedure那样可以单独存在;
它不需要人为的去调用,当达到特定条件后,由MySQL自动调用或者说自动触发,所以叫做触发器;
触发的事件分为 INSERT,UPDATE,DELETE三种;
解发的时机分为 BEFORE,AFTER 代表事件发生之前触发和事件发生之后触发;

由此可见我们可以定义6种触发器,分别是INSERT的前后,UPDATE的前后及DELETE的前后;

触发器名字的命名规范

全名: t2_b4_insert_do_not_update_pk

t1: 表名
b4: 触发时机,对应 before
insert:触发事件
do_not_update_pk: 功能描述

编写一个不允许修改主键的trigger

接下来,我们,BEFORE UPDATE为例,揭开trigger的神密面纱.
BEFORE UPDATE:从字面就很好理解是在执行UPDATE之前被自动触发,执行过程中出现错误,就会中断本次UPDATE,并可以抛出自定义的异常.在trigger中,有两个最重要的关键字NEW和OLD, 分别代表修改前的数据对象和修改后的数据对象,如果我们需要判断某个字段的值是否发生了更新可以这样做:

-- 如果是NOT NULL的字段
IF NEW.字段名 <> OLD.字段名 THEN
  --do something;
END IF;

-- 非NOT NULL的字段的判断方法, 大家可以仔细体会一下这种写法
IF IFNULL(NEW.字段名,1) <> IFNULL(OLD.字段名,1) THEN
  --do something;
END IF;

来一下完整的例子吧:

DELIMITER $$

DROP TRIGGER IF EXISTS T2_B4_UPDATE_DO_NOT_UPDATE_PK $$
CREATE TRIGGER T2_B4_UPDATE_DO_NOT_UPDATE_PK BEFORE UPDATE ON t2
FOR EACH ROW
BEGIN
  -- 功能: 不允许修改主建

  -- 错误提示
  DECLARE C_ERROR_MSG VARCHAR(100);
  
  -- 判断主键是否被修改了
  IF NEW.ID <> OLD.ID THEN
    -- 错误消息
    SET C_ERROR_MSG = 'B4TriggerError: Primary key does not allow modification';
    -- 抛出异常, 事务会被rollback
    SIGNAL SQLSTATE '09001' SET MESSAGE_TEXT = C_ERROR_MSG, MYSQL_ERRNO = 9001;
  END IF;
END$$

DELIMITER ;

测试效果:

# 修改之前的数据
mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
...
| 10 |  939 | UUUUUUUUUU |  939 | UUUUUUUUUU |
| 11 |  196 | FFFFFFFFFF |  196 | FFFFFFFFFF |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

# 修改id失败
mysql> update t2 set id=12 where id=11;
ERROR 9001 (09001): B4TriggerError: Primary key does not allow modification

# 确数据没有被修改
mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
...
| 10 |  939 | UUUUUUUUUU |  939 | UUUUUUUUUU |
| 11 |  196 | FFFFFFFFFF |  196 | FFFFFFFFFF |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

# 修改其他字段成功
mysql> update t2 set nid=999 where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
...
| 10 |  939 | UUUUUUUUUU |  939 | UUUUUUUUUU |
| 11 |  999 | FFFFFFFFFF |  196 | FFFFFFFFFF |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

一个有趣的测试:

mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
...
| 10 |  999 | UUUUUUUUUU |  939 | UUUUUUUUUU |
| 11 |  999 | FFFFFFFFFF |  196 | FFFFFFFFFF |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

# 开启一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t2 set nid=888 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t2 set nid=888 where id=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 这里发生了一个错误
mysql> update t2 set id=12 where id=11;
ERROR 9001 (09001): B4TriggerError: Primary key does not allow modification

# 提交这个事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# 此时,叶老师该登场了, 请问大家来猜猜nid=888呢,还是999? 经过同学们的认真思考投票后, 仍发现, 真理还是在少数人的手中, 哈哈...
mysql> select * from t2;
+----+------+------------+------+------------+
| id | nid  | cid        | n1   | c1         |
+----+------+------------+------+------------+
...
| 10 |  888 | UUUUUUUUUU |  939 | UUUUUUUUUU |
| 11 |  888 | FFFFFFFFFF |  196 | FFFFFFFFFF |
+----+------+------------+------+------------+
10 rows in set (0.00 sec)

纳尼!? 纳尼!? 纳尼!?
Innodb不是支持事务的原子性吗? 为什么会出现事务的部分提交,部分回滚. Monty,你个大骗子! Heikki,你个大骗子! Oracle,你也是大骗子! 叶老师,.....空气突然变得安静.....,你更是个大骗子!

喝了一杯凉水,稍微冷静了一下: 哦,不对,是trigger破坏了事务的原子性,trigger根本就不能用,伟哥才是最大的骗子,净整些没有用东西来浪费我们的时间.

一气之下喝下了一壶凉水,终于冷静下了, 如果trigger真有这么严重的问题, 为何还要设计它, 显得逼格高吗? 还是得从头来分析分析, 为什么会这样, 再回顾一下:

# 开启一个事务
mysql> begin;
# 正常的更新
mysql> update t2 set nid=888 where id=10;
mysql> update t2 set nid=888 where id=11;

# 发生了错误的更新,这里出错了...,这里出错了...,这里出错了...
mysql> update t2 set id=12 where id=11;
ERROR 9001 (09001): B4TriggerError: Primary key does not allow modification

# 提交这个事务
mysql> commit;

咦?! 啊?! 哦!?
一阵感叹过后,终于看出了问题. 伟哥, 你太坏了! 故意挖坑让我跳, 害得我把全世界的人都得罪了! 事务执行过程已经产生了错误, 说明事务就不能再提交, 你还故意提交? 还要让我们猜? 你是世上最坏的人, 对对对, 没有之一!

Monty, 你真是个好人, 这么好的产品,还免费给我们用
Heikki, 你真是个牛人, 搞个InnoDB,直接把MySQL带飞
Oracle大咖,你是全球最棒的数据库专家,对对对,也没有之一
叶老师,今后谁也动摇不了你在我心中男神的地位,一日为师终身为父, 更何况您是教我如何赚钱生存的老师, 都怪我学得太肤浅, 这么容易就上了伟哥的当
伟哥,你你你,你真的有当过兵吗?军人不都憨厚老实吗? 你怎么这么太调皮...

说归说, 闹归闹, 最后我们再认真小结一下:

来点段子能让你更容易记住重点;
一般情况是不会出现的上面这种情况的,因为事务原子性的设计本身就是需要每个动作都成功后再提交;
二般情况,就是像伟哥这样,明明有错误发生了,执意要commit,可以拿来坑人,可以拿来面试;
如果你全明白了的话,就可以出台挣钱了;
如果你还是似懂非懂的话,请继续往下看;

MySQL双班的同学可能有一部分同学是没有编程经验的,伟哥常说一句口头禅,"好人做到底,送佛送到西",再给大家写个伪代码,帮助大家进一步理解在编程语言中,是如何处理事务提交和回滚的:

# 不管是什么语言,异常(或叫错误)捕获语法,看起来像下面这样
try{
  # MySQL层面的逻辑
  mysql.query(update t2 set nid=888 where id=10)
  mysql.query(update t2 set nid=888 where id=11)
  mysql.query(update t2 set id=12 where id=11)
  
  # 非MySQL的逻辑处理, 如网络,文件等等
  other.do_other_things()
  
  # 开始提交,如果上面的任何地方出现在了异常,都会转到 error{}中执行,异常代码下面的语句直接被跳过
  mysql.query(commit)
}error{
  # 任何代码发生错误后都会转到这里开始执行,最常见的就是处理事务的回滚
  mysql.query(rollback)
}

# 伟哥你又骗人,我在bash手册中找了一天,也没发现这个语法
# 同学, bash那是脚本,不是编程语言,你没有认真听吴老师讲课吗?

# 又有同学说,伟哥,你这段程序是什么语言呀,我用Python,PHP,Java,GO,C++都试过了,怎么全部报错呀.伪代码属于什么语言呀?
# 同学,伪代码是一种虚拟代码,通常用来简化描述一种算法或程序的关键实现方法或过程,你全明白了吗?

现在应该全明白了吧? 不过伟哥劝你还先别出台挣钱, 咱们后面还有很多干货. 伟哥从来不骗人的. 这次是认真的.

编写一个自动记录行更新版本的trigger

再来一个例子:

# 先增加一个字段
mysql> alter table t2 add version int not null default 0 comment '版本号';
Query OK, 0 rows affected (0.65 sec)

mysql> select * from t2;
+----+------+------------+------+------------+---------+
| id | nid  | cid        | n1   | c1         | version |
+----+------+------------+------+------------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | RRRRRRRRRR |       0 |
|  2 |   69 | TTTTTTTTTT |   69 | TTTTTTTTTT |       0 |
|  3 |  609 | ssssssssss |  609 | ssssssssss |       0 |
|  4 |  949 | oooooooooo |  949 | oooooooooo |       0 |
|  5 |  559 | IIIIIIIIII |  559 | IIIIIIIIII |       0 |
|  6 |   71 | pppppppppp |   71 | pppppppppp |       0 |
|  7 |  325 | pppppppppp |  325 | pppppppppp |       0 |
|  8 |  533 | ssssssssss |  533 | ssssssssss |       0 |
| 10 |  888 | UUUUUUUUUU |  939 | UUUUUUUUUU |       0 |
| 11 |  888 | FFFFFFFFFF |  196 | FFFFFFFFFF |       0 |
+----+------+------------+------+------------+---------+
10 rows in set (0.00 sec)

再建一个trigger:

DELIMITER $$

DROP TRIGGER IF EXISTS T2_B4_UPDATE_AUTO_UPDATE_VERSION $$
CREATE TRIGGER T2_B4_UPDATE_AUTO_UPDATE_VERSION BEFORE UPDATE ON t2
FOR EACH ROW
BEGIN
  -- 功能: 自动更新版本号
  
  -- 累加版本号
  SET NEW.VERSION = OLD.VERSION + 1;
END$$

DELIMITER ;

看看效果:

# 创建新的触发器,请注意MySQL 5.6+的版本才为一个表创建多个同类型的触发器
# 这样的好处是,可以把不同功能或业务的逻辑定义到不同的trigger中,并以名称进行区分,便于维护
mysql> DELIMITER $$
mysql>
mysql> DROP TRIGGER IF EXISTS T2_B4_UPDATE_AUTO_UPDATE_VERSION $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER T2_B4_UPDATE_AUTO_UPDATE_VERSION BEFORE UPDATE ON t2
    -> FOR EACH ROW
    -> BEGIN
    ->   -- 功能: 自动更新版本号
    ->   
    ->   -- 累加版本号
    ->   SET NEW.VERSION = OLD.VERSION + 1;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;

测试一下:

# 请意观察 version
mysql> select * from t2 where id=1;
+----+------+------------+------+------------+---------+
| id | nid  | cid        | n1   | c1         | version |
+----+------+------------+------+------------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | RRRRRRRRRR |       0 |
+----+------+------------+------+------------+---------+
1 row in set (0.00 sec)

mysql> update t2 set c1='alvin zane' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 where id=1;
+----+------+------------+------+------------+---------+
| id | nid  | cid        | n1   | c1         | version |
+----+------+------------+------+------------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | alvin zane |       1 |
+----+------+------------+------+------------+---------+
1 row in set (0.00 sec)

mysql> update t2 set c1='I' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 where id=1;
+----+------+------------+------+------+---------+
| id | nid  | cid        | n1   | c1   | version |
+----+------+------------+------+------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | I    |       2 |
+----+------+------------+------+------+---------+
1 row in set (0.00 sec)

mysql> update t2 set c1='like' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 where id=1;
+----+------+------------+------+------+---------+
| id | nid  | cid        | n1   | c1   | version |
+----+------+------------+------+------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | like |       3 |
+----+------+------------+------+------+---------+
1 row in set (0.00 sec)

mysql> update t2 set c1='trigger' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 where id=1;
+----+------+------------+------+---------+---------+
| id | nid  | cid        | n1   | c1      | version |
+----+------+------------+------+---------+---------+
|  1 |  522 | RRRRRRRRRR |  522 | trigger |       4 |
+----+------+------------+------+---------+---------+
1 row in set (0.00 sec)

编写一个自动记录行更新历史记录的trigger

还没有完,再来一个例子:

-- 请留意t2_history的PRIMARY KEY

CREATE TABLE `t2_history` (
  `id` int(11) NOT NULL,  
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `nid` int(11) DEFAULT NULL,
  `cid` varchar(10) DEFAULT NULL,
  `n1` int(11) DEFAULT NULL,
  `c1` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`version`)
) ENGINE=InnoDB;

DELIMITER $$

DROP TRIGGER IF EXISTS T2_B4_UPDATE_AUTO_BACKUP $$
CREATE TRIGGER T2_B4_UPDATE_AUTO_BACKUP BEFORE UPDATE ON t2
FOR EACH ROW
BEGIN
  -- 功能: 自动备份历史副本
  
  -- INSERT到历史表,(ID,VERSION)总是记录,其它列只记录有变化的数据,默认为NULL
  INSERT INTO t2_history SET
    ID=OLD.ID,
    VERSION=OLD.VERSION,
    NID=IF(IFNULL(OLD.NID,1)<>IFNULL(NEW.NID,1),OLD.NID,NULL),
    CID=IF(IFNULL(OLD.CID,1)<>IFNULL(NEW.CID,1),OLD.CID,NULL),
    N1=IF(IFNULL(OLD.N1,1)<>IFNULL(NEW.N1,1),OLD.N1,NULL),
    C1=IF(IFNULL(OLD.C1,1)<>IFNULL(NEW.C1,1),OLD.C1,NULL);

END$$

DELIMITER ;

这里就省去创建table和trigger的过程了,直接开始测试:

mysql> update t2 set c1='I' where id=3;
mysql> update t2 set c1='love' where id=3;
mysql> update t2 set c1='python' where id=3;
mysql> update t2 set c1='too' where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2_history where id=3;
+----+---------+------+------+------+------------+
| id | version | nid  | cid  | n1   | c1         |
+----+---------+------+------+------+------------+
|  3 |       0 | NULL | NULL | NULL | ssssssssss |
|  3 |       1 | NULL | NULL | NULL | I          |
|  3 |       2 | NULL | NULL | NULL | love       |
|  3 |       3 | NULL | NULL | NULL | python     |
+----+---------+------+------+------+------------+
4 rows in set (0.00 sec)

# 查询c1字段的所有版本,只需要这样:
select id,version,c1 from t2_history where id=3
union all
select id,version,c1 from t2 where id=3;
+----+---------+------------+
| id | version | c1         |
+----+---------+------------+
|  3 |       0 | ssssssssss |
|  3 |       1 | I          |
|  3 |       2 | love       |
|  3 |       3 | python     |
|  3 |       4 | too        |
+----+---------+------------+
5 rows in set (0.00 sec)

再把修改人,修改时间也记录起来, 看起来一切都接近完美了. 再没有什么东西可以逃出DBA的法眼了,你懂的.

有了触发器后,正常的一个update除了指定表外,可能还会产生其它的数据更新,像上面的t2_history的新增. 这个情况下,binlog(row格式)会怎么记录呢? 从库的trigger会不会重复产生binlog呢?

好了,又到菠菜的时候了,请下注:

a. binlog只记录t2表的更新,t2_history表的由从库的trigger自动生成;
b. binlog同时记录t2,t2_history表的更新,从库trigger不会被触发;
c.binlog同时记录t2,t2_history表的更新,从库trigger会被触发;

请允许我再调皮一下,请自己动手验证你的答案吧.

小结trigger的相关概念一下:

trigger绑定在table下
它随时在监控table执行INSERT,UPDATE,DELETE的情况,
它可以获取到执行DML时的两种数据内容,分别是NEW.字段名和OLD.字段名
它可以改变DML的结果
它可以终止DML的执行,并抛出异常通知上游语言回滚事务
它可以产生新数据,如执行其它表的DML语句
它还可以function和procedure一起使用
更多东西,等你去挖掘

最后总结一下本节主要内容:

触发器的基本概念,触发事件,触发时间
触发器名字的命名规范
编写一个不允许修改主键的trigger
编写一个自动记录行更新版本的trigger
编写一个自动记录行更新历史记录的trigger

返回目录

最后编辑于：2018.12.27 20:22:33

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,519评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,842评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,544评论 0赞 330
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,742评论 1赞 271
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,646评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,027评论 1赞 275
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,513评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,169评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,324评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,268评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,299评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,996评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,591评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,667评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,911评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,288评论 2赞 345
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,871评论 2赞 341

4. SQL编程之MySQL 触发器

触发器名字的命名规范

编写一个不允许修改主键的trigger

编写一个自动记录行更新版本的trigger

编写一个自动记录行更新历史记录的trigger

推荐阅读更多精彩内容