mysql触发器学习

喜欢ヅ旅行 2022-09-18 09:47 189阅读 0赞

1   引言  
  
Mysql的触发器和存储过程一样，都是嵌入到mysql的一段程序。触发器是mysql5新增的功能，目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本，很多程序比如fc-star管理端，sfrd(das)，dorado都会用到触发器程序，实现对于数据库增、删、改引起事件的关联操作。本文介绍了触发器的类型和基本使用方法，讲述了触发器使用中容易产生的误区，从mysql源码中得到触发器执行顺序的结论，本文最后是实战遭遇的触发器经典案例。没有特殊说明时，本文的实验均基于mysql5.0.45版本。  
  
2   Mysql触发器的类型  
  
2.1   Mysql触发器的基本使用  
  
创建触发器。创建触发器语法如下：  
  
CREATE TRIGGER trigger\_name trigger\_time trigger\_event  
ON tbl\_name FOR EACH ROW trigger\_stmt  
  
其中trigger\_name标识触发器名称，用户自行指定；  
  
trigger\_time标识触发时机，用before和after替换；  
  
trigger\_event标识触发事件，用insert，update和delete替换；  
  
tbl\_name标识建立触发器的表名，即在哪张表上建立触发器；  
  
trigger\_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；  
  
下面给出sfrd一个触发器实例：  
  
CREATE /\*!50017 DEFINER = 'root'@'localhost' \*/ TRIGGER trig\_useracct\_update  
AFTER UPDATE  
ON SF\_User.useracct FOR EACH ROW  
BEGIN  
IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN  
IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN  
if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN  
INSERT into FC\_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;  
end if;  
ELSE  
INSERT into FC\_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;  
END IF;  
END IF;  
END;  
  
上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列，在上述实例中，OLD.ulevelid表示表 SF\_User.useracct修改之前ulevelid列的值，NEW.ulevelid表示表SF\_User.useracct修改之后 ulevelid列的值。另外，如果是insert型触发器，NEW.ulevelid也表示表SF\_User.useracct新增行的 ulevelid列值；如果是delete型触发器OLD.ulevelid也表示表SF\_User.useracct删除行的ulevelid列原值。  
  
另外，OLD列是只读的，NEW列则可以在触发器程序中再次赋值。  
  
上述实例也使用了IF，THEN ，ELSE，END IF等关键字。在触发器程序体中，在beigin和end之间，可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。  
  
查看触发器。查看触发器语法如下，如果知道触发器所在数据库，以及触发器名称等具体信息：  
  
SHOW TRIGGERS from SF\_User like "usermaps%";       //查看SF\_User库上名称和usermaps%匹配的触发器  
  
如果不了解触发器的具体的信息，或者需要查看数据库上所有触发器，如下：  
  
SHOW TRIGGERS;       //查看所有触发器  
  
用上述方式查看触发器可以看到数据库的所有触发器，不过如果一个库上的触发器太多，由于会刷屏，可能没有办法查看所有触发器程序。这时，可以采用如下方式：  
  
Mysql中有一个information\_schema.TRIGGERS表，存储所有库中的所有触发器，desc information\_schema. TRIGGERS，可以看到表结构：  
  
\+----------------------------+--------------+------+-----+---------+-------+  
| Field                      | Type         | Null | Key | Default | Extra |  
\+----------------------------+--------------+------+-----+---------+-------+  
| TRIGGER\_CATALOG            | varchar(512) | YES |     | NULL    |       |   
| TRIGGER\_SCHEMA             | varchar(64) | NO   |     |         |       |   
| TRIGGER\_NAME               | varchar(64) | NO   |     |         |       |   
| EVENT\_MANIPULATION         | varchar(6)   | NO   |     |         |       |   
| EVENT\_OBJECT\_CATALOG       | varchar(512) | YES |     | NULL    |       |   
| EVENT\_OBJECT\_SCHEMA        | varchar(64) | NO   |     |         |       |   
| EVENT\_OBJECT\_TABLE         | varchar(64) | NO   |     |         |       |   
| ACTION\_ORDER               | bigint(4)    | NO   |     | 0       |       |   
| ACTION\_CONDITION           | longtext     | YES |     | NULL    |       |   
| ACTION\_STATEMENT           | longtext     | NO   |     |         |       |   
| ACTION\_ORIENTATION         | varchar(9)   | NO   |     |         |       |   
| ACTION\_TIMING              | varchar(6)   | NO   |     |         |       |   
| ACTION\_REFERENCE\_OLD\_TABLE | varchar(64) | YES |     | NULL    |       |   
| ACTION\_REFERENCE\_NEW\_TABLE | varchar(64) | YES |     | NULL    |       |   
| ACTION\_REFERENCE\_OLD\_ROW   | varchar(3)   | NO   |     |         |       |   
| ACTION\_REFERENCE\_NEW\_ROW   | varchar(3)   | NO   |     |         |       |   
| CREATED                    | datetime     | YES |     | NULL    |       |   
| SQL\_MODE                   | longtext     | NO   |     |         |       |   
| DEFINER                    | longtext     | NO   |     |         |       |   
\+----------------------------+--------------+------+-----+---------+-------+  
  
这样，用户就可以按照自己的需要，查看触发器，比如使用如下语句查看上述触发器：  
  
select \* from information\_schema. TRIGGERS where TRIGGER\_NAME= 'trig\_useracct\_update'\\G;  
  
删除触发器。删除触发器语法如下：  
  
DROP TRIGGER \[schema\_name.\]trigger\_name  
  
2.2   Msyql触发器的trigger\_time和trigger\_event  
  
现在，重新注意到trigger\_time和trigger\_event，上文说过， trigger\_time可以用before和after替换，表示触发器程序的执行在sql执行的前还是后；trigger\_event可以用 insert，update，delete替换，表示触发器程序在什么类型的sql下会被触发。  
  
在一个表上最多建立6个触发器，即1）before insert型，2）before update型，3）before delete型，4）after insert型，5）after update型，6）after delete型。  
  
触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”（摘自mysql使用手册）。  
  
另外还有一点需要注意，msyql除了对insert，update，delete基本操作进行定义外，还定义了load data和replace语句，而load data和replace语句也能引起上述6中类型的触发器的触发。  
  
Load data语句用于将一个文件装入到一个数据表中，相当与一系列insert操作。replace语句一般来说和insert语句很像，只是在表中有 primary key和unique索引时，如果插入的数据和原来primary key和unique索引一致时，会先删除原来的数据，然后增加一条新数据；也就是说，一条replace sql有时候等价于一条insert sql，有时候等价于一条delete sql加上一条insert sql。即是：  
?   Insert型触发器：可能通过insert语句，load data语句，replace语句触发；  
?   Update型触发器：可能通过update语句触发；  
?   Delete型触发器：可能通过delete语句，replace语句触发；  
  
3   Mysql触发器的执行顺序  
  
先抛出触发器相关的几个问题  
  
3.1   如果before类型的触发器程序执行失败，sql会执行成功吗？  
  
实验如下：  
  
1）在FC\_Word.planinfo中建立before触发器：  
  
DELIMITER |  
create trigger trigger\_before\_planinfo\_update  
before update  
ON FC\_Word.planinfo FOR EACH ROW  
BEGIN  
insert into FC\_Output.abc (planid) values (New.planid);  
END  
|  
  
2）查看：mysql> select showprob from planinfo where planid=1;  
  
\+----------+  
| showprob |  
\+----------+  
|        2 |   
\+----------+  
  
3）执行sql：  
  
update planinfo set showprob=200 where planid=1;      触发触发器程序；  
  
4）由于不存在FC\_Output.abc，before触发器执行失败，提示：  
  
ERROR 1146 (42S02): Table 'FC\_Output.abc' doesn't exist  
  
5）再次查看：  
  
mysql> select showprob from planinfo where planid=1;  
\+----------+  
| showprob |  
\+----------+  
|        2 |   
\+----------+  
  
即修改sql未执行成功。即如果before触发器执行失败，sql也会执行失败。  
  
3.2   如果sql执行失败，会执行after类型的触发器程序吗？  
  
实验如下：  
  
1）在FC\_Word.planinfo中建立after触发器：  
  
DELIMITER |  
create trigger trigger\_after\_planinfo\_update  
after update  
ON FC\_Word.planinfo FOR EACH ROW  
BEGIN  
INSERT INTO FC\_Output.fcevent set level = 2, type = 2, tabid = 5, userid = NEW.userid, planid = NEW.planid, planstat2 = NEW.planstat2, showprob = NEW.showprob, showrate = NEW.showrate, showfactor = NEW.showfactor, planmode = NEW.planmode;  
END  
|  
  
2）查看触发表：  
  
mysql> select \* from FC\_Output.fcevent where planid=1;  
Empty set (0.00 sec)  
  
没有planid=1的记录  
  
3）执行sql：  
  
mysql> update planinfo set showprob1=200 where planid=1;  
  
4）由于不存在showprob1列，提示错误：  
  
ERROR 1054 (42S22): Unknown column 'showprob1' in 'field list'  
  
5）再次查看触发表：  
  
mysql> select \* from FC\_Output.fcevent where planid=1;  
Empty set (0.00 sec)  
  
触发表中没有planid=1的记录，sql在执行失败时，after型触发器不会执行。  
  
3.3   如果after类型的触发器程序执行失败，sql会回滚吗？  
  
实验如下：  
  
1）在FC\_Word.planinfo中建立after触发器：  
  
DELIMITER |  
create trigger trigger\_after\_planinfo\_update  
after update  
ON FC\_Word.planinfo FOR EACH ROW  
BEGIN  
insert into FC\_Output.abc (planid) values (New.planid);  
END  
|  
  
2）查看：mysql> select showprob from planinfo where planid=1;  
  
\+----------+  
| showprob |  
\+----------+  
|        2 |   
\+----------+  
  
3）执行sql：  
  
update planinfo set showprob=200 where planid=1;触发触发器程序；  
  
4）由于不存在FC\_Output.abc，after触发器执行失败，提示：  
  
ERROR 1146 (42S02): Table 'FC\_Output.abc' doesn't exist  
  
5）再次查看：  
  
mysql> select showprob from planinfo where planid=1;  
\+----------+  
| showprob |  
\+----------+  
|        2 |   
\+----------+  
  
即修改sql未执行成功。即如果after触发器执行失败，sql会回滚。  
  
这里需要说明一下，上述实验所使用的mysql引擎是innodb，innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎，在 innodb上所建立的表是事务性表，也就是事务安全的。“对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚”（摘自mysql使用手册）。因而，即使语句失败，失败之前所作的任何更改依然有效，也就是说，对于 innodb引擎上的数据表，如果触发器中的sql或引发触发器的sql执行失效，则事务回滚，所有操作会失效。  
  
3.4   mysql触发器程序执行的顺序  
  
当一个表既有before类型的触发器，又有after类型的触发器时；当一条sql语句涉及多个表的update时，sql、触发器的执行顺序经过mysql源码包装过，有时比较复杂。  
  
可以先看一段mysql的源代码，当SQL中update多表的时候，Mysql的执行过程如下（省去了无关代码）：  
  
/\* 遍历要更新的所有表 \*/  
for (cur\_table= update\_tables; cur\_table; cur\_table= cur\_table->next\_local)  
\{   
org\_updated = updated  
/\* 如果有 BEFORE 触发器，则执行；如果执行失败，跳到err2位置 \*/  
if (table->triggers &&   
table->triggers->process\_triggers(thd, TRG\_EVENT\_UPDATE,TRG\_ACTION\_BEFORE, TRUE))  
goto err2;  
/\*执行更新，如果更新失败，跳到err位置\*/  
if(local\_error=table->file->update\_row(table->record\[1\], table->record\[0\])))  
goto err;  
updated++; // 更新计数器  
/\* 如果有 AFTER 触发器，则执行；如果执行失败，跳到err2位置\*/  
if (table->triggers &&  
table->triggers->process\_triggers(thd, TRG\_EVENT\_UPDATE, TRG\_ACTION\_AFTER, TRUE))  
goto err2;  
err:  
\{   
/\*标志错误信息，写日志等\*/  
\}  
err2:  
\{   
/\*恢复执行过的操作\*/  
check\_opt\_it.rewind();  
/\*如果执行了更新，且表是有事务的，做标志\*/  
if (updated != org\_updated)  
\{   
if (table->file->has\_transactions())  
transactional\_tables= 1;  
\}  
\}  
\}  
  
从上面代码可以找到本章开始时抛出问题的答案。  
  
1）   如果before型触发器执行失败，直接goto跳到err2位置，不会执行后续sql语句；  
  
2）   如果sql执行失败，直接goto跳到err位置，不会执行或许的after型触发器；  
  
3）   如过after触发器执行失败，goto到err2位置，恢复执行过的操作，且在事务型的表上做标记。  
  
另外，在使用复杂的sql时，由于有些复杂的sql是mysql自己定义的，所以存在不确定性，使用简单的sql比较可控。  
  
4   Mysql触发器在数据库同步中的表现  
  
4.1   触发器运行失败时，数据库同步会失败吗？  
  
有同步关系如下dbA?dbB。初始时同步正常。  
  
1）在dbB上建立触发器：  
  
DELIMITER |  
create trigger trigger\_after\_planinfo\_update  
after update  
ON FC\_Word.planinfo FOR EACH ROW  
BEGIN  
insert into FC\_Output.abc (planid) values (New.planid);  
END  
|  
  
2）在dbA上执行sql，执行成功；  
  
mysql> update planinfo set showprob=200 where planid= 1;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1 Changed: 1 Warnings: 0  
  
3）由于dbB上没有FC\_Output.abc表，触发器会执行失败，这时，检查一下同步状态：  
  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: NO  
Last\_Errno: 1146  
Last\_Error: Error 'Table 'FC\_Output.abc' doesn't exist' on query. Default database: 'FC\_Word'. Query: 'update planinfo set showprob=200 where planid= 1'  
  
可以看到IO线程运行正常，sql线程运行失败，并提示触发器运行失败的错误信息。  
  
回忆一下3.1和3.3所述部分，无论是before部分的触发器还是after类型的触发器，对于innodb引擎，当触发器执行失败时，相应sql也会执行失败，所以数据库同步也会失败。  
  
4.2   创建、删除触发器写bin-log  
  
创建和删除触发器的语句也会写入bin-log里，所以也会如一般的insert，update，delete语句一样同步到下游数据库中，即上游创建触发器，下游也会创建。  
  
这里再引出两个小问题：有同步关系dbA?dbB，  
  
1）   在dbA上创建一个触发器，如果dbB上已经有同表同类型的触发器，同步状态如何？  
  
2）   在dbB上删除一个触发器，如果dbB上没有对应触发器，同步状态如何？  
  
这两个问题可以类比同步中的insert语句和delete语句，答案就是  
  
1）   同步失败，因为不允许重复创建同表同类型的触发器；  
  
2）   同步正常，因为drop一个不存在的触发器，不影响运行结果；  
  
5   Mysql触发器经典案例  
  
5.1   案例1 一条sql涉及多个表的update时，触发得到update之前的旧值  
  
【现象】表test\_info上建有触发器如下：  
  
CREATE /\*!50017 DEFINER = 'root'@'localhost' \*/ TRIGGER trig\_test\_info\_update  
AFTER UPDATE  
ON FC\_Word.test\_info FOR EACH ROW  
BEGIN  
DECLARE tlevel INTEGER DEFAULT 0;  
DECLARE ttype INTEGER DEFAULT 0;  
SET tlevel = 4;  
SET ttype = 33;  
INSERT INTO TEST\_Output.fcevent (te, le, uid, pid, uid, wid, bi, mbid, wl) SELECT ttype, tlevel, NEW.uid, NEW.pid, NEW.uid, NEW.wid, NEW.bi, NEW.mbid, wl FROM TEST\_Word.wext2 where wid = NEW.wid;  
/\*。。。其余部分逻辑省略\*/  
END IF;  
END;  
  
这个触发器程序有点长，可以单看飘黄的两句，即更新操作满足第一个条件执行飘黄语句时，触发器的行为。触发器是建立在test\_info表上的，飘黄语句中可以看到，也需要查询wext2表。  
  
执行如下sql1：   
  
Update test\_info a, wext2 b set a.th=(a.th+1), a.w4=(a.w4&8), b.wl=NULL where a.wid=b.wid and a.wid=142394379;  
  
可以看到sql中既修改了test\_info2表，同时修改了wext2表，程序原意是触发得到wext2表wl字段修改后的新值（即NULL）；不过实验得到，执行上述sql后，触发器程序查询到的wurl是sql修改之前的旧值。  
  
再执行下面类似sql2：  
  
Update wext2 a, test\_info2 b set b.th=(b.th+1), b.w4=(b.w4&8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;  
  
实验的到，执行上述sql后，触发器程序查询到的wurl是sql修改之后的新值。  
  
【原因】原因当然与sql中的别名a，b无关，而是和wext2表和test\_info表的书写顺序有关。如本文3.4部分所述，一条sql涉及多个表的 update操作时，数据表字段、触发器执行顺序是mysql源码包装过的。在执行上述sql1时，先执行test\_info的更新，然后是after触发器，最后是wext2的更新，也就是说，在执行after触发器时，wext2还没有进行更新，所以触发得到的是旧值。而执行sql2时，先执行 wext2更新，然后是test\_info更新，最后是after触发器，也就是说，在执行after触发器时，wext2已经更新完毕，所以出去得到的是新值。  
  
引起上述现象是顺序关系的，无论该表是否支持事务。在使用复杂的sql时，由于有些复杂的sql是mysql自己定义的，所以存在不确定性，存在风险，使用简单的sql比较可控。  
  
5.2   案例2 mysql5.0.19版本修改表结构后触发器失效  
  
【现象】userpref表上建有after类型触发器，修改userpref表的外键关联后，在userpref表中的新增记录没有触发下来，即触发器失效。  
  
【原因】mysql5.0.19修改表结构是，触发器消失。这是mysql5.0.19的一个bug，在创建触发器时，会把触发器的内容保存在 information\_schema.TRIGGERS表中，同时在var目录下创建触发器的数据库目录下创建一个触发器名称为前缀，以TRN为后缀的文件，当修改触发器的表时，information\_schema.TRIGGERS表的内容会删除，导致触发器消失。  
  
在mysql5.0.45版本中，这个bug已经被修复。Mysql5.0.45版本的触发器，无论是修改表的索引、外键，还是改变表字段，触发器都不会失效。  
  
5.3   案例3 删除数据表后触发器失效  
  
【现象】联调环境中存在dbA?dbB，主库dbA上没有触发器，在从库dbB上的FC\_Word.wnegative表，FC\_Word.wbuget 表上建有触发器；触发器开始运行正常，期间没有对从库的任何直接操作，有一日发现对wnegative表上的修改无法触发。查看从库状态，同步正常；用 select TRIGGER\_NAME from information\_schema.TRIGGERS发现wnegative表上的触发器消失了；在var/FC\_Word目录下也没有 wnegative的.TRN文件，wnegative表上的触发器不见了。  
  
【分析】查找dbB的查询日志，发现有一条：  
  
100223 18:27:45 135939 Query       DROP TABLE IF EXISTS \`wnegative\`  
135939 Query       CREATE TABLE \`wnegative\` (  
KEY \`Index\_wnegative\_planid\` (\`planid\`),  
KEY \`Index\_wnegative\_unitid\` (\`unitid\`)  
135939 Query       /\*!40000 ALTER TABLE \`wnegative\` DISABLE KEYS \*/  
100223 18:27:46 135939 Query       INSERT INTO \`wnegative\` VALUES (614,1,289026,2911155,1848481);  
  
可以看到，在100223 18:27:45时，删除了表wnegative，紧接着有创建表wnegative；查找触发表发现，在100223 18:27:45时间后对wnegative的修改就没有触发了，而在这个之前对wnegative的修改是触发正常的。故，怀疑对wnegative表的删除使wnegative表上的触发器也被删除。对wnegative表的删除是在主库dbA上操作后，被同步到dbB上。  
  
【原因】在删除wnegative表时，mysql同时删除了wegative表上的触发器。  
  
可以通过下面实验证明上述猜测：  
  
1）   首先在wnegative建立after insert型触发器；  
2）   增加一条wnegative中记录；  
3）   查看结果发现触发器正确触发；  
4）   删除wnegative表；  
5）   使用select TRIGGER\_NAME from information\_schema.TRIGGERS查看所有触发器，wnegative表上触发器已经不存在了；同时到var/FC\_Word目录下，对应触发器的.TRN文件也不存在了；  
6）   重新创建wnegative表，并增加一条wnegative中记录；没有了wnegative表上触发器，自然也不能触发任何结果。  
  
6   结束语  
  
Mysql中的触发器功能已经在凤巢系统的各个模块中有广泛应用，究其细节，还有很多值得注意的地方；本文建立在实验和案例的基础上，数据库基于线上系统使用的mysql5.0.45版本，分析了触发器相关的一些特殊情况下msyql的处理方式。