mysql - 同步两个mysql数据库表

Question

所以，我有两个 mysql 数据库表:“table_original”和“table_copy”

这是我当前的设置:

每天晚上，table_original 都会从另一台服务器更新信息。它要么删除旧的数据库行，要么添加一个具有唯一 post_id 的新行(即使删除行，也不会有两个相同的 post_id)。

然后，某些信息连同 post_id 被复制到 table_copy 中。因此，两者由 post_id 链接。

到目前为止，table_original 有大约 20,000 行，而 table_copy 有大约 40,000 行。

我刚刚注意到 table_copy 没有从第一个表中更新，而只是添加行而不是删除从第一个表中删除的任何内容。

为了同步它们，我最初的方法是检查 table_original 中的每一行与 table_copy 的对比，如果 post_id 存在，则什么都不做，如果不存在，则删除table_copy 中的行。

我担心的是，table_original 每晚都会更新:旧的 post_id 行将被删除和/或添加新行。不幸的是，我无法在完成之前知道正在做什么。意思是，我必须等到数据库更新才能查看删除和添加的内容。

然后，每次更新第一个表时，我必须对照第二个表检查每一行以更新它们。 table 只会变得更大，我担心这种方法可能不是最好的。

你们有什么建议我可以做什么？

谢谢!

Answer 1

要删除 table_copy 中不存在于 table_original 中的行:

DELETE t1.* FROM table_copy AS t1 
LEFT OUTER JOIN table_original AS t2 USING (post_id)
WHERE t2.post_id IS NULL;

我建议您创建一个包含几行的虚拟表，这样您就可以试验这种查询并在针对您的真实数据运行它之前增强您对它的信心!

参见 http://dev.mysql.com/doc/refman/5.7/en/delete.html有关多表 DELETE 语句的更多文档。

要让它们每晚自动同步，请使用触发器:

CREATE TRIGGER copy_on_ins AFTER INSERT ON table_original
FOR EACH ROW
  INSERT INTO table_copy SET post_id = NEW.post_id, other_columns = NEW.other_column;

CREATE TRIGGER copy_on_upd AFTER UPDATE ON table_original
FOR EACH ROW
  UPDATE table_copy SET other_column = NEW.other_column
  WHERE post_id = NEW.post_id;

CREATE TRIGGER copy_on_del AFTER DELETE ON table_original
FOR EACH ROW
  DELETE FROM table_copy WHERE post_id = OLD.post_id;

参见 http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

---

回复你的评论:

要知道哪些 post_id 被删除是很棘手的。由于它们不再在数据库中，您需要:

• 如果您发现仍在数据库中的 post_id 值之间存在差距，则推断它们曾经存在过。但这并不能保证，因为如果插入失败，可能永远不会使用 id 值。
• 检查某种日志。有些人使用触发器来附加到审计表。或者编写他们的应用程序以删除帖子以创建日志。
• 使用 mysqlbinlog 通读删除事件工具。这有点高级。