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 ;