mysql两表之间做同步_MySQL触发器实现两表数据同步（详解）

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

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 ;

每天进步一点，更多精彩内容，欢迎关注公众号“编程社”！