如何在 MySQL 中查找并删除重复数据：完整指南及 SQL 示例

1 1. 介绍
2 2. 基础：使用键列检测重复
3 3. 提取所有拥有重复键的记录
4 4. 跨多列检测重复
5 5. Removing Duplicate Records (DELETE)
6 6. Performance Considerations and Index Strategy
7 7. 高级用例：处理复杂场景
8 8. 总结
9 9. FAQ：关于在 MySQL 中提取和删除重复数据的常见问题

1. 介绍

在操作数据库时，遇到诸如“插入了重复记录”或“本应唯一的数据出现多次”等问题并不罕见。在使用 MySQL 等关系型数据库的环境中，提取并管理重复数据是保持数据准确性和质量的关键任务。

例如，在会员信息、产品数据和订单历史等核心业务表中，可能因为用户失误或系统错误而插入了重复记录。如果不加以处理，这会降低聚合和分析的准确性，甚至导致意外的 bug 或运营问题。

要解决这个“重复数据问题”，首先必须识别出哪些记录是重复的，然后根据具体情况对这些重复记录进行整理或删除。然而，仅使用 MySQL 的标准 SELECT 语句往往不足以高效检测重复。需要稍微进阶的 SQL 技巧和实用方法。

本文聚焦于“如何在 MySQL 中提取重复数据”，涵盖从基础 SQL 语句到实战应用、性能考量以及常见错误处理的全部内容。无论你是数据库新手还是每天编写 SQL 的工程师，本指南都旨在提供实用且面向现场的知识。

2. 基础：使用键列检测重复

在 MySQL 中提取重复数据的最基本方式是识别“多个记录在特定列（键列）上拥有相同值”的情况。本节将说明用于检测重复键值的代表性 SQL 查询以及其工作原理。

2-1. 使用 GROUP BY 和 HAVING 检测重复

检测重复的基本技巧是使用 GROUP BY 子句按特定列对记录进行分组，然后通过 HAVING 子句筛选出包含两条或以上记录的分组。下面是一个典型示例：

SELECT key_column, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY key_column
HAVING COUNT(*) > 1;

示例：提取重复的会员电子邮件地址

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

执行此查询后，如果同一电子邮件地址被注册多次，结果中将显示该电子邮件地址以及重复次数（count）。

2-2. 跨多列检测重复

如果需要基于两列或多列的组合来检测重复，可以在 GROUP BY 子句中指定多个列，逻辑相同。

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

使用这种方式，可以检测出如“相同全名和出生日期”或“相同产品 ID 和订单日期”等完全匹配的重复记录。

2-3. 计算重复记录的总数

若想了解整体的重复规模，可以使用子查询来计算所有重复条目的总数。

SELECT SUM(duplicate_count) AS total_duplicates
FROM (
  SELECT COUNT(*) AS duplicate_count
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
) AS duplicates;

该查询会对所有重复分组的重复条目数量求和。

通过组合 GROUP BY 与 HAVING，可以以简洁高效的方式在 MySQL 中提取重复数据。

3. 提取所有拥有重复键的记录

在前一节中，我们介绍了如何仅列出“重复键值”。然而，在实际工作中，往往需要确认“到底哪些具体记录是重复的，并检查它们的全部细节”。例如，你可能需要审查完整的重复用户档案，或逐行检查重复的产品数据。

在本节中，我们解释实用的 SQL 模式，以提取 所有共享重复键的记录。

3-1. 使用子查询提取重复记录

最基本的方法是 在子查询中检索重复键值的列表，然后获取所有匹配这些键的记录。

SELECT *
FROM table_name
WHERE key_column IN (
  SELECT key_column
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
);

示例：提取所有具有重复电子邮件地址的记录

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

当您运行此查询时，它会提取 “users” 表中电子邮件地址重复的所有行（包括 ID、注册日期等列）。

3-2. 使用 EXISTS 高效提取

如果需要处理大型数据集或关注性能，使用 EXISTS 也很有效。IN 和 EXISTS 类似，但根据数据量和索引情况，二者的速度可能有所不同。

SELECT *
FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t1.key_column = t2.key_column
  GROUP BY t2.key_column
  HAVING COUNT(*) > 1
);

示例：重复电子邮件记录（使用 EXISTS）

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1
  FROM users u2
  WHERE u1.email = u2.email
  GROUP BY u2.email
  HAVING COUNT(*) > 1
);

3-3. 注意事项和性能考虑

当数据集较大时，子查询的性能可能受到显著影响。通过适当的索引，IN 和 EXISTS 都可以在实际水平上运行良好。
但是，如果需要复杂条件或想要跨多列确定重复，查询可能会变得很重。请始终先在测试环境中验证其行为。

通过这种方式，使用子查询或 EXISTS 子句即可提取 所有匹配重复键的记录。

4. 跨多列检测重复

重复检测条件并不总是基于单列。实际中，常常需要对多列组合的唯一性进行要求。例如，当 “全名 + 出生日期” 匹配，或 “产品 ID + 颜色 + 尺码” 完全相同时，您可能会将记录视为重复。

在本节中，我们详细解释 如何使用多列提取重复记录。

4-1. 使用 GROUP BY 检测多列重复

要检测跨多列的重复，在 GROUP BY 子句中列出多个以逗号分隔的列。使用 HAVING COUNT(*) > 1，即可仅提取出现两次或以上的组合。

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

示例：通过 “first_name” 和 “birthday” 检测重复

SELECT first_name, birthday, COUNT(*) AS count
FROM users
GROUP BY first_name, birthday
HAVING COUNT(*) > 1;

此查询帮助您识别 “相同姓名” 与 “相同出生日期” 组合被多次注册的情况。

4-2. 提取多列重复键的所有记录

如果需要获取重复键组合的所有记录详情，可以在子查询中提取重复对，然后获取所有匹配这些对的行。

SELECT *
FROM table_name t1
WHERE (col1, col2) IN (
  SELECT col1, col2
  FROM table_name
  GROUP BY col1, col2
  HAVING COUNT(*) > 1
);

示例：获取 “first_name” 和 “birthday” 重复的完整记录

SELECT *
FROM users u1
WHERE (first_name, birthday) IN (
  SELECT first_name, birthday
  FROM users
  GROUP BY first_name, birthday
  HAVING COUNT(*) > 1
);

使用此查询，例如，如果组合 “Taro Tanaka / 1990-01-01” 被多次注册，您可以检索所有相关的详细行。

4-3. 检测完全重复（COUNT DISTINCT）

如果您想估计“在多个列中有多少行是完全重复的”，也可以使用带有 COUNT(DISTINCT ...) 的聚合。

SELECT COUNT(*) - COUNT(DISTINCT col1, col2) AS duplicate_count
FROM table_name;

此 SQL 提供了表中完全重复行的近似计数。

4-4. Notes

即使是多列重复检测，适当的索引也能显著提升查询速度。
如果涉及的列很多或存在 NULL 值，可能会得到意外的重复结果。请仔细设计条件。

通过这种方式，检测并提取跨多列的重复数据可以通过精心设计的 SQL 灵活处理。

5. Removing Duplicate Records (DELETE)

一旦能够提取重复数据，下一步就是删除不必要的重复记录。实际中，常见的做法是在重复记录中只保留一条，删除其余。然而，在 MySQL 中自动删除重复记录时，必须仔细缩小删除目标，以避免意外的数据丢失。

本节我们将说明删除重复数据的常用安全方法以及关键注意事项。

5-1. 使用子查询 + DELETE 删除重复记录

如果您想只保留“最旧”或“最新”的记录并删除其他记录，使用带子查询的 DELETE 语句会很有帮助。

示例：保留最小（最旧）ID 记录并删除其他记录

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

此查询为每个 email 保留最小的 id（即最先注册的记录），并删除所有共享相同 email 的其他行。

5-2. 如何避免 MySQL 特定错误（错误 1093）

在 MySQL 中，当您尝试在 DELETE 同时在子查询中引用同一表时，可能会遇到错误 1093。此时，可以通过将子查询结果包装为派生表（临时结果集）来避免该错误。

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY email
  ) AS temp_ids
);

通过使用 SELECT * FROM (...) AS alias 包装子查询，您可以防止错误并安全地执行删除。

5-3. 删除多列键的重复记录

如果您想基于多个列的组合删除重复记录，可使用带多列的 GROUP BY，并删除除代表记录之外的所有记录。

示例：针对 “first_name” 与 “birthday” 的重复记录，删除除第一条记录之外的所有记录

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY first_name, birthday
  ) AS temp_ids
);

5-4. 删除操作的安全措施与最佳实践

删除重复记录是一项高风险操作，可能永久删除数据。请务必遵循以下最佳实践：

进行备份：在删除之前始终保存整个表或目标记录的备份。
使用事务：如果可能，将操作包装在事务中，以便在出现问题时能够立即回滚。
先用 SELECT 确认数量：养成先运行 SELECT 查询以验证“删除目标是否正确？”的习惯。
检查索引：为用于重复检测的列添加索引，可提升性能和准确性。

在 MySQL 中，您可以通过使用子查询和派生表安全地删除重复数据。始终谨慎操作，进行充分测试并制定可靠的备份策略。

6. Performance Considerations and Index Strategy

在 MySQL 中提取或删除重复数据时，随着表的增大，查询执行时间和服务器负载会变得更加棘手。尤其在大规模系统或批处理作业中，注重性能的 SQL 设计和索引优化至关重要。本节我们将说明 提升性能的技巧 和 索引设计的关键点 在重复数据处理中的应用。

6-1. 在 EXISTS、IN 与 JOIN 之间的选择

SQL 构造如 IN、EXISTS 和 JOIN 常用于提取重复数据，但它们各自具有不同的特性和性能倾向。

IN 当子查询结果集较小时速度快，但随着结果集增大，性能往往会下降。
EXISTS 在找到匹配记录后立即停止搜索，因此在大表或匹配相对稀少的情况下通常更有效。
JOIN 适用于一次性检索大量信息，但如果连接了不必要的数据或缺乏适当的索引，可能会变慢。

示例性能比较

Syntax	Small Data	Large Data	Comment
IN	◎	△	Slow when the result set is large
EXISTS	◯	◎	Advantageous for large databases
JOIN	◯	◯	Proper indexes required

根据实际系统和数据量选择最优的语法非常重要。

6-2. 为什么索引设计很重要

对于用于重复检查或删除过滤的列，必须创建索引。如果没有索引，可能会进行全表扫描，导致性能极度缓慢。

示例：创建索引

CREATE INDEX idx_email ON users(email);

如果要在多个列上检测重复，复合索引同样有效。

CREATE INDEX idx_name_birthday ON users(first_name, birthday);

索引设计可以显著改变读取性能和搜索效率。
注意：索引过多会降低写入速度并增加存储占用，因此需要保持平衡。

6-3. 大数据集的批处理

如果数据集规模在数万到数百万行之间，最好采用 更小的批次 进行处理，而不是一次性处理全部数据。
对于删除和更新操作，限制每次执行处理的行数（例如 LIMIT 1000），并多次运行，以降低锁争用和性能下降。
```
DELETE FROM users
WHERE id IN (
  -- The first 1000 duplicate record IDs extracted by a subquery
)
LIMIT 1000;
```

6-4. 使用执行计划（EXPLAIN）

使用 EXPLAIN 分析查询的执行方式。这有助于检查索引是否被有效利用，以及是否出现全表扫描（ALL）。

EXPLAIN SELECT * FROM users WHERE email IN (...);

只要牢记性能和索引策略，就能 在大数据集下安全高效地进行重复数据处理。

7. 高级用例：处理复杂场景

在实际环境中，重复检测和删除往往比简单匹配更为复杂。可能需要添加额外条件、分阶段安全执行操作，或满足更严格的运维要求。本节我们将介绍 高级实用技术，帮助安全灵活地处理重复数据。

7-1. 条件式重复删除

如果只想删除满足特定条件的重复记录，可策略性地使用 WHERE 子句。

示例：仅删除 email 相同且 status = ‘withdrawn’ 的重复记录

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    WHERE status = 'withdrawn'
    GROUP BY email
  ) AS temp_ids
)
AND status = 'withdrawn';

通过在 WHERE 和 GROUP BY 中添加条件，可精确控制保留哪些记录、删除哪些记录。

7-2. 推荐：批处理与分段执行

如果数据集非常大或希望避免锁争用和性能下降，请使用 批处理。

不要一次性处理所有删除目标——使用 LIMIT 进行分批执行
使用事务控制，并在出现意外错误时回滚

通过备份和日志来降低风险

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    -- Extract duplicate record IDs filtered by conditions
  ) AS temp_ids
)
LIMIT 500;

此方法显著降低系统负载。

7-3. 处理复杂的重复定义

在不同的业务场景中，“重复”的定义各不相同。您可以结合 子查询、CASE 表达式和聚合函数 实现灵活处理。

示例：仅在 product_id、order_date 和 price 完全相同的情况下视为重复

SELECT product_id, order_date, price, COUNT(*)
FROM orders
GROUP BY product_id, order_date, price
HAVING COUNT(*) > 1;

对于更高级的需求，例如“仅保留重复记录中最新的一条”，可以使用子查询或 ROW_NUMBER()（MySQL 8.0 及以上版本可用）。

7-4. 事务和备份的最佳实践

始终在事务中包装 DELETE 或 UPDATE 操作，这样在出现问题时可以使用 ROLLBACK 恢复数据。
在处理重要表或大数据集时，务必事先创建备份。

通过掌握这些高级技术，您可以在任何环境中安全、灵活地处理重复数据。

8. 总结

本文系统地阐述了在 MySQL 中提取和删除重复数据的方法，从基础到高级应用。让我们回顾关键要点。

8-1. 关键要点

检测重复数据：不仅可以在单列中检测重复，也可以跨多列检测。GROUP BY 与 HAVING COUNT(*) > 1 的组合是检测重复的基本模式。
提取所有重复记录：使用子查询和 EXISTS 子句，可检索所有对应重复键值的记录。
删除重复记录：通过 MIN(id) 或 MAX(id) 保留代表行，并结合子查询与 DELETE 语句，可安全删除不必要的重复记录。避免 MySQL 错误 1093 同样重要。
性能与索引：对于大数据集或复杂条件，合理的索引、批量处理以及使用 EXPLAIN 检查执行计划至关重要。
实用技巧：条件删除、分批执行、事务管理和备份是防止生产环境出错的关键做法。

8-2. 按使用场景快速参考

Scenario	Recommended Approach
Single-column duplicate detection	GROUP BY + HAVING
Multi-column duplicate detection	GROUP BY (multiple columns) + HAVING
Retrieve all duplicate records	Subquery (IN / EXISTS)
Safe deletion	Subquery + derived table + DELETE
High-speed processing of large datasets	Indexes + batch processing + EXPLAIN
Conditional duplicate deletion	Combine WHERE clause and transactions

8-3. 防止未来出现重复问题

在插入时防止重复同样重要。

在表设计阶段考虑使用 UNIQUE 约束。
定期进行数据清理和审计，有助于及早发现运营问题。

提取和删除 MySQL 中的重复数据需要从基础 SQL 到高级技巧的全方位知识。希望本指南能帮助您进行数据库维护和系统运维。
如果您有具体案例或进一步的问题，建议查阅 FAQ 或咨询数据库专家。

9. FAQ：关于在 MySQL 中提取和删除重复数据的常见问题

Q1. 为什么使用 `GROUP BY` + `HAVING` 而不是 `DISTINCT`？

DISTINCT 会在结果集中去除重复，但它无法告诉您某个值出现了多少次。通过结合 GROUP BY 与 HAVING COUNT(*) > 1，您可以确定哪些值出现多次以及重复的数量。

Q2. 应该使用 `IN` 还是 `EXISTS`？

对于小数据集，两者差别不大。对于大表或索引有效的情况下，EXISTS 通常表现更佳。请在您的环境中测试两种方式，并使用 EXPLAIN 验证执行计划。

Q3. 如何检测跨多列的重复？

在 GROUP BY 中指定多个列，并使用 HAVING COUNT(*) > 1 来检测所有指定列匹配的组合。例如：GROUP BY first_name, birthday

Q4. 在执行 DELETE 时出现错误 1093。我该怎么办？

当在 DELETE 语句的子查询中引用同一张表时，MySQL 会抛出错误 1093。可以使用 SELECT * FROM (...) AS alias 将子查询结果包装成派生表，以避免该错误。

Q5. 如何安全地删除重复数据？

在删除之前始终先创建备份，使用 SELECT 语句验证目标，并在可能的情况下使用事务。对于大型数据集，批量删除也更安全。

Q6. 当查询在大数据量下变慢时该怎么办？

在用于重复检测的列上创建索引。使用带 LIMIT 的批处理，并通过 EXPLAIN 检查执行计划，以避免不必要的全表扫描。

Q7. 如何从根本上防止重复插入？

在表设计时定义 UNIQUE 约束或唯一键，以防止插入重复值。同时，在部署后定期进行重复检查和数据清理。

Q8. 这些方法可以在 MariaDB 或其他 RDBMS 中使用吗？

基本的 SQL 构造，如 GROUP BY、HAVING 和子查询，在 MariaDB 和 PostgreSQL 中也受支持。不过，DELETE 子查询的限制以及性能特性可能因产品而异，建议提前进行测试。