MySQL 去重删除：如何删除重复数据并保留最新记录

引言

在数据库的日常维护中或者在平时的开发过程中，由于操作失误或设计缺陷，可能会导致数据表中出现重复记录。如果这些数据没有主键约束或唯一索引，数据库不会自动防止重复数据的插入。这种情况不仅影响数据的准确性，还可能影响查询性能。

本文将介绍一种高效的方法来删除 MySQL 表中的重复数据，并保留每组数据中 updated_at 最新的那一条记录。我们以 user_life_targets 这张表为例，详细讲解如何使用 SQL 解决这个问题。

问题描述

假设有如下表 user_life_targets，其结构如下：

CREATE TABLE `user_life_targets` (
  `id` bigint NOT NULL,
  `user_id` bigint NOT NULL DEFAULT '0',
  `life_id` bigint NOT NULL DEFAULT '0',
  `task_id` bigint NOT NULL DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_u_l_t` (`user_id`,`life_id`,`task_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

由于某些原因，该表中可能出现了重复的 user_id, life_id, task_id 组合的数据，我们需要删除这些重复数据，并且 保留 updated_at 最新的那条记录。

解决方案

1. 使用 ROW_NUMBER() 窗口函数进行去重

MySQL 8.0 及以上版本支持 ROW_NUMBER() 窗口函数，我们可以利用它为重复数据编号，并删除 updated_at 不是最新的记录。

SQL 实现：

WITH ranked_targets AS (
    SELECT 
        id,
        user_id,
        life_id,
        task_id,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY user_id, life_id, task_id ORDER BY updated_at DESC) AS rn
    FROM `user_life_targets`
)
DELETE FROM `user_life_targets`
WHERE id IN (
    SELECT id FROM ranked_targets WHERE rn > 1
);

2. 解析 SQL 逻辑

步骤解析

1. 创建临时数据集 ranked_targets
2. ROW_NUMBER() OVER (PARTITION BY user_id, life_id, task_id ORDER BY updated_at DESC)
3. 这个函数会按照 (user_id, life_id, task_id) 进行分组，并且按照 updated_at 降序排序。
4. ROW_NUMBER() 给每组数据分配唯一的编号，最新的数据编号为 1。
5. 删除重复数据
6. 通过 DELETE 语句，删除 ranked_targets 中 rn > 1 的数据，只保留 rn = 1 的记录（即 updated_at 最新的记录）。

适用于 MySQL 5.7 及以下版本的方法

如果你的 MySQL 版本不支持 WITH 语法（即 MySQL 8.0 以下版本），可以使用 DELETE + 子查询的方式删除数据：

DELETE FROM user_life_targets 
WHERE id NOT IN (
    SELECT id FROM (
        SELECT id FROM user_life_targets t1
        WHERE updated_at = (
            SELECT MAX(updated_at) 
            FROM user_life_targets t2
            WHERE t1.user_id = t2.user_id AND t1.life_id = t2.life_id AND t1.task_id = t2.task_id
        )
    ) AS latest_records
);

解释：

• 通过子查询 SELECT MAX(updated_at) 找到相同 (user_id, life_id, task_id) 组合中最新的 updated_at 记录。
• 外层 DELETE 语句删除不在最新记录列表中的数据。
• 由于 MySQL 5.7 及以下不允许在 DELETE 语句中直接使用 JOIN，因此使用子查询方式删除。

性能优化建议

1. 创建合适的索引
2. 确保 (user_id, life_id, task_id, updated_at) 上有索引，提高查询效率。
3. 例如： CREATE INDEX idx_user_life_task ON user_life_targets(user_id, life_id, task_id, updated_at);
4. 避免 IN 过大
5. DELETE FROM ... WHERE id NOT IN (...) 在大数据量下可能会较慢。
6. 如果数据量特别大，可以考虑使用临时表存储要保留的 id，然后使用 DELETE JOIN 方式删除。
7. 备份数据
8. 在执行 DELETE 之前，建议先进行数据备份，防止误删。
9. 备份方式： CREATE TABLE backup_user_life_targets AS SELECT * FROM user_life_targets;

结论

本篇文章介绍了如何在 MySQL 中删除重复数据，并保留 updated_at 最新的一条记录。

方法总结：

• MySQL 8.0 及以上 推荐使用 ROW_NUMBER() 窗口函数。
• MySQL 5.7 及以下 版本可以使用 DELETE + 子查询方式。
• 在执行 DELETE 之前，建议先进行数据备份。

希望本篇文章能帮助你在实际项目中高效管理数据库数据，避免数据冗余导致的问题！