0.前言

背景:一个业务需要数据库的两个表的某些字段同步。

需求:数据库两个表之间实现同步的变化。

本文:同一服务器,同一个数据库,从tableA表同步部分数据到tableB表。

同一个服务器,不同数据库之间同步tableA与tableB,点这里。

不同服务器,不同数据库之间同步tableA与tableB,点这里。

1.触发器的基本结构

首先,触发器叫TRIGGER。基本结构如下:

CREATE or REPLACE TRIGGER 名字 
时间(after|before) 
动作(INSERT|UPDATE|DELETE)
on 表A
for each row
begin

sql语句,表B;

end;
2.插入操作(insert)

首先创建数据表,默认两张表是同一个结构,下面还有不同结构的。

-- 创建表A(触发表)
DROP TABLE IF EXISTS `tableA`;
CREATE TABLE `tableA`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `code` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `center` tinyint(1) NULL DEFAULT NULL,
  `fax` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `phone` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `pen_only_one`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- 创建表B(同步数据的表)
DROP TABLE IF EXISTS `tableB`;
CREATE TABLE `tableB`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `code` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `center` tinyint(1) NULL DEFAULT NULL,
  `fax` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `phone` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `pen_only_one`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

创建insert类型的触发

drop trigger if exists insert_tableB;
-- 如果有这个触发器就先删除
create trigger insert_tableB 
-- 触发表名称 insert_tableB
after insert 
-- 触发条件,在insert操作之后 
on tableA 
-- 需要在哪个表触发
for each row
begin 
	insert into  tableB (`code`,`id`)
	values(
	new.`code`,
	new.`id`
	);
-- sql语句	
end;

首先A表是空表,没有数据。

开始在表A中插入一条数据。

insert into  tableA (`code`,`id`)
	values('12345','54321');

效果,已经同步了。

下面尝试一下,不同表,不同字段能否同步(类型要一致)

我们删除表B,重新创建

-- 创建表B(同步数据的表)
DROP TABLE IF EXISTS `tableB`;
CREATE TABLE `tableB`  (
  `id_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `code_code` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `center` tinyint(1) NULL DEFAULT NULL,
  `fax` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '',
  `phone` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id_id`) USING BTREE,
  UNIQUE INDEX `pen_only_one`(`code_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

然后修改触发器

drop trigger if exists insert_tableB;
-- 如果有这个触发器就先删除
create trigger insert_tableB 
-- 触发表名称 insert_tableB
after insert 
-- 触发条件,在insert操作之后 
on tableA 
-- 需要在哪个表触发
for each row
begin 
	insert into  tableB (`code_code`,`id_id`)
	values(
	new.`code`,
	new.`id`
	);
-- sql语句	
end;

重新在A表中插入数据

insert into  tableA (`code`,`id`)
	values('aaaaa','bbbbbb');

结果,同步成功。

结论:不同字段的相同类型可以同步,后面不再详细测试。

关于mysql中new和old的区别,请看这篇文章。

3.更新操作

我们修改触发器,当tableA表更新时,触发

drop trigger if exists update_tableB;
-- 如果有这个触发器就先删除
create trigger update_tableB 
-- 触发表名称 update_tableB 
after update 
-- 触发条件,在update操作之后 
on tableA 
-- 需要在哪个表触发
for each row
begin 
	update tableB 
	set 
	code_code = new.`code`,
	id_id =new.`id`
	WHERE code_code=old.`code`;
-- sql语句	
end;

然后在表A更新数据:

UPDATE tableA set code='43'
WHERE code='aaaaa'

tableB表效果同步更新。

4.删除操作

创建删除的触发器

drop trigger if exists delete_tableB;
-- 如果有这个触发器就先删除
create trigger delete_tableB 
-- 触发表名称 delete_tableB 
after DELETE 
-- 触发条件,在delete操作之后 
on tableA 
-- 需要在哪个表触发
for each row
begin 
	DELETE from tableB 
	WHERE code_code=old.`code`;
-- sql语句	
end;

在tableA表执行删除操作。

DELETE from tableA 
	WHERE `code`='43';

tableB表同步删除。

分类: mysql