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

梦里梦外; 2024-03-30 12:46 34阅读 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 ;

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

#创建数据库
    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;