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表是空表,没有数据。

开始在表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表同步删除。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值