mysql 跨服务器的触发器 mysql触发器跨库写入

**

前言

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

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

本文：同一服务器，同一个数据库，从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’);
 结果，同步成功。结论：不同字段的相同类型可以同步，后面不再详细测试。
关于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表同步删除。5.不同操作
 不同操作创建触发器drop trigger if exists insert_tableB;
 – 如果有这个触发器就先删除
 create trigger insert_tableB
 – 触发表名称 insert_tableB
 after update
 – 触发条件，在update操作之后
 on tableA
 – 需要在哪个表触发
 for each row
 begin
 insert into tableB (code_code,id_id)
 values(
 new.code,
 new.id
 );
 – sql语句
 end;

这样只要是tablA表修改记录成功，tableB表就会新添加一条记录，而不是修改记录了！关键在于触发条件！！！