导航：首页 > 数据库 >

MySQL中怎么实现多库数据同步

发表于：2024-11-16 作者：千家信息网编辑

千家信息网最后更新 2024年11月16日，本篇文章为大家展示了MySQL中怎么实现多库数据同步，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。一、建立测试环境CREATE table mother(id

千家信息网最后更新 2024年11月16日MySQL中怎么实现多库数据同步

本篇文章为大家展示了MySQL中怎么实现多库数据同步，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

一、建立测试环境
CREATE table mother(id int(6) primary key auto_increment,value text);
CREATE table son LIKE mother;
建立了一个mother表和一模一样的son表，我们将要在mother表上建触发器，对mother表进行增、删、改都应该能同步到son表中
二、触发器原型
MySQL变动前的数据集和和变动后的数据集分别叫OLD和NEW，INSERT没有OLD只有NEW，DELETE只有OLD没有NEW，UPDATE两个都有。
同步插入触发器
DELIMITER |
CREATE TRIGGER tg_sync_insert
AFTER INSERT
ON mother
FOR EACH ROW
BEGIN
INSERT INTO son SELECT * FROM mother WHERE id > LAST_INSERT_ID();
END;
|
DELIMITER ;
LAST_INSERT_ID()获取到最后插入表后的id值，所以mother表必须要有一个auto_increment的字段，以为存在同时插入多个记录的情况，需要用大于LAST_INSERT_ID()
同步删除触发器
DELIMITER |
CREATE TRIGGER tg_sync_delete
AFTER DELETE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
END;
|
DELIMITER ;
同步更新触发器
DELIMITER |
CREATE TRIGGER tg_sync_update
AFTER UPDATE
ON mother
FOR EACH ROW
BEGIN
DELETE FROM son WHERE son.id=OLD.id;
INSERT INTO son SELECT * FROM mother WHERE id = OLD.id;
END;
|
DELIMITER ;
三、测试
插入
mysql> insert into mother (value) values ('first');
Query OK, 1 row affected (0.00 sec)
mysql> select * from son;
+--+---+
| id | value |
+--+---+
| 6 | first |
+--+---+
1 row in set (0.00 sec)
mysql> insert into mother (value) values ('second'),('third');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from mother;
+--+---+
| id | value |
+--+---+
| 7 | second |
| 6 | first |
| 8 | third |
+--+---+
3 rows in set (0.00 sec)
mysql> select * from son;
+--+---+
| id | value |
+--+---+
| 7 | second |
| 6 | first |
| 8 | third |
+--+---+
3 rows in set (0.00 sec)
mysql>
更新
mysql> update mother set value='updated' where id='8′;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from mother;
+--+---+
| id | value |
+--+---+
| 7 | second |
| 6 | first |
| 8 | updated |
+--+---+
3 rows in set (0.00 sec)
mysql> select * from son;
+--+---+
| id | value |
+--+---+
| 7 | second |
| 6 | first |
| 8 | updated |
+--+---+
3 rows in set (0.00 sec)
mysql>
删除
mysql> delete from mother where id='8′;
Query OK, 1 row affected (0.00 sec)
mysql> select * from son;
+--+---+
| id | value |
+--+---+
| 7 | second |
| 6 | first |
+--+---+
2 rows in set (0.00 sec)
mysql> delete from mother where id in (7,6);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from son;
Empty set (0.01 sec)
mysql>