如何在innoDB MySQL数据库中合并2条记录 [英] How to merge 2 Records in innoDB MySQL databases

问题描述

这与如何在mysql中更改ID

我也检查了其他问题，但都没有一个像这样。

I also have checked other questions and none are quite like this one.

我们知道，innodb有一个功能。例如，如果我想更改记录的ID，则指向前一个ID的所有其他表都将神奇地更新。

As we know, innodb has a feature. If I want to channge an id of a record for example, then all other table that point to the previous ID will magically be updated.

如果我要合并该怎么办2条记录？

What about if I want to MERGE 2 records?

说我有2条业务。

他们有2个ID。

我想将它们合并为一个。我还想使用innodb的强大功能来自动更改内容。

I want to merge them into one. I also want to use innodb awesome feature to automatically change things.

我不能只将其中一个ID更改为另一个ID。还是我可以？

I can't just change one of the id to the other ID. Or can I?

您将如何合并数据库中的2条类似记录？

What would you do to merge 2 simmilar records in database?

当然，实际上进入合并记录将是业务决策。

Of course what actually goes into the combined record will be business decisions.

基本上，我只是不想一一列出所有其他表。我认为关于更新规则是有原因的。有什么方法可以将slaveID更改为masterID，将master中的所有数据保持不变，然后让数据库本身（而不是我的程序）将所有指向slaveID的表都指向masterID？当然，slaveID的记录也将消失。

Basically I just do not want to pin point all the other table one by one. I think on update rule is there for a reason. Is there a way where I just change slaveID to masterID, keep ALL data in master the same, and then have the database itself (rather than my program) to repoint all tables that point to slaveID to point to masterID? of course, records for slaveID will be gone anyway.

例如，对于普通的mysql引擎，您可以更改ID，然后必须遍历所有指向该表的表到旧ID来指向新ID。使用innodb，该重新指向是由数据库引擎本身完成的。哪一种酷。为什么有人会使用非innodb引擎呢？

For example, with normal mysql engine, you can change ID, and then you have to go through all table that points to the old ID to point the new ID instead. With innodb, that repointing is done by the database engine itself. Which is kind of cool. Why would anyone use non innodb engine anyway.

除了合并以外，我也想这样做。

I want to do the same but for merging.

推荐答案

尝试将记录的主键设置为已经存在的值只会导致键冲突错误。乍看之下这很简单，但有一个副作用：您不能使用 ON UPDATE CASCADE 合并两个记录-它根本行不通。

Trying to set a records primary key to an already existing value will simply result in a key violation error. While this is simple on a first glance, it has a side effect: You can not use ON UPDATE CASCADE to merge two records - it will simply not work.

如果可以更改架构，则可以使用旧的但很好的重定向技巧：

If you have the possibility to change the schema, you can use the old but good redirect-trick:

（假设您的ID是正数，可能是整数，而不是整数）

(Assuming your IDs are positive, maybe unsigend ints)

• 添加字段 redirect int not null default 0


• 创建视图：

。

CREATE VIEW tablename_view 
SELECT 
  -- repeat next line for every field apart from redirect
  IF(s.redirect>0,m.<fieldname>,s.<fieldname>
FROM tablename AS s
LEFT JOIN tablename AS m ON s.redirect=m.id

• 将记录（从属）合并到另一个记录（主控）时，运行 UPDATE表名SET redirect = < id_of_master> WHERE id =< id_of_slave>


• 调整您选择的查询es从 tablename_view 中选择，而不是 tablename


• 创建并使用维护清除合并奴隶的脚本


• When you merge a record (slave) into another record (master) run UPDATE tablename SET redirect=<id_of_master> WHERE id=<id_of_slave>
• Adapt your select queries to select from tablename_view instead of tablename
• Create and use a maintenance script to weed out merger slaves

这篇关于如何在innoDB MySQL数据库中合并2条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！