Main_csdn_String Java攻城狮  2018年07月04日
请问如何更优雅的设计数据库同步问题?这个示例是说明使用触发器进行同步
DELIMITER $$ 
USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aai`; $$
CREATE TRIGGER `a`.`aai`
AFTER INSERT ON `a`.`aa`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `d`.`dd` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
INSERT INTO `b`.`bb` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
INSERT INTO `c`.`cc` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$

USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aau`; $$
CREATE TRIGGER `a`.`aau` AFTER UPDATE
ON `a`.`aa` FOR EACH ROW
BEGIN
IF (new.`servers` = 'b' AND new.type = 0) THEN
UPDATE `c`.`cc` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `d`.`dd` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
IF (new.`servers` = 'c' AND new.type = 0) THEN
UPDATE `b`.`bb` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `d`.`dd` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
IF (new.`servers` = 'd' AND new.type = 0) THEN
UPDATE `b`.`bb` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
UPDATE `c`.`cc` SET `uuid` = new.uuid, `type` = new.`type`,`servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$


DELIMITER $$
USE `a`; $$
DROP TRIGGER IF EXISTS `a`.`aad`; $$
CREATE TRIGGER `a`.`aad` AFTER DELETE
ON `a`.`aa` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'b' AND OLD.type = 0) THEN
DELETE FROM `c`.`cc` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
IF (OLD.`servers` = 'c' AND OLD.type = 0) THEN
DELETE FROM `b`.`bb` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
IF (OLD.`servers` = 'd' AND OLD.type = 0) THEN
DELETE FROM `c`.`cc` WHERE `uuid` = OLD.uuid;
DELETE FROM `d`.`dd` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbi`; $$
CREATE TRIGGER `b`.`bbi`
BEFORE INSERT ON `b`.`bb`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'b') THEN
INSERT INTO `a`.`aa` (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbd`; $$
CREATE TRIGGER `b`.`bbd` AFTER DELETE
ON `b`.`bb` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'b') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `b`; $$
DROP TRIGGER IF EXISTS `b`.`bbu`; $$
CREATE TRIGGER `b`.`bbu` AFTER UPDATE
ON `b`.`bb` FOR EACH ROW
BEGIN
IF (new.`servers` = 'b') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$

DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`cci`; $$
CREATE TRIGGER `c`.`cci`
BEFORE INSERT ON `c`.`cc`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'c') THEN
INSERT INTO (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$


DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`ccd`; $$
CREATE TRIGGER `c`.`ccd` AFTER DELETE
ON `c`.`cc` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'c') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `c`; $$
DROP TRIGGER IF EXISTS `c`.`ccu`; $$
CREATE TRIGGER `c`.`ccu` AFTER UPDATE
ON `c`.`cc` FOR EACH ROW
BEGIN
IF (new.`servers` = 'c') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$


DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddi`; $$
CREATE TRIGGER `d`.`ddi`
BEFORE INSERT ON `d`.`dd`
FOR EACH ROW
BEGIN
IF (new.`servers` = 'd')
THEN
INSERT INTO (`uuid`,`type`,`servers`,`name`)VALUES(new.uuid,new.type,new.`servers`,new.`name`);
END IF;
END; $$

DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddd`; $$
CREATE TRIGGER `d`.`ddd` AFTER DELETE
ON `d`.`dd` FOR EACH ROW
BEGIN
IF (OLD.`servers` = 'd') THEN
DELETE FROM `a`.`aa` WHERE `uuid` = OLD.uuid;
END IF;
END;
$$

DELIMITER $$
USE `d`; $$
DROP TRIGGER IF EXISTS `d`.`ddu`; $$
CREATE TRIGGER `d`.`ddu` AFTER UPDATE
ON `d`.`dd` FOR EACH ROW
BEGIN
IF (new.`servers` = 'd') THEN
UPDATE `a`.`aa` SET `uuid` = new.uuid, `servers` = new.servers,`name` = new.name WHERE `uuid` = new.uuid;
END IF;
END;
$$
...全文
167 点赞 收藏 2
写回复
2 条回复

还没有回复,快来抢沙发~

发动态
发帖子
MySQL
创建于2007-09-28

2.4w+

社区成员

5.5w+

社区内容

MySQL相关内容讨论专区
社区公告
暂无公告