同一mysql程序，不同数据库，表同步

场景描述

当同一个mysql程序中，有两个数据库test_main（主数据库）和test_follow（从数据库），都存在user表，需要对user表进行同步，打开test_main数据库，在里面创建如下触发器：

1、当两张user表字段一样且有id字段时

a、插入同步

CREATE TRIGGER `user_syn_insert` AFTER INSERT ON `user` FOR EACH ROW BEGIN
    INSERT INTO test_follow.user SELECT * FROM `user` WHERE `user`.id=new.id;
END

b、更新同步

CREATE TRIGGER `user_syn_update` AFTER UPDATE ON `user` FOR EACH ROW BEGIN
    DELETE FROM test_follow.user where `id`=old.id;
    INSERT INTO test_follow.user SELECT * FROM `user` WHERE `user`.id=new.id;
END

c、删除同步

CREATE TRIGGER `user_syn_delete` BEFORE DELETE ON `user` FOR EACH ROW BEGIN
    DELETE FROM test_follow.student where `id`=old.id;
END

2、当两张user表字段不一样且有id字段时，或只同步指定字段时

a、插入同步

CREATE TRIGGER `user_syn_insert` AFTER INSERT ON `user` FOR EACH ROW BEGIN
    INSERT INTO test_follow.user(`id`,`name`,`age`) VALUES(new.id,new.name,new.age);
END

b、更新同步

CREATE TRIGGER `user_syn_update` AFTER UPDATE ON `user` FOR EACH ROW BEGIN
    UPDATE test_follow.user SET `name`=new.name,`age`=new.age WHERE`id`=new.id;
END

c、删除同步

CREATE TRIGGER `user_syn_delete` BEFORE DELETE ON `user` FOR EACH ROW BEGIN
    DELETE FROM test_follow.student where `id`=old.id;
END

3、对于只有两个字段的简单中间表，是没有id字段的，这时建议统一使用相同的字段名，比如a_id,b_id。（对于有其他字段的中间表，建议给表增设id字段）

a、插入同步

CREATE TRIGGER `ux_syn_insert` AFTER INSERT ON `ux` FOR EACH ROW BEGIN
    INSERT INTO test_follow.ux (a_id,b_id) VALUES (new.a_id,new.b_id);
END

b、更新同步

CREATE TRIGGER `ux_syn_update` AFTER UPDATE ON `ux` FOR EACH ROW BEGIN
    UPDATE test_follow.ux set a_id=new.a_id,b_id=new.b_id WHERE a_id=old.a_id and b_id=old.b_id;
END

c、删除同步

CREATE TRIGGER `ux_syn_delete` AFTER DELETE ON `ux` FOR EACH ROW BEGIN
    DELETE FROM test_follow.ux where a_id=old.a_id and b_id=old.b_id;
END

4、最后，关于删除触发器的sql语句

DROP TRIGGER `user_syn_delete`;