SQL快速识别与删除表中重复记录的方法

先说说核心操作思路：用 GROUP BY + HA VING 能精准揪出按指定字段判定的重复值——比如 SELECT email, COUNT(*) FROM users GROUP BY email HA VING COUNT(*) > 1 就能把重复邮箱全列出来。但注意，这一步只能“识别”，不能直接删；真要清理，得配合子查询、窗口函数或临时表，保留最小或最大 id 的那一行。而且动手之前务必备份、验证，执行完再赶紧加上唯一索引，从源头堵住。下面展开细说。

用 GROUP BY + HA VING 找出重复主键字段

直接查重复行，关键不是看整行是否一样，而是明确你按哪些字段判定“重复”。比如用户表中 email 不该重复，那就只对 email 分组；如果业务要求 name 和 phone 组合唯一，就得写 GROUP BY name, phone。

常见错误是写 SELECT * 配合 GROUP BY —— 大多数数据库（如 MySQL 严格模式、PostgreSQL）会报错，因为非分组字段值不明确。正确做法是先聚焦识别逻辑：

SELECT email, COUNT(*) FROM users GROUP BY email HA VING COUNT(*) > 1;

这条语句能快速暴露哪些 email 出现了多次，且返回每组的出现次数，方便评估影响范围。

安全删除重复行：保留最小/最大 id 的那一行

删之前必须决定留哪一条——通常留 id 最小的（最早插入），或最大的（最新更新）。别用 DELETE FROM table WHERE ... 直接套子查询删全部，容易误删或锁表太久。

推荐用自连接或窗口函数（取决于数据库版本）：

• MySQL 5.7+ / PostgreSQL / SQL Server：用 ROW_NUMBER() 窗口函数标记重复组内的顺序
• SQLite 或老版本 MySQL：用自连接找“更大 id”的重复行，再删它们

例如在支持窗口函数的库中，删掉每个重复 email 中除最小 id 外的所有行：

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t
  WHERE t.rn > 1
);

注意：PARTITION BY email 定义重复组，ORDER BY id 决定谁被保留。改用 ORDER BY id DESC 就保留最新那条。

执行前务必备份或用事务包裹

删除操作不可逆，尤其当表大、索引缺失、或 WHERE 条件写错时，可能删掉全部数据。线上环境严禁跳过验证步骤。

实操建议：

• 先用 SELECT 把将被删的 id 全部查出来，人工抽检几条确认逻辑
• 在事务里执行：BEGIN; DELETE ...; SELECT COUNT(*) FROM users WHERE id IN (...); ROLLBACK;（测试完再 COMMIT）
• 避免在高峰期跑大表去重，DELETE 可能触发大量索引更新和锁等待

有些数据库（如 MySQL InnoDB）对大事务有 innodb_log_file_size 限制，删几十万行以上建议分批，比如每次删 1000 行加 LIMIT 1000。

后续预防比清理更重要

删完只是止血，真正要解决的是源头。重复数据大概率是因为缺少约束，而不是应用层没校验。

立刻补上唯一索引：

CREATE UNIQUE INDEX idx_users_email ON users(email);

如果已有重复，建索引会失败，得先清完再建。另外注意：

• NULL 值在唯一索引中不参与冲突判断（多数数据库允许多个 NULL），如果业务允许空邮箱，得额外用触发器或应用层控制
• 复合唯一约束写法是 CREATE UNIQUE INDEX idx_name_phone ON users(name, phone)
• 建完索引后，下次插入重复值会直接报错 ERROR 1062 (23000): Duplicate entry ... for key ...，比事后清理成本低两个数量级

真正麻烦的不是怎么删，而是删完发现下个月又冒出来——说明约束没加，或者上游系统绕过了校验逻辑。