MySQL删除重复数据

查找重复数据

select 重复字段 From 表 Group By 重复字段 Having Count(*)>1

查询全部重复数据

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

删除方法一：删除多余重复数据，只保留id号最大的一条

delete from 表 where id not in (select temp.maxid from (SELECT  MAX(id) AS maxid FROM 表 GROUP BY 重复字段) temp)

删除方法二：删除多余重复数据，只保留id号最小的一条

delete from 表 where id not in (select temp.minid from (SELECT  min(id) AS minid FROM 表 GROUP BY 重复字段) temp)

删除方法三：

1.先找出重复数据的id

SELECT  MAX(id) AS maxid FROM 表 GROUP BY 重复字段 Having Count(重复字段)>1;

#如果数据量大，也可以先查1000条
#SELECT  MAX(id) AS maxid FROM 表 GROUP BY 重复字段 Having Count(重复字段)>1 limit 1000;

2.删除这些id

delete from 表 where id in (select temp.maxid from (SELECT  MAX(id) AS maxid FROM 表 GROUP BY 重复字段 Having Count(重复字段)>1) temp)