我有两个包含两个表的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;更多推荐
在存储在两个不同数据库中的两个表之间同步主键
发布评论