有干净的清理方式在MySQL重复的条目吗?Is there a clean way of cleaning up duplicate entries in MySQL?

- 此内容更新于:2014-12-30
主题:

原文:

In a table, I have three columns - id, name, and count. A good number of name columns are identical (due to the lack of a UNIQUE early on) and I want to fix this. However, the id column is used by other (4 or 5, I think - I would have to check the docs) tables to look up the name and just removing them would break things. So is there a good, clean way of saying "find all identical records and merge them together"?

解决方案:
原文:

This kind of question comes up from time to time. No, there's not a really clean way to do it. You have to change all the rows in the child table that depend on unwanted values in the parent table before you can eliminate the unwanted rows in the parent table.

MySQL supports multi-table UPDATE and DELETE statements (unlike other brands of database) and so you can do some pretty neat tricks like the following:

UPDATE names n1
  JOIN names n2 ON (n1.id < n2.id AND n1.name = n2.name)
  JOIN child_table c ON (n2.id = c.id)
SET c.name_id = n1.id
ORDER BY n1.id DESC;

Once you have done this on all the child table(s), you can use MySQL's multi-table DELETE syntax to remove unwanted rows in the parent table:

DELETE FROM n2
  USING names n1 JOIN names n2 ON (n1.id < n2.id AND n1.name = n2.name);
解决方案:
为什么你不能做点什么呢
原文:

Why can't you do something like

update dependent_table set name_id = <id you want to keep> where name_id in (
    select id from names where name = 'foo' and id != <id you want to keep>)