灯下哥谭
灯下哥谭
2022-03-17
目录

MySQL使用SQL语句查重去重原创

# 前言

在数据库运维过程中，经常会遇到数据重复的问题，比如由于唯一索引调整、程序bug等原因导致的重复数据写入。本文介绍如何使用SQL语句来识别和清理重复数据。

版本说明

本文写于 2022-03。
查重去重的 SQL 写法（GROUP BY/窗口函数）未变，经 2026-07 复核仍适用。

# 查找重复数据的基本思路

查找重复数据的核心思路是：

  1. 确定用于判断重复的字段组合
  2. 使用子查询找出重复记录中除了最新记录之外的其他记录
  3. 可以选择保留最早或最新的记录，通常我们保留最新记录（通过max函数）

# 查找重复数据的SQL模板

点击查看SQL模板 
SELECT * FROM
  table_name AS ta
WHERE
  ta.id <> (
    SELECT
      max(tb.id)
    FROM
      table_name AS tb
    WHERE
      ta.duplicate_column = tb.duplicate_column  -- 用于判断重复的字段
  );
1
2
3
4
5
6
7
8
9
10
11

这个模板中：

  • table_name 是要操作的表名
  • id 是表的主键
  • duplicate_column 是用于判断重复的字段，可以是单个字段，也可以是多个字段的组合

⚠️ 这个写法有两个必须知道的限制：

第一，NULL 值的重复它找不到。 关联条件 ta.col = tb.col 在 col 为 NULL 时结果是 NULL 而不是 TRUE——SQL 里 NULL = NULL 不成立。所以如果重复行的判重字段是 NULL，这条 SQL 会一条都查不出来，而你会以为「没有重复」。判重字段可空时，把等号换成 <=>（NULL 安全等值）：

WHERE ta.duplicate_column <=> tb.duplicate_column
1

第二，它是相关子查询，大表上会很慢。 外层每一行都要触发一次内层聚合，复杂度接近 O(n²)。几万行还能忍，几十万行以上应该用下面的窗口函数写法。

# 更好的写法：窗口函数（MySQL 8.0）

8.0 之后不需要再写相关子查询了。ROW_NUMBER() 一遍扫描就能给每组重复行编号，逻辑也直白得多：

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY tran_start_time, tran_end_time, member_account   -- 判重字段组合
      ORDER BY id DESC                                              -- DESC = 保留 id 最大的那条
    ) AS rn
  FROM my_test_table_name
  WHERE updated_at >= '2022-03-15 18:20:00'
    AND updated_at <  '2022-03-15 19:30:00'
)
SELECT * FROM my_test_table_name
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
1
2
3
4
5
6
7
8
9
10
11
12
13

rn = 1 是每组里要保留的那条，rn > 1 就是要删的。想改成「保留最早的」把 ORDER BY id DESC 改成 ASC 即可，比改子查询里的 MAX/MIN 直观得多，也不会踩 NULL 的坑（PARTITION BY 把 NULL 当作同一组）。

时间条件用左闭右开（>= 配 <）而不是 BETWEEN：BETWEEN 两端都是闭区间，按小时/天切片处理时会在边界上重复计入同一行。

# 原始写法（相关子查询，兼容 5.7）

# 实际应用示例

下面是一个更复杂的实际应用示例，展示了如何在特定时间范围内查找多字段组合重复的数据：

点击查看SQL实例 
SELECT * FROM
  my_test_table_name
WHERE
  id IN (
    SELECT id
    FROM (
      SELECT *
      FROM my_test_table_name
      WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
    ) AS ta
    WHERE ta.id <> (
      SELECT max(tb.id)
      FROM (
        SELECT *
        FROM my_test_table_name
        WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
      ) AS tb
      WHERE
        ta.tran_start_time = tb.tran_start_time
        AND ta.tran_end_time = tb.tran_end_time
        AND ta.member_account = tb.member_account
    )
  );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

这个示例展示了：

  • 如何在指定时间范围内查找重复数据
  • 如何基于多个字段组合（tran_start_time、tran_end_time、member_account）判断重复
  • 如何使用子查询优化查询性能

# 删除重复数据

确认查询结果无误后，可以将 SELECT 语句改写为 DELETE 语句来删除重复数据：

点击查看删除语句 
DELETE FROM
  my_test_table_name
WHERE
  id IN (
    SELECT id
    FROM (
      SELECT *
      FROM my_test_table_name
      WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
    ) AS ta
    WHERE ta.id <> (
      SELECT max(tb.id)
      FROM (
        SELECT *
        FROM my_test_table_name
        WHERE updated_at BETWEEN '2022-03-15 18:20:00' AND '2022-03-15 19:30:00'
      ) AS tb
      WHERE
        ta.tran_start_time = tb.tran_start_time
        AND ta.tran_end_time = tb.tran_end_time
        AND ta.member_account = tb.member_account
    )
  );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 删除的窗口函数版本

MySQL 不允许在 DELETE 的子查询里直接引用同一张表（ERROR 1093），所以要多包一层派生表「物化」一下：

DELETE FROM my_test_table_name
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY tran_start_time, tran_end_time, member_account
             ORDER BY id DESC
           ) AS rn
    FROM my_test_table_name
    WHERE updated_at >= '2022-03-15 18:20:00'
      AND updated_at <  '2022-03-15 19:30:00'
  ) t
  WHERE t.rn > 1
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

多出来的那层 SELECT id FROM (...) t 不是冗余——去掉它就会报 1093。

# 验证

删之前：先数清楚要删多少，把 DELETE 换成 SELECT COUNT(*) 跑一遍，和「总行数 − 去重后行数」对上：

-- 预期要删的行数
SELECT COUNT(*) - COUNT(DISTINCT tran_start_time, tran_end_time, member_account) AS should_delete
FROM my_test_table_name
WHERE updated_at >= '2022-03-15 18:20:00' AND updated_at < '2022-03-15 19:30:00';
1
2
3
4

这个数字和上面 rn > 1 查出的行数必须完全一致。对不上说明判重字段选错了或有 NULL 参与，先查清楚再动手。

删之后：确认每组只剩一条：

SELECT tran_start_time, tran_end_time, member_account, COUNT(*) AS c
FROM my_test_table_name
GROUP BY tran_start_time, tran_end_time, member_account
HAVING c > 1;
1
2
3
4

预期输出：空集。

# 坑与边界

  • 删完必须立刻加唯一索引，否则重复会再长出来。 去重是一次性的，防重复是结构性的：

    ALTER TABLE my_test_table_name
  ADD UNIQUE KEY uk_dedup (tran_start_time, tran_end_time, member_account);
    1
    2

    只清理不加约束，过几周同样的工单会再来一次。注意唯一索引对 NULL 不生效——MySQL 允许唯一索引列里存在多个 NULL，判重字段可空时唯一索引挡不住重复，得先把列改成 NOT NULL。

  • 大表删除要分批。一次 DELETE 几十万行会产生巨大的 undo 与 binlog、长时间持有行锁、并把从库延迟拉高。按主键分段循环，每批几千行，批间 sleep：

    DELETE FROM my_test_table_name WHERE id IN (...) LIMIT 2000;
    1

    循环执行到 ROW_COUNT() 返回 0 为止。

  • 判重字段里有浮点数时不要用等值判重。FLOAT/DOUBLE 的等值比较不可靠，看起来一样的两个值可能不等。这类字段应先 ROUND() 或改用 DECIMAL。

  • 判重字段是字符串时，排序规则决定了什么算「重复」。utf8mb4_0900_ai_ci 下 'ABC' 和 'abc' 是同一个值，utf8mb4_bin 下则不是。去重前先确认列的 collation 与业务预期一致（见 MySQL不同字符集之间的区别和选择）。

  • 先备份。DELETE 没有 undo 按钮。至少把要删的行先 CREATE TABLE bak_xxx AS SELECT ... 存一份，确认无误后再清理备份表。

# 注意事项

  1. 执行删除操作前，强烈建议先使用 SELECT 语句验证查询结果
  2. 建议在删除前做好数据备份
  3. 如果数据量较大，建议分批处理，避免长时间锁表
  4. 删除后检查唯一索引约束，确保不会再次产生重复数据
#数据迁移#性能优化
上次更新: 9/11/2026

← MySQLdump逻辑备份→

最近更新
01
DeepSeek Harness 实战 06｜学习笔记：插件、工具、技能不在同一个维度上 原创
09-11
02
DeepSeek Harness 实战 05｜让两个编码 Agent 共用一份长期记忆 原创
09-09
03
DeepSeek Harness 实战 04｜学习笔记：从「已知限制」里读出三处设计张力 原创
09-08
更多文章>
Theme by Vdoing