mysql触发器(2)—同一服务器，不同数据库的表同步

原文链接：这里

0.前言

前面一篇中，我们简单介绍了触发的使用，这篇文章，我们介绍下，同一个服务器下，不同数据库之间的表字段同步。

1.背景介绍

同一个服务器下，有两个数据库，dataBaseA和dataBaseB，dataBaseA库有tableA表，dataBaseB库有tableB表。现在要求当A表的数据有变化时，B表同时变化（假定两张表的表结构完全一致）

2.基本原理

假设dataBaseA中tableA表的结构和dataBaseB库的tableB表一致，都是下面这样子：

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 = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

注意一点，这里的ENGINE已经变成MyISAM，不再是InnoDB。

那么我们可以参考下面模板创建触发器：

delimiter ||

DROP TRIGGER

IF EXISTS 触发器名称 ||

-- 存在就先删除

CREATE TRIGGER 触发器名称

时间(AFTER|BEFORE)

操作(INSERT|UPDATE|DELETE)

ON 库名A.表名A

FOR EACH ROW

BEGIN

INSERT 库名B.表名B

(`id`,`code`)

VALUES

(new.id,new.code);

END||

delimiter ;

如果是互为触发器可以参考下面这个：

delimiter ||

DROP TRIGGER

IF EXISTS 触发器名称 ||

-- 存在就先删除

CREATE TRIGGER 触发器名称

时间(AFTER|BEFORE)

操作(INSERT|UPDATE|DELETE)

ON 库名A.表名A

FOR EACH ROW

BEGIN

IF @disable_trigger IS NULL THEN

SET @disable_trigger = 1;

INSERT 库名B.表名B

(`id`,`code`)

VALUES

(new.id,new.code);

END IF;

END||

delimiter ;

3.插入操作insert

举例：

delimiter ||

DROP TRIGGER

IF EXISTS t_insert ||

CREATE TRIGGER t_insert

AFTER INSERT

ON dataBaseA.tableA

FOR EACH ROW

BEGIN

IF @disable_trigger IS NULL THEN

SET @disable_trigger = 1;

INSERT dataBaseB.tableB

(`id`,`code`)

VALUES

(new.id,new.code);

SET @disable_trigger = NULL;

END IF;

END||

delimiter ;

4.更新操作update

参考：

delimiter ||

DROP TRIGGER

IF EXISTS t_update||

CREATE TRIGGER t_update

AFTER UPDATE

ON dateBaseA.tableA

FOR EACH ROW

BEGIN

IF @disable_trigger IS NULL THEN

SET @disable_trigger = 1;

UPDATE dataBaseB.tableB SET

`code`=new.`code`

WHERE

`id`= OLD.`id` ;

SET @disable_trigger = NULL;

END IF;

END||

delimiter ;

5.删除操作delete

 

delimiter ||

DROP TRIGGER

IF EXISTS t_update||

CREATE TRIGGER t_update

AFTER INSERT

ON dateBaseA.tableA

FOR EACH ROW

BEGIN

IF @disable_trigger IS NULL THEN

SET @disable_trigger = 1;

UPDATE dataBaseB.tableB SET

`code`=new.`code`

WHERE

`id`= OLD.`id` ;

SET @disable_trigger = NULL;

END IF;

END||

delimiter ;