mysql 查询重复数据并删除

表名： articles ，

内容重复字段：title，

准备过程：

Navicate 数据表导出sql，将导出dsql导入到本地测试库，查看title字段为varchar类型且没有索引，本地库title设置title字段普通索引（未设置索引的情况下sql查询耗时太久，等了一分钟都没出结果)

Navicate 执行操作过程:

1. 查询标题重复的数据量：

select count(*) from articles
where title in
 (select title from articles group by title having count(*) > 1)

2. 查询重复的数据量，排除主键id最小的重复记录

select count(*) from articles
where title in 
(select title from articles group by title having count(*) > 1) 
and id not in 
( select min(id) from  articles  group by title  having count(* )>1)

3. 查询重复的数据的id，和 title

select id,title from articles where title in 
(select title from articles group by title having count(*) > 1) 
and id not in 
( select min(id) from  articles  group by title  having count(* )>1)

4. 查询所有重复的记录的id兵进行字符串拼接，排除主键id最小的重复记录

select GROUP_CONCAT(id) from articles where title in
 (select title from articles group by title having count(*) > 1 )
and id not in 
( select min(id) from  articles  group by title  having count(* )>1 ) 

5. 将第4步查询出来的重复数据id拼接的字符串作为条件进行数据删除

delete from articles where id in (第4步查询出的id字符串)

6. 检查本地测试库中article表内重复数据已被删除，将第5步的sql在线上执行。第四步和第五步要多次执，因为GROUP_CONCAT 一次拼接的id 是有限的，可能没有全部拼接出来

方法二：

该方法 title字段必须加索引，加索引的情况下，7W条数据删除8K条执行了49秒

DELETE
FROM
	表名称
WHERE
	重复字段名 IN (
		SELECT
			tmpa.重复字段名
		FROM
			(
				SELECT
					重复字段名
				FROM
					表名称
				GROUP BY
					重复字段名
				HAVING
					count(1) > 1
			) tmpa
	)
AND id NOT IN (
SELECT
	tmpb.minid
FROM
	(
		SELECT
			min(id) AS minid
		FROM
			表名称
		GROUP BY
			重复字段名
		HAVING
			count(1) > 1
	) tmpb
)