Mysql触发器的简单使用

最近碰到个小需求，需要同步维护2个表信息一致。

想了下有大概2种实现思路：

• 使用Laravel里的事件模型来同步处理数据，但是改动的地方太多，有可能遗漏，好处就是可控，业务逻辑留在代码层

• 使用Mysql触发器，只需要给表建立 Insert, Update, Delete 的触发器就可以完成表的同步，但是这样会导致业务逻辑分布在代码层和Mysql里，以后出了问题不好定位,而且触发器会 很消耗资源，一般都是建议能不用就不用吧，对增删改非常频繁的表上最好还是不要使用触发器的好

最后想了下还是决定使用Mysql的触发器吧，毕竟我这次动的表，它的改动频率不是很高，以前也没接触过触发器，顺便学习下（这才是主要目的）

---

触发器是什么

根据Mysql官方文档 Trigger

触发器是一个和表所关联的数据库对象，当表触发了特定的事件，这些触发器将会被触发。触发器的一些简单用途示例：比如对数据插入前进行检验，或者对更新中的数据进行计算等等。

触发器被定义为，在SQL语句对每一行执行 insert, update, delete 时被激活，这些针对每一行的操作就是 触发器事件。例如，一行数据可以通过执行 insert 或者 load data 语句来插入，这时每一行的插入都会触发一个 insert触发器。另外触发器还可以被设置为在这些触发事件之前执行还是之后执行。

重点提示：Mysql触发器只作用于对关联的表进行 SQL语句 操作的时候才会被触发。当通过API来操作数据库表的时候，是不会发送转换的SQL语句到Mysql服务器的，也就不会触发触发器。比如使用 NDB API。

另外触发器必须要关联到表上，作用不到临时表和视图上。
触发器中也不能 显式的使用事务 相关操作

触发器的使用

创建触发器

官方地址 Create Trigger

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

看下必要参数

1. trigger_name: 触发器的名字
2. trigger_time: 触发执行的时机，有2个可选值 before 和 after
3. trigger_event: 触发事件，有3个可选值
   • insert: 当插入一行数据的时候触发，比如使用 insert, load data, replace 等一系列插入操作
   • update: 当更改某一行数据的时候触发
   • delete: 当删除某一行数据的时候触发
4. table_name: 触发器作用关联的表名，可以通过 数据库名.表名 这样来明确指定， 比如 Myinit.user
5. FOR EACH ROW: 表示作用关联表上的每一行都会触发触发器
6. trigger_order： 这个是Mysql5.7以后的一个功能，用于定义多个触发器的执行顺序，可选值有2个 follows (随后) 和 precedes(在…之前)
7. trigger_body： 这个就是触发器的主要内容了，这里是触发器具体要做的事情，比如增删改操作数据之类。

一个简单示例：

create trigger ins_sum before insert on account for each row set @sum = @sum + NEW.amount;

如果你有多条执行语句可以这样定义

# 定界符
delimiter $$

# 删除触发器
drop trigger if exists sync_master_insert$$
# 创建触发器
create trigger sync_master_insert after insert on master for each row

begin
# 插入插入master的数据插入到备份表
insert into `master_duplication`(`id`, `name`) 
select `id`, `name`, from `master` where id = new.id;
# ... 操作 ...
end; $$

delimiter ;

补充说明下 触发器Body中的 OLD 和 NEW 关键字 详情在这里 Trigger Syntax

OLD 和 NEW Mysql对触发器的扩展，这2个关键字不区分大小写。

在 insert 触发器中，只有 new 关键字可以被使用(比如你想要获取指定字段的值就用 new.col_name)，没有 old 的行数据。new 用来表示将要（before）或者已经（after）被插入的数据（PS：这个时候不论before还是after，使用old获取的数据都是一样的）

在 delete 触发器中，只有 old 关键字可以被使用（比如你想要获取指定字段的值就用 old.col_name），没有 new 的行数据。old 用来表示将要（before）或者已经（after）被删除的数据。（PS：这个时候不论before还是after，使用old获取的数据都是一样的）

在 update 触发器中，你可以使用 old.col_name 来作为该行数据更新之前的副本，使用 new.col_name 来作为该行数据更新之后的副本。

查看触发器

如果你想查看当前库下的所有触发器可以使用 show triggers; 来查看。

另外所有的触发器都是存储在 information_schema 数据库下的 triggers 表中，如果想要查看所有触发器的详细信息可以使用SQL

# 可以增加 trigger_name 作为过滤条件
select * from information_schema.triggers;

删除触发器

语法如下

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

示例：

drop trigger if exists sync_order;

需要注意一点，删除触发器需要有关联触发器的表的 Trigger 特权。

log_bin_trust_function_creators 问题

在创建触发器的时候碰到一个报错 Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

后面找了下问题原因，在Mysql启用二进制日志的情况下，log_bin_trust_function_creators 这个变量就会启用。

说明：以下函数可以是 存储过程，函数，触发器 等

它表示是否可以信用函数的创建者，主要是为了避免这些函数写入二进制日志引发不安全的操作。其默认值是0，也就是用户不允许创建或修改这些函数。除非用户具有 超级 特权。

所以上面的报错就很好理解了，因为我当前操作的用户不具有这些特权，为了避免不安全操作所以禁止我创建触发器。

解决办法

首先说下二进制日志，这个最重要的功能是实现主从复制，而上面这些函数有可能导致主从数据不一致情况，所有Mysql才做了这个限制。

Mysql服务器会把 create event, create procedure, create function, alter event, alter procedure, alter function, drop event, drop procedure, drop function 这些语句都写入二进制日志。而trigger和function类似，因此对于function的限制也同样作用于触发器。

解决方案1：切换root用户来操作，或者提升当前用户权限

解决方案2：如果数据库没有开启主从复制 或者 你的操作确实不会影响数据一致问题 或者 就算影响了数据也没什么影响，那么将该参数设置为1，也就是打开状态

set global log_bin_trust_function_creators = 1;

这个只是临时设置，重启后就会失效。如果想永久生效，可以更改 my.cnf 中增加 log_bin_trust_function_creators=1 这样可以永久生效。

如果你是创建的 create function ... 还可以通过指定该function的类型，来明确告诉Mysql你的function不会改变数据，这个时候也是可以创建function的，这种只适用于创建function，对于trigger不适应（反正没找到解决办法…）

添加参数：

DETERMINISTIC: 不确定是否会修改数据
NO SQL： 没有SQL 也就无从修改数据
READS SQL DATA： 只是读取数据

加了这3个其中之一的function将被允许创建和修改等操作

create function f1(i int)
returns int
# here
DETERMINISTIC
reads sql data
begin
  return i;
end;

Mysql二进制日志

创建完update触发器后，测试了下，可以正确执行，但是老是会报一个警告 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT... 看的很不爽。这主要是Mysql的二进制日志采用的是 STATEMENT 也就是基于语句执行的，它会对函数中有修改操作的进行警告，告诉你有可能导致主从数据不一致的情况出现。上面的报错后半部分是警告的原因，我给忘记了，大概是说我往一个自增主键的备份表中添加数据，有可能导致不一致，然后我就把备份表的主键设置不自增，警告消失了…

Mysql有3种二进制日志格式

STATEMENT: 基于语句的日志记录。

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能
• 缺点: 为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果

ROW: 基于每一行的日志记录 这是默认值。

• 优点：不记录上下文信息，清楚的记录下每一行数据修改的细节。
• 缺点：因为是基于行的，日志会非常大。比如执行了一个update修改了100W数据，就会有100W条记录

MIXED: 混合日志记录，这种会在根据场景在 STATEMENT 和 ROW之间自动切换。

• 优点：前2者优点的结合。自动切换
• 缺点：个人想法有2点。 1.自动切换的效率问题，比如在不该使用row格式的时候切换到了row格式记录。2.在切换到对应格式的时候，对应格式本身的缺点也就是上面的内容。当然比起单独使用row或者statement,还是能接受的

对于MIXED格式来说，在如下情况的时候，binlog会自动转为ROW格式记录

1.NDB引擎
2.SQL语句里包含了UUID()函数
3.自增长字段被更新了
4.包含了insert delayed语句
5.使用了用户定义函数（UDF）
6.使用了临时表
7.附带limit语法的

查看当前的binlog格式

可以使用语句

mysql> show global variables like 'binlog_format';

设置全局binlog格式

设置全局的binlog格式需要相应的权限，可以使用语句

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

设置当前客户端binlog格式

设置当前客户端的binlog格式可以基于当前的会话session来实现

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

触发器示例

直接贴代码吧

# 创建 insert 触发器
delimiter $$
# 删除触发器 如果存在的话
drop trigger if exists sync_master_insert$$
# 创建触发器
create trigger sync_master_insert after insert on master for each row
begin
# 比较懒的插入 - 查询插入
#insert into `master_duplication`(`id`, `name`, `age`, `school`, `score`, `birth_date`) 
#select `id`, `name`, `age`, `school`, `score`, `birth_date` from `master` where id = new.id;

# 常规插入
# 注意使用new关键字来获取数据
insert into `master_duplication`(`id`, `name`, `age`, `school`, `score`) 
values (new.id, new.name, new.age, new.school, new.score);
end; $$
delimiter ;

#=============================================

# 创建 delete 触发器
delimiter $$
# 删除触发器 如果存在的话
drop trigger if exists sync_master_delete$$
# 创建触发器
create trigger sync_master_delete after delete on master for each row
begin

# 注意使用old关键字来获取数据
delete from master_duplication where id = old.id;

end; $$
delimiter ;


#=============================================

# 创建 update 触发器
delimiter $$
# 删除触发器 如果存在的话
drop trigger if exists sync_master_update$$
# 创建触发器
create trigger sync_master_update after update on master for each row
begin

# 注意new和old关键字来获取数据
update master_duplication set
`id` = new.id, `name` = new.name, `age` = new.age, `school` = new.school, `score` = new.score
where id = old.id;

end; $$
delimiter ;

---