mysql 查询重复数据并删除

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

 

发表评论

您的电子邮箱地址不会被公开。