mysql触发器-表数据实时同步

灰太狼 2022-05-14 04:53 250阅读 0赞

今天遇到一个需求，是同一个实例里面的两个库中的几张表需要做单向同步，刚开始想直接使用Otter&Canal的架构去做同步。但是因为这个架构依赖的是binlog进行同步的，但是阿里云RDS对于binlog是有做定时清理操作的。所以如果用canal去通过binlog同步的话 一旦RDS的binlog 清理了 将无法同步，并且无法找回之前的数据。所以考虑到只是同步极少数的表，就用了mysql的触发器。

mysql的触发器只支持行级触发器不支持语句级触发器，行级触发器又分为单行触发器以及多行触发器。

单行触发器类似于：

create trigger tri\_name after insert|delete|update on table\_name for each row trigger\_sql

多行触发器类似于：  
DELIMITER $  
create trigger cif\_ofs\_supplier\_attribute\_Insert after insert  
on cif.ofs\_supplier\_attribute for each row  
begin  
INSERT INTO ofs.ofs\_supplier\_attribute(id,supplier\_id,common\_flag,is\_deleted,gmt\_created,gmt\_modified) VALUES(new.id,new.supplier\_id,new.common\_flag,new.is\_deleted,new.gmt\_created,new.gmt\_modified)  
END$;  
DELIMITER ;

其中DELIMITER 定好结束符为”$$”, 然后最后又定义为”;”, MYSQL的默认结束符为”;”.   after 代表是在触发之后操作，before代表在触发之前操作

触发器语句中使用了两种特殊的表：old 表和 new 表

old 表用于存储 DELETE 和 UPDATE 语句所影响的行的副本，

在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。old 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 new 表和触发器表中。new 表中的行是触发器表中新行的副本。

1.插入操作（Insert）

new表有数据，old表无数据

2.删除操作（Delete）

new表无数据，old表有数据

3.更新操作（Update）

new表有数据（[新数据][Link 1]），old表有数据（旧数据）

表同步多行触发器例子：

### inserted (插入): ###

DELIMITER $  
create trigger cif\_ofs\_rel\_supplier\_role\_Insert after insert  
on cif.ofs\_rel\_supplier\_role for each row  
begin  
INSERT INTO ofs.ofs\_rel\_supplier\_role(id,supplier\_id,role\_code,is\_deleted,gmt\_created,gmt\_modified) VALUES(new.id,new.supplier\_id,new.role\_code,new.is\_deleted,new.gmt\_created,new.gmt\_modified);  
END$;  
DELIMITER ;

### update(更新) ###

DELIMITER $  
create trigger cif\_ofs\_rel\_supplier\_role\_update after update  
on cif.ofs\_rel\_supplier\_role for each row  
begin  
update ofs.ofs\_rel\_supplier\_role set id=new.id,supplier\_id=new.supplier\_id,role\_code=new.role\_code,is\_deleted=new.is\_deleted,gmt\_created=new.gmt\_created,gmt\_modified=new.gmt\_modified where id=old.id;  
END$;  
DELIMITER ;

### delete(删除) ###

DELIMITER $  
create trigger cif\_ofs\_rel\_supplier\_role\_delete after delete  
on cif.ofs\_rel\_supplier\_role for each row  
begin  
delete from ofs.ofs\_rel\_supplier\_role where id=old.id;  
END$;  
DELIMITER ;

## ##

### 查看触发器： ###

查看触发器方式有多种：

1.show triggers;

2.SELECT \* FROM information\_schema.\`TRIGGERS\`;

3.使用Navicat查看，选中对应的表 右键设计表结构 查看

### 删除触发器： ###

drop trigger trigger\_name

[Link 1]: https://www.baidu.com/s?wd=%E6%96%B0%E6%95%B0%E6%8D%AE&from=1012015a&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y4nyFWPjFWPj9BPHRsmhFB0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPHfsP10LPWm3