MySQL 使用 CTE 删除重复数据

以下是关于使用 MySQL 的 CTE 删除重复数据的详细说明。本文将通过CTE的介绍、删除重复数据的场景与应用示例、常见误区以及注意事项等方面，帮助您深入理解如何高效地使用 公共表表达式（CTE，Common Table Expression） 来处理重复数据。

一、什么是 CTE？

CTE（Common Table Expression，公共表表达式） 是一种临时的命名结果集，可以在 SQL 查询中反复使用，从而简化复杂查询的结构。MySQL 从 8.0 版本开始支持 CTE，提供了极大灵活性，使得对表的查询和操作更加直观和清晰。
在处理重复数据时，CTE 非常有用。我们可以使用 CTE 来定位需要删除的重复数据，并进一步进行删除操作，从而保证数据的唯一性和一致性。
> ? 小结：CTE 是一种临时命名查询结果集，特别适合用于复杂查询和操作的简化，例如删除重复数据等任务。

二、删除重复数据的场景

在日常的数据库操作中，经常会由于各种原因（如数据导入或用户误操作）导致数据表中产生重复数据。例如，一个表中可能会有多行记录的 关键字段（如姓名和电子邮件） 完全一致。为了维护数据的一致性，需要将这些重复记录删除，仅保留一条。

删除重复数据的原则

1. 找出重复数据：定义哪些字段的组合代表重复数据，例如 姓名和电子邮件。
2. 保留最新/最旧的数据：通常的需求是保留最早或最新的一条记录。
3. 删除其他重复记录：确保最终表中只有唯一的有效记录。

   三、使用 CTE 删除重复数据的实现步骤

   下面我们来看具体如何使用 MySQL 的 CTE 删除重复数据。假设我们有一个名为 employees 的表，其中包含一些重复数据。表的结构如下：	id	name	email
   1	John Doe	john@example.com
   2	Jane Smith	jane@example.com
   3	John Doe	john@example.com
   4	John Doe	john@example.com

   我们希望删除 name 和 email 相同的重复数据，仅保留最小的 id。

   1. 使用 CTE 找出重复数据

   首先，通过 CTE 找出重复数据，并确定每组重复数据中需要保留的记录。可以使用 窗口函数 来实现这一目标。

   WITH cte AS (
   SELECT
   id,
   name,
   email,
   ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
   FROM
   employees
   )
   SELECT * FROM cte;

   代码解释

   • WITH cte AS (...)：定义一个名为 cte 的公共表表达式。
   • ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id)：为每组具有相同 name 和 email 的记录分配唯一的行号，按照 id 升序排序。其中 rn = 1 的记录为我们想要保留的。
   • PARTITION BY：用于对每个 name 和 email 的组合进行分组。

   • ORDER BY id：通过 id 进行排序，以确定要保留的记录。 上面的查询结果会为每组重复记录生成一个 行号（rn），如下所示：	id	name	email	rn
     1	John Doe	john@example.com	1
     3	John Doe	john@example.com	2
     4	John Doe	john@example.com	3
     2	Jane Smith	jane@example.com	1

     2. 删除重复数据

     在上一步中，我们得到了每组重复记录的行号，现在我们只需要删除 rn > 1 的记录即可。

     WITH cte AS (
     SELECT
     id,
     name,
     email,
     ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
     FROM
     employees
     )
     DELETE FROM employees
     WHERE id IN (
     SELECT id FROM cte WHERE rn > 1
     );

     代码解释

   • DELETE FROM employees：删除操作的目标表为 employees。
   • WHERE id IN (...)：通过子查询确定要删除的记录。
   • SELECT id FROM cte WHERE rn > 1：从 CTE 中选择 rn > 1 的记录的 id，这些即为需要删除的重复数据。

     CTE 删除重复数据的工作流程

     flowchart TD
     A[定义 CTE 找出重复数据] --> B[为每组记录分配行号]
     B --> C[筛选出行号大于 1 的记录]
     C --> D[执行删除操作]
     D --> E[完成重复数据删除]

     > ? 总结：通过 CTE 和窗口函数，我们可以方便地找到重复记录，并对其进行删除操作，从而保持数据的唯一性和一致性。

     四、注意事项与最佳实践

     1. 小心误删数据

     使用 CTE 删除重复数据时，务必确保 分组字段（PARTITION BY） 的选择正确，以避免误删不应删除的数据。在执行删除操作之前，可以先通过 SELECT 语句查看结果，确保筛选到的记录符合预期。

     2. 保留原则的选择

     在处理重复数据时，通常有以下几种保留原则：

   • 保留最早的记录：可以使用 ORDER BY id ASC。
   • 保留最新的记录：可以使用 ORDER BY id DESC。
     根据实际需求选择合适的排序方式。

     3. 备份数据

     在对数据表进行删除操作之前，建议备份数据，以防止因错误操作导致数据丢失。可以使用以下命令备份数据：

     CREATE TABLE employees_backup AS SELECT * FROM employees;

     这样可以确保在误删数据的情况下，能够进行恢复。

     4. 使用事务控制

     在执行删除操作时，建议使用事务来控制数据的操作，确保在出错时可以回滚。

     START TRANSACTION;
     WITH cte AS (
     SELECT
     id,
     name,
     email,
     ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS rn
     FROM
     employees
     )
     DELETE FROM employees
     WHERE id IN (
     SELECT id FROM cte WHERE rn > 1
     );
     COMMIT;

     如果在删除过程中发现问题，可以使用 ROLLBACK 语句回滚到删除之前的状态：

     ROLLBACK;

     删除重复数据的注意事项表

     注意事项	描述
     小心误删数据	确保分组字段正确，避免误删重要数据
     保留原则的选择	根据需求选择保留最早或最新的记录
     备份数据	在删除前备份数据，防止意外删除造成数据丢失
     使用事务控制	使用事务来保证数据安全，在出错时可以回滚

     五、CTE 与其他删除重复数据方法的对比

     除了使用 CTE 以外，MySQL 中还有其他一些删除重复数据的方法，例如使用 子查询 或 JOIN。下面对比这些方法的优缺点。	方法	优点	缺点
     CTE	语义清晰，易于维护，适用于复杂逻辑	MySQL 8.0 以上版本支持
     子查询	简单直接，适合小规模数据	对大数据集性能可能较差
     JOIN	适用于复杂的关联删除	语法复杂，容易出错

     > ? 小结：CTE 是一种清晰易维护的方法，尤其适用于需要多步操作的场景；而对于简单的数据集，可以选择子查询来删除重复数据。

     六、总结

     使用 CTE 删除重复数据 是一种非常有效且灵活的方式，特别是当数据表中存在复杂重复时，通过 CTE 可以方便地分组、编号、筛选需要保留和删除的数据。在使用 CTE 时，需要特别注意 分组字段的选择、数据的备份和事务的使用，以确保数据的安全性和准确性。
     > ? 总结：CTE 提供了一种简单而强大的方式来处理重复数据，通过合理的行号分配和筛选，可以高效地完成数据的去重。结合备份和事务控制等措施，可以进一步提高数据操作的安全性和可靠性。
     希望本文能帮助您深入理解和掌握如何使用 CTE 来删除 MySQL 中的重复数据，为您的数据管理提供强有力的支持。