mysql中如何将一个表中的部分记录合并,如何在innoDB MySQL数据库中合并2条记录

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

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.

What about if I want to MERGE 2 records?

Say I have 2 businesses.

They have 2 ID.

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

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

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

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

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.

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.

解决方案

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:

(Assuming your IDs are positive, maybe unsigend ints)

add a field redirect int not null default 0

Create a view:

.

CREATE VIEW tablename_view

SELECT

-- repeat next line for every field apart from redirect

IF(s.redirect>0,m.,s.

FROM tablename AS s

LEFT JOIN tablename AS m ON s.redirect=m.id

When you merge a record (slave) into another record (master) run UPDATE tablename SET redirect= WHERE id=

Adapt your select queries to select from tablename_view instead of tablename

Create and use a maintenance script to weed out merger slaves