0%

MySQL删除重复记录的方法[2019.8.5]

最前

由于近期在搞一些爬虫,手艺不佳经常挂掉,于是重启之后往往在数据库形成了重复的记录。这里介绍一种方法,试图根据两个或多个字段值去除重复的记录。

查询重复记录

根据 A,B 两个字段进行分组,查询出重复的记录,并显示重复记录(不包括A最小的记录)。

如果需要完整显示重复记录,将WHERE条件中的第二行去除即可。

1
2
3
4
5
6
7
8
( SELECT
ID
FROM
Table1 AS t1
WHERE
( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
)

删除重复记录

针对 MSSQL、PostgreSQL 等数据库

利用上述查询的结果,直接利用IN的方法删除重复记录即可,仅保留A最小的记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
DELETE 
FROM
Table1
WHERE
ID IN (
SELECT
ID
FROM
Table1 AS t1
WHERE
( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
)

针对 MySQL 数据库

有趣的是,当我在 MySQL 数据库中运行上述命令时,会提示如下错误:

1093 - You can’t specify target table ‘Tabel1’ for update in FROM clause

意思是,在 MySQL 数据库中,无法UPDATE在本条查询语句中已经SELECT过的语句,鉴于我是单表查询,所以难以回避这个问题。我的解决方案是使用CREATE TEMPORARY TABLE temp创建临时表,再对临时表进行查询删除操作。

  1. 查询新建临时表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TEMPORARY TABLE temp (
    SELECT
    ID
    FROM
    Table1 AS t1
    WHERE
    ( t1.A, t1.B ) IN ( SELECT A, B FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
    AND t1.A NOT IN ( SELECT min( A ) FROM Table1 GROUP BY A, B HAVING count(*) > 1 )
    )
  2. 根据临时表进行查询删除

1
2
3
4
5
DELETE 
FROM
Table1
WHERE
A IN ( SELECT A FROM temp )

完成查询重复记录,并删除!一般来说,临时表会在关闭数据库连接时自动删除,也可以通过DROP TABLE temp命令手动删除。