MySQL中查询和删除重复行

1、重复行的定义

在不同的业务场景中，可能我们对重复行的定义不一样，比如以下的表记录：

在某些场景下，我们把name字段重复的记录称为重复行，而在另一些场景下，我们把name和address都重复的记录称为重复行，或者把name，sex，birth，address这4个字段都重复的记录称为重复行，具体可区分为两大类：单字段重复和多字段重复，无论是哪一种，我们的目的都是将重复的记录找出来，然后删除掉多余的，只保留一行，这是我们的最终目的。

2、单字段重复

这种某一个字段重复的情况很简单，我们只需要将这个字段用group by进行分组，然后用count聚合函数和having子句进行过滤就行了。

2.1、查询重复记录

还是以这张表为例：

现在我认为name重复的记录就是重复记录，先找出重复记录，SQL语句：

SELECT
	* 
FROM
USER 
WHERE
	NAME IN ( SELECT NAME FROM USER GROUP BY NAME HAVING count(*) > 1 );

查询结果：

结果是没问题的，OK，找出重复记录后的下一步是删除多余记录，只保留其中一条。

2.2、删除多余行

因为删除是按主键Id来进行删除的，修改上面的SQL语句：

SELECT
	min( id ) 
FROM
USER 
WHERE
	NAME IN ( SELECT NAME FROM USER GROUP BY NAME HAVING count(*) > 1 ) 
GROUP BY
NAME;

查询结果：

可以考虑用min(id)来进行筛选，保留重复记录最小id对应的记录，其余重复的删掉。

SELECT
	min( id ) 
FROM
USER 
GROUP BY
NAME;

刚好重复的几条记录的id都不在结果集中，那么最终的删除语句为：

DELETE 
FROM
USER 
WHERE
	id NOT IN ( SELECT tmp.mid FROM ( SELECT min( id ) mid FROM USER GROUP BY NAME ) tmp );

执行完毕后的表记录：

name字段重复的记录只保留下了id最小的一条，其余重复的删掉了。

3、多字段重复

先补充一下表记录：

3.1、查询重复记录

现在我认为name，sex和address都相同的记录才是重复的，那么先查询出来：

SELECT
	* 
FROM
USER 
WHERE
	( NAME, sex, address ) IN (
	SELECT NAME
		,
		sex,
		address 
	FROM
	USER 
	GROUP BY
		NAME,
		sex,
		address 
	HAVING
	count(*) > 1 
	);

查询结果：

结果没问题。

3.2、删除多余行

还是使用min(id)来筛选，先查询：

SELECT
	min( id ) 
FROM
USER 
GROUP BY
	NAME,
	sex,
	address

然后删除，最终语句：

DELETE 
FROM
USER 
WHERE
	id NOT IN ( SELECT tmp.mid FROM ( SELECT min( id ) mid FROM USER GROUP BY NAME, sex, address ) tmp );

执行后的表记录：

可以看到，name、sex、address都相同的重复记录只保留了一条，其余多的删掉了。

4、小结

重复记录的定义不同，可根据具体业务场景来进行筛选删除，无论是单字段还是多字段重复，大体思路都一样，借助min(id)或max(id)来辅助筛选删除。