在存储在两个不同数据库中的两个表之间同步主键 [英] Syncing primary key between two tables stored in two different database

问题描述

我有两个包含两个表的MySQL数据库，我们称它们为TABLE_A和TABLE_B.这两个表都具有字段id和title.这两个数据库都在同一台服务器上，并且同一用户可以访问这两个数据库.

I have two MySQL database that contain two table, let's call them TABLE_A and TABLE_B. Both these tables have as fields id and title. Both databases are on the same server and the same user can access both.

现在，关于title字段，TABLE_A是TABLE_B的子集.这意味着TABLE_A中的每个title也都存在于TABLE_B中.虽然两个表的id字段完全没有关联.

Now, TABLE_A is a subset of TABLE_B for what concern the title field. It means that every title in TABLE_A is present in TABLE_B, too. While id fields of the two table are in no way related.

我需要根据标题，即同一个title和同一个id，将TABLE_A中的id字段与TABLE_B中的id字段同步.如果不清楚，我必须保存TABLE_B id并覆盖TABLE_A的内容.而且我不必将丢失的title从TABLE_B添加到TABLE_A.

What I need is to sync id fields in TABLE_A with id fields in TABLE_B, according to the title, i.e. same title, same id. If it's not clear, I have to save TABLE_B id and override TABLE_A ones. And I DON'T have to add missing title from TABLE_B to TABLE_A.

有人建议使用时态表，在该表中复制与TABLE_A相同的所有TABLE_B字段，然后将其重命名为TABLE_A.我不能遵循这种方式，因为TABLE_A实际上还需要维护其他字段.因此，我不能完全删除旧的TABLE_A.

Someone suggested to use a temporal table where to copy all TABLE_B fields in common with TABLE_A and then rename it as TABLE_A. I cannot follow this way, as TABLE_A actually has also other fields that I need to maintain. So, I cannot entirely drop the old TABLE_A.

此外，id是两个表的主键.这意味着我不能简单地从TABLE_B复制到TABLE_A，因为一旦我尝试将一个id更改为TABLE_A中已经存在但链接到另一个title的另一个id，查询就会失败.

Moreover, id is the primary key for both tables. It means that I cannot simply copy from TABLE_B to TABLE_A as the query will fail as soon as I try to change one id to another one that is already present in TABLE_A but linked to a different title.

我知道如何编写Perl或PHP脚本来做到这一点，但是我想知道是否存在纯MySQL解决方案.

I know how to write a Perl or PHP script to do it, but I would like to know if a pure MySQL solution exists.

推荐答案

您可以这样做

CREATE TABLE TableA_TMP AS
SELECT * FROM TableA;

ALTER TABLE TableA_TMP ADD id_new int;

UPDATE TableA_TMP A INNER JOIN TableB B ON lower(A.title) = lower(B.title)
SET id_new = B.id;

RENAME TABLE TableA TO TableA_backup;    

CREATE TableA AS
select id_new as id, title,.... from TableA_TMP;  

这篇关于在存储在两个不同数据库中的两个表之间同步主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！