mysql 查询重复数据并删除

表名： articles ，内容重复字段：title，

准备过程：

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