mysql 利用触发器实现表数据同步

数据库 db1 和 数据库 db2 处于同一个数据库连接下，分别拥有表 table1 和 table2，两个表结构一致

现将 db1.table1 下的改动更新到 db2.table2，以下触发器是在 db1 下创建的

1. 插入同步

   drop trigger if exists after_table1_insert
   delimiter $$
   create trigger after_table1_insert
   after insert
   on table1
   for each row
   begin
   insert into db2.table2 select * from table1 where id = new.id;
   end$$
   delimiter ;
   

2. 更新同步

   drop trigger if exists after_table1_update
   delimiter $$
   create trigger after_table1_update
   after update
   on table1
   for each row
   begin
   delete from db2.table2 where id = new.id;
   insert into db2.table2 select * from table1 where id = new.id;
   end$$
   delimiter ;
   

3. 删除同步

   drop trigger if exists after_table1_delete
   delimiter $$
   create trigger after_table1_delete
   after delete
   on table1
   for each row
   begin
   delete from db2.table2 where id = old.id;
   end$$
   delimiter ;
   

扩展链接

触发器创建语法： https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html

触发器创建示例： https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html