语句定界符与触发器

本文讨论语句定界符（delimiter）和触发器（trigger）的应用。

语句定界符（delimiter）

前面的示例中，执行的每条SQL语句后都有一个分号（;），它是语句的结束符，也是定界符，标识了SQL语句的结束；默认情况下，SQL语句会自动提交（auto commit）执行，即当SQL语句遇到分号时就会立即执行。

在一些情况下，如创建触发器、存储过程、事务等数据库元素时，可能需要包含多条语句，这些语句会作为整体执行；此时，可以通过delimiter语句重新定义定界符，新的定界符只要不是SQL语句中使用的符号即可，如使用$$或//等符号；创建对象代码中依然可以将分号作为语句的结束符，但只有在遇到新的定界符时才会提交执行；对象创建完成后应使用delimiter语句重新将定界符定义为分号（;）。接下来，在创建触发器时可以看到定界符的具体应用。

触发器（trigger）

触发器是在特定事件时自动执行操作的一种工作机制，可以完成一些数据自动化处理工作。创建触发器使用create trigger语句，基本语法如下：

MySQL

create trigger [if not exists] <触发器名称>
<执行时间> <执行事件>
on <表名> for each row
<执行语句>

其中：

[if not exists]为可选，不使用时，如果触发器已存在则会抛出错误。
<执行时间>指定在在执行事件之前或之后触发执行语句，分别使用before和after关键字指定。
<执行事件>指定在什么操作时触发执行语句，包括insert、update和delete三种类型，分别用于表数据对应的操作。
<表名>指定触发器产生的数据表。
<执行语句>可以是单语句也可以是多语句，多语句时应包含在begin和end关键字之间。

使用insert触发器时可以使用new临时表读取添加的数据，对于auto_increment字段，在before触发时为0，在after触发时为新的数值。下面的代码会在cdb_demo数据库中创建t200和t201表，其中，t200表为数据原始表，t201表用于保存t200表新添加数据的副本。

MySQL

use cdb_demo;

create table t200(
recid bigint not null auto_increment primary key,
f1 varchar(30) not null unique,
f2 varchar(30) not null,
f3 int
)engine=innodb default charset='utf8mb4';

create table t201(
recid bigint not null,
f1 varchar(30) not null,
f2 varchar(30) not null,
f3 int
)engine=innodb default charset='utf8mb4';

create index ind_t201_f1 on t201(f1);

代码中首先创建了t200表，然后创建的t201表结构并不完全一样，主要的区别在于t201表中的f1字段没有定义为主键，f2字段没有定义唯一约束，但添加了索引；这么做的原因是t200表中的主键和唯一键数据可能会删除后重新添加相同的数据，此时再添加到t201表就会产生主键或唯一约束错误。实际应用中，也可以在副本数据的表中添加一些附加数据字段，如操作用户、操作时间和操作的客户端IP等信息。

下面的代码会在t200表中添加insert触发器。

MySQL

use cdb_demo;

delimiter //

create trigger trig_t200_insert_a 
after insert on t200 for each row
begin
	insert into t201(recid,f1,f2,f3)
	values(new.recid,new.f1,new.f2,new.f3);
end;
//

delimiter ;

本代码创建了trig_t200_insert_a触发器，它会在t200表执行insert语句之后将新添加的数据同步保存到t201表。下面的代码会在t200表中添加一条记录。

MySQL

use cdb_demo;

insert into t200(f1,f2,f3)
values('user01','用户01',1);

执行语句后，可以查询t201表中是否同步了添加的数据，如下图所示。

查询结果

使用update触发器时可以使用new临时表读取或修改新的数据，使用old临时表读取修改前的数据。下面的代码会创建t202表，用于保存t200表数据更新前的旧数据和更新后的新数据。

MySQL

use cdb_demo;

create table t202(
recid bigint not null,
f1 varchar(30) not null,
f2 varchar(30) not null,
f3 int,
f_ts timestamp default current_timestamp 
)engine=innodb default charset='utf8mb4';

create index ind_t202_recid on t202(recid);

这里，在t202表中添加了f_ts字段，用于保存记录添加时的系统时间；通过recid和f_ts字段数据的顺序就可以完全跟踪t200表数据的修改情况。下面的代码创建了t200表中的update触发器。

MySQL

use cdb_demo;

delimiter //

create trigger trig_t200_update_b 
before update on t200 for each row
begin
	insert into t202(recid,f1,f2,f3)
	values(old.recid,old.f1,old.f2,old.f3);
end;

create trigger trig_t200_update_a 
after update on t200 for each row
begin
	insert into t202(recid,f1,f2,f3)
	values(new.recid,new.f1,new.f2,new.f3);
end;
//

delimiter ;

代码中创建了t200表update操作的两个触发器，在更新之前会将旧的数据保存到t202表中，在更新之后会将新的数据保存到t202表中。

下面的代码会更新t200表中f1字段数据为'user01'的记录。

MySQL

use cdb_demo;

update t200 set f2='张三' where f1='user01';

执行t200表的更新操作后，可以在t202表中查看更新前后的数据对比，如下图的所示。

查询结果

使用delete触发器时可以使用old临时表读取删除的数据，并将删除数据保存到指定的表中。下面的代码会创建t203表，用于保存t200表中删除的数据副本。

MySQL

use cdb_demo;

create table t203(
recid bigint not null,
f1 varchar(30) not null,
f2 varchar(30) not null,
f3 int,
f_ts timestamp default current_timestamp 
)engine=innodb default charset='utf8mb4';

create index ind_t203_recid on t203(recid);

这里创建的t203表结构与t202表相同，同样也添加了recid字段的索引。下面的代码会创建t200表的delete触发器。

MySQL

use cdb_demo;

delimiter //

create trigger trig_t200_delete_b 
before delete on t200 for each row
begin
	insert into t203(recid,f1,f2,f3)
	values(old.recid,old.f1,old.f2,old.f3);
end;
//

delimiter ;

本例，trig_t200_delete_b触发器会在t200表执行delete操作前将删除的数据保存到t203表中。

下面的代码将删除t200表中f1字段数据为'user01'的记录。

MySQL

use cdb_demo;

delete from t200 where f1='user01';

执行操作后可以查看t203表的记录，如下图所示。

查询结果

上述示例中，将insert、update、delete三种触发器的数据副本分别保存到不同的数据表中，实际应用中，如果将所有数据副本保存到一个表中，可以在表中添加操作类型字段，比如，可以将触发器类型作为操作标识数据。

删除触发器时可以使用drop trigger语句，格式如下：

MySQL

drop trigger <触发器名称>;

需要注意，如果insert、update或delete操作失败，则对应的after触发器并不会执行。如果before触发器执行失败，则不会执行主语句和after触发器。