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 ;
 

先创建两个测试的数据库以及表

#创建数据库
create database db_test6;
#使用数据库
use db_test6;
#创建学生表

CREATE TABLE `students` (
  `stu_num` char(4) NOT NULL,
  `stu_name` varchar(20) NOT NULL,
  `stu_gender` char(2) NOT NULL,
  `stu_age` int NOT NULL,
  PRIMARY KEY (`stu_num`)
)
#创建需要同步的数据库
create database db_test7;
#使用数据库
use db_test7;
#创建学生表
#创建所要同步的学生表（字段相同）
CREATE TABLE `students` (
  `stuNum` char(4) NOT NULL,
  `stuName` varchar(20) NOT NULL,
  `stuGender` char(2) NOT NULL,
  `stuAage` int NOT NULL,
  PRIMARY KEY (`stu_num`)
) 

然后创建触发器

#创建触发器trigger_test实现数据库db_test6中students表增加内容时数据库db_test7 中students表同步
create trigger trigger_test3 after insert on students for each row

	insert into db_test7.students(stuNum,stuName,stuGender,stuAge) 
	values(new.stu_num,new.stu_name,new.stu_gender,new.stu_age); 

#测试增加数据库db_test6中students内容
insert into students(stu_num,stu_name,stu_gender,stu_age)
	values("1002","张三","男",20); 

#创建触发器trigger_test实现数据库db_test6中students表删除内容时数据库db_test7 中students表同步
create trigger trigger_test4 after delete on students for each row
begin
	delete from db_test7.students where stuNum=old.stu_num ;
end	
	
	#测试删除数据库db_test6中students中的内容
	delete from students where stu_num="1002";
	
	#创建触发器trigger_test实现students修改内容时studentstest同步
	create trigger trigger_test5 after update on students for each row
begin
	update db_test7.students set stuName=new.stu_name,stuGender=new.stu_gender,stuAge=new.stu_age where stuNum=old.stu_num ;
end

	#测试更新数据库db_test6中students中的内容
	update students set stu_name="lisi",stu_gender="女",stu_age=21 where stu_num=1002;