mysql触发器(1)—数据表之间的同步
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表是空表，没有数据。
开始在表Ａ中插入一条数据。
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');
结果，同步成功。
结论：不同字段的相同类型可以同步，后面不再详细测试。
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表同步删除。
