MySQL 如何从mysql db中删除重复条目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2867530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove duplicate entries from a mysql db?
提问by unutbu
I have a table with some ids + titles. I want to make the title column unique, but it has over 600k records already, some of which are duplicates (sometimes several dozen times over).
我有一个带有一些 ID + 标题的表格。我想让标题列独一无二，但它已经有超过 60 万条记录，其中一些是重复的（有时是几十倍）。
How do I remove all duplicates, except one, so I can add a UNIQUE key to the title column after?
如何删除除一个之外的所有重复项，以便我可以在之后的标题列中添加一个 UNIQUE 键？
回答by unutbu
This command adds a unique key, and drops all rows that generate errors (due to the unique key). This removes duplicates.
此命令添加一个唯一键，并删除所有生成错误的行（由于唯一键）。这将删除重复项。
ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(title);
Edit: Note that this command may not work for InnoDB tablesfor some versions of MySQL. See this postfor a workaround. (Thanks to "an anonymous user" for this information.)
编辑：请注意，此命令可能不适用于某些 MySQL 版本的InnoDB 表。请参阅此帖子以获取解决方法。（感谢“匿名用户”提供此信息。）
回答by nc3b
Create a new table with just the distinct rows of the original table. There may be other ways but I find this the cleanest.
创建一个仅包含原始表的不同行的新表。可能还有其他方法，但我觉得这是最干净的。
CREATE TABLE tmp_table AS SELECT DISTINCT [....] FROM main_table
More specifically:
The faster way is to insert distinct rows into a temporary table. Using delete, it took me a few hours to remove duplicates from a table of 8 million rows. Using insert and distinct, it took just 13 minutes.
更具体地说：
更快的方法是将不同的行插入到临时表中。使用删除，我花了几个小时从一个 800 万行的表中删除重复项。使用 insert 和 distinct 只需要 13 分钟。
CREATE TABLE tempTableName LIKE tableName;
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;
DROP TABLE tableName;
INSERT tableName SELECT * FROM tempTableName;
DROP TABLE tempTableName;
回答by Mohammed Abrar Ahmed
Below query can be used to delete all the duplicate except the one row with lowest "id" field value
下面的查询可用于删除除“id”字段值最低的一行之外的所有重复项
DELETE t1 FROM table_name t1, table_name t2 WHERE t1.id > t2.id AND t1.name = t2.name
In the similar way, we can keep the row with the highest value in 'id' as follows
同理，我们可以保留 'id' 中值最高的那一行，如下所示
DELETE t1 FROM table_name t1, table_name t2 WHERE t1.id < t2.id AND t1.name = t2.name
回答by César Revert-Gomar
Deleting duplicates on MySQL tables is a common issue, that usually comes with specific needs. In case anyone is interested, here (Remove duplicate rows in MySQL) I explain how to use a temporary table to delete MySQL duplicates in a reliable and fast way (with examples for different use cases).
删除 MySQL 表上的重复项是一个常见问题，通常伴随着特定需求。如果有人感兴趣，这里（删除 MySQL 中的重复行）我解释了如何使用临时表以可靠和快速的方式删除 MySQL 重复项（使用不同用例的示例）。
In this case, something like this should work:
在这种情况下，这样的事情应该有效：
-- create a new temporary table
CREATE TABLE tmp_table1 LIKE table1;
-- add a unique constraint
ALTER TABLE tmp_table1 ADD UNIQUE(id, title);
-- scan over the table to insert entries
INSERT IGNORE INTO tmp_table1 SELECT * FROM table1 ORDER BY sid;
-- rename tables
RENAME TABLE table1 TO backup_table1, tmp_table1 TO table1;
回答by souLTower
This shows how to do it in SQL2000. I'm not completely familiar with MySQL syntax but I'm sure there's something comparable
这显示了如何在 SQL2000 中执行此操作。我不完全熟悉 MySQL 语法，但我确定有类似的东西
create table #titles (iid int identity (1, 1), title varchar(200))
-- Repeat this step many times to create duplicates
insert into #titles(title) values ('bob')
insert into #titles(title) values ('bob1')
insert into #titles(title) values ('bob2')
insert into #titles(title) values ('bob3')
insert into #titles(title) values ('bob4')
DELETE T FROM
#titles T left join
(
select title, min(iid) as minid from #titles group by title
) D on T.title = D.title and T.iid = D.minid
WHERE D.minid is null
Select * FROM #titles
回答by Nitin
delete from student where id in (
SELECT distinct(s1.`student_id`) from student as s1 inner join student as s2
where s1.`sex` = s2.`sex` and
s1.`student_id` > s2.`student_id` and
s1.`sex` = 'M'
ORDER BY `s1`.`student_id` ASC
)
回答by StéphanS
The solution posted by Nitin seems to be the most elegant / logical one.
Nitin 发布的解决方案似乎是最优雅/合乎逻辑的解决方案。
However it has one issue:
但是它有一个问题：
ERROR 1093 (HY000): You can't specify target table 'student' for update in FROM clause
错误 1093 (HY000)：您不能在 FROM 子句中指定要更新的目标表“学生”
This can however be resolved by using (SELECT * FROM student) instead of student:
然而，这可以通过使用 (SELECT * FROM student) 而不是 student 来解决：
DELETE FROM student WHERE id IN (
SELECT distinct(s1.`student_id`) FROM (SELECT * FROM student) AS s1 INNER JOIN (SELECT * FROM student) AS s2
WHERE s1.`sex` = s2.`sex` AND
s1.`student_id` > s2.`student_id` AND
s1.`sex` = 'M'
ORDER BY `s1`.`student_id` ASC
)
Give your +1's to Nitin for coming up with the original solution.
将您的 +1 给 Nitin，以提出原始解决方案。
回答by Stephen Ostermiller
Since the MySql ALTER IGNORE TABLEhas been deprecated, you need to actually delete the duplicate date before adding an index.
由于 MySqlALTER IGNORE TABLE已被弃用，因此您需要在添加索引之前实际删除重复的日期。
First write a query that finds all the duplicates. Here I'm assuming that emailis the field that contains duplicates.
首先编写一个查找所有重复项的查询。在这里，我假设这email是包含重复项的字段。
SELECT
s1.email
s1.id,
s1.created
s2.id,
s2.created
FROM
student AS s1
INNER JOIN
student AS s2
WHERE
/* Emails are the same */
s1.email = s2.email AND
/* DON'T select both accounts,
only select the one created later.
The serial id could also be used here */
s2.created > s1.created
;
Next select only the unique duplicate ids:
接下来只选择唯一的重复 ID：
SELECT
DISTINCT s2.id
FROM
student AS s1
INNER JOIN
student AS s2
WHERE
s1.email = s2.email AND
s2.created > s1.created
;
Once you are sure that only contains the duplicate ids you want to delete, run the delete. You have to add (SELECT * FROM tblname)so that MySql doesn't complain.
一旦确定只包含要删除的重复 ID，请运行删除。您必须添加，(SELECT * FROM tblname)以便 MySql 不会抱怨。
DELETE FROM
student
WHERE
id
IN (
SELECT
DISTINCT s2.id
FROM
(SELECT * FROM student) AS s1
INNER JOIN
(SELECT * FROM student) AS s2
WHERE
s1.email = s2.email AND
s2.created > s1.created
);
Then create the unique index:
然后创建唯一索引：
ALTER TABLE
student
ADD UNIQUE INDEX
idx_student_unique_email(email)
;
