是否有一种清理MySQL清理重复条目的方法？

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"?

在表格中,我有三列 - id,name和count。很多名称列都是相同的(由于早期缺少UNIQUE),我想解决这个问题。但是,id列被其他人使用(4或5,我认为 - 我必须检查文档)表来查找名称,只是删除它们会破坏事物。那么有一种好的,干净的方式来说“找到所有相同的记录并将它们合并在一起”吗?

2 个解决方案

#1

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:

MySQL支持多表UPDATE和DELETE语句(与其他品牌的数据库不同),所以你可以做一些非常巧妙的技巧,如下所示:

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:

在所有子表上完成此操作后,可以使用MySQL的多表DELETE语法删除父表中不需要的行:

DELETE FROM n2
  USING names n1 JOIN names n2 ON (n1.id < n2.id AND n1.name = n2.name);

#2

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>)

#1