MySQL 使用 CTE 删除重复数据

博主：小蓝
发布时间：2024 年 10 月 26 日
19 次浏览
暂无评论
5069字数
分类： Linux 技术杂烩

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

# MySQL 使用 CTE 删除重复数据

## 一、什么是 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 找出重复数据，并确定每组重复数据中需要保留的记录。可以使用 **窗口函数** 来实现这一目标。

```sql
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`** 的记录即可。

```sql
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 删除重复数据的工作流程

```mermaid
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. 备份数据

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

```sql
CREATE TABLE employees_backup AS SELECT * FROM employees;
```

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

### 4. 使用事务控制

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

```sql
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`** 语句回滚到删除之前的状态：

```sql
ROLLBACK;
```

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

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

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

除了使用 CTE 以外，MySQL 中还有其他一些删除重复数据的方法，例如使用 **子查询** 或 **JOIN**。下面对比这些方法的优缺点。

| **方法** | **优点**                     | **缺点**         |
| -------------- | ---------------------------------- | ---------------------- |
| CTE            | 语义清晰，易于维护，适用于复杂逻辑 | MySQL 8.0 以上版本支持 |
| 子查询         | 简单直接，适合小规模数据           | 对大数据集性能可能较差 |
| JOIN           | 适用于复杂的关联删除               | 语法复杂，容易出错     |

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

## 六、总结

**使用 CTE 删除重复数据** 是一种非常有效且灵活的方式，特别是当数据表中存在复杂重复时，通过 CTE 可以方便地分组、编号、筛选需要保留和删除的数据。在使用 CTE 时，需要特别注意 **分组字段的选择、数据的备份和事务的使用**，以确保数据的安全性和准确性。

> 🌟 **总结**：CTE 提供了一种简单而强大的方式来处理重复数据，通过合理的行号分配和筛选，可以高效地完成数据的去重。结合备份和事务控制等措施，可以进一步提高数据操作的安全性和可靠性。

希望本文能帮助您深入理解和掌握如何使用 **CTE** 来删除 **MySQL** 中的重复数据，为您的数据管理提供强有力的支持。

蓝易云是一家专注于香港及国内数据中心服务的提供商，提供高质量的服务器租用和云计算服务、包括免备案香港服务器、香港CN2、美国服务器、海外高防服务器、国内高防服务器、香港VPS等。致力于为用户提供稳定,快速的网络连接和优质的客户体验。

最后修改：2024 年 10 月 26 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

MySQL 使用 CTE 删除重复数据

小蓝 • 2024 年 10 月 26 日

# MySQL 使用 CTE 删除重复数据

## 一、什么是 CTE？

在处理**重复数据**时，CTE 非常有用。我们可以使用 CTE 来定位需要删除的重复数据，并进一步进行删除操作，从而保证数据的唯一性和一致性。

> 💡 **小结**：CTE 是一种临时命名查询结果集，特别适合用于复杂查询和操作的简化，例如删除重复数据等任务。

## 二、删除重复数据的场景

### 删除重复数据的原则

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

下面我们来看具体如何使用 MySQL 的 CTE 删除重复数据。假设我们有一个名为 **`employees`** 的表，其中包含一些重复数据。表的结构如下：

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

### 1. 使用 CTE 找出重复数据

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

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

### 代码解释

上面的查询结果会为每组重复记录生成一个 **行号（rn）**，如下所示：

### 2. 删除重复数据

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

### 代码解释

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

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

## 四、注意事项与最佳实践

### 1. 小心误删数据

### 2. 保留原则的选择

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

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

### 3. 备份数据

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

```sql
CREATE TABLE employees_backup AS SELECT * FROM employees;
```

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

### 4. 使用事务控制

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

```sql
START TRANSACTION;

COMMIT;
```

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

```sql
ROLLBACK;
```

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

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

除了使用 CTE 以外，MySQL 中还有其他一些删除重复数据的方法，例如使用 **子查询** 或 **JOIN**。下面对比这些方法的优缺点。

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

## 六、总结

希望本文能帮助您深入理解和掌握如何使用 **CTE** 来删除 **MySQL** 中的重复数据，为您的数据管理提供强有力的支持。

MySQL 使用 CTE 删除重复数据

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

微信视频号视频下载工具WeChatVideoDownloader

Win11系统优化工具-Windows11一键设置

Windows微软常用运行库合集一键安装

【电脑软件】Google翻译修复工具

Windows版Kangle安装教程及安装包

云服务器哪家好便宜

针对在Centos/Linux安装Apache过程中出现的常见问题集锦

Docker部署WordPress的详细教程

Linux od命令

使用s3cmd 2.x 与 Cyberduck 管理在 DigitalOcean Spaces 对象存储中的数据

MySQL 使用 CTE 删除重复数据

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

MySQL 使用 CTE 删除重复数据

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款