MySQL触发器实现两表数据同步（详解）

Love The Way You Lie 2022-04-04 17:42 560阅读 0赞

本文主要实现两个表数据同步，当第一个表数据变化的时候，自动同步数据到第二张表。

##### 1. 创建一个数据表 order\_1，同时复制出一份表order\_2 #####

> 表结构如下：  
> CREATE TABLE `a_order_1` (  
> `order_id` int(11) NOT NULL AUTO\_INCREMENT,  
> `order_sn` varchar(100) DEFAULT NULL,  
> `user_nick` varchar(100) DEFAULT NULL,  
> `user_mobile` varchar(100) DEFAULT NULL,  
> `address` varchar(255) DEFAULT NULL,  
> `status` tinyint(1) DEFAULT NULL,  
> `payment` decimal(10,2) DEFAULT NULL,  
> PRIMARY KEY (`order_id`)  
> ) ENGINE=MyISAM AUTO\_INCREMENT=3 DEFAULT CHARSET=utf8;

##### 2. 创建插入触发器 #####

> DELIMITER $  
> create trigger a\_order\_1\_trigger after insert  
> on a\_order\_1 for each row  
> begin  
> INSERT INTO a\_order\_2(order\_id,order\_sn,user\_nick,user\_mobile,address,status,payment) VALUES(new.order\_id,new.order\_sn,new.user\_nick,new.user\_mobile,new.address,new.status,new.payment);  
> END $;  
> DELIMITER ;

##### 3. 创建更新触发器 #####

> DELIMITER $  
> create trigger a\_order\_1\_trigger\_u after update  
> on a\_order\_1 for each row  
> begin  
> update a\_order\_2 set order\_id=new.order\_id,order\_sn=new.order\_sn,user\_nick=new.user\_nick,user\_mobile=new.user\_mobile,address=new.address,status=new.status,payment=new.payment where order\_id=old.order\_id;  
> END$;  
> DELIMITER ;

##### 4 创建删除触发器 #####

> DELIMITER $  
> create trigger a\_order\_1\_trigger\_d after delete  
> on a\_order\_1 for each row  
> begin  
> delete from a\_order\_2 where order\_id=old.order\_id;  
> END$;  
> DELIMITER ;

**每天进步一点，更多精彩内容，欢迎关注公众号“编程社”！**  
![在这里插入图片描述][aHR0cDovL3d3dy5taTEyLndhbmcvd2VsY29tZS8xLmpwZw]

[aHR0cDovL3d3dy5taTEyLndhbmcvd2VsY29tZS8xLmpwZw]: /images/20220404/a3e3c97b3bed432b9d51a3cb27309e8b.png