MySQL查找重复并删除它们 [英] mySQL find dupes and remove them

问题描述

我想知道是否有一种方法可以通过一个查询来做到这一点.

I am wondering if there is a way to do this through one query.

当我最初使用虚假数据填充数据库以处理10k条记录时，似乎一团糟，所有脚本都将多余的1,044行虚拟化了，其中这些行是重复的.我使用

Seems when I was initially populating my DB with dummy data to work with 10k records, somewhere in the mess of it all the script dummped an extra 1,044 rows where the rows are duplicates. I determined this using

SELECT x.ID, x.firstname FROM info x
INNER JOIN (SELECT ID FROM info
GROUP BY ID HAVING count(id) > 1) d ON x.ID = d.ID

我要弄清楚的是，通过这个单个查询，我是否可以再添加一个片段，以便从找到的每个重复对象中删除匹配的重复对象之一?

What I am trying to figure out is through this single query can I add another piece to it that will remove one of the matching dupes from each dupe found?

我也意识到应该将ID列设置为自动递增，但不是

also I realize the ID column should have been set to auto increment, but it wasn't

推荐答案

我最喜欢的删除重复项的方法是:

My favorite way of removing duplicates would be:

ALTER IGNORE TABLE info ADD UNIQUE (ID);

要进一步解释(作为参考，请参阅这里)

To explain a bit further (for reference, take a look here)

唯一性-您正在向ID列添加唯一索引.

UNIQUE - you are adding unique index to ID column.

IGNORE -是对标准SQL的MySQL扩展.如果新表中的唯一键上有重复项，或者在启用严格模式时出现警告，则它控制ALTER TABLE的工作方式.如果未指定IGNORE，则在发生重复键错误的情况下，副本将中止并回滚.如果指定了IGNORE，则只有第一行用于唯一键重复的行.其他冲突的行将被删除.不正确的值将被截断为最接近的匹配可接受值.

IGNORE - is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

这篇关于MySQL查找重复并删除它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！