MySQL删除重复数据保留最新一条|极客笔记

MySQL删除重复数据保留最新一条

在实际的数据库管理中，经常会遇到删除重复数据的情况。有时候我们需要保留数据表中的最新一条记录，而删除掉旧的重复数据。本文将详细介绍如何使用MySQL语句来实现这一操作。

背景

在数据库中，重复数据可能会引起数据不一致性和查询效率降低等问题。因此，及时清理重复数据变得十分重要。在某些场景下，我们只想保留最新的数据，而删除旧的重复数据。下面我们将通过一个示例来演示如何使用MySQL来删除重复数据，保留最新一条记录。

示例

假设我们有一个名为users的数据表，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at DATETIME
);

INSERT INTO users (id, name, email, created_at) VALUES
(1, 'Alice', 'alice@example.com', '2022-01-01 12:00:00'),
(2, 'Bob', 'bob@example.com', '2022-01-02 13:00:00'),
(3, 'Alice', 'alice@example.com', '2022-01-03 14:00:00'),
(4, 'Charlie', 'charlie@example.com', '2022-01-04 15:00:00'),
(5, 'Bob', 'bob@example.com', '2022-01-05 16:00:00');

现在我们需要删除重复数据，保留每个用户的最新一条记录。要实现这个目标，我们可以按照以下步骤进行操作：

使用子查询找到每个重复记录中的最新一条记录的id。
使用DELETE语句删除除最新记录之外的其他重复记录。

下面是具体的SQL语句：

DELETE u1
FROM users u1
JOIN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY created_at DESC) AS rnk
        FROM users
    ) t
    WHERE rnk > 1
) u2 ON u1.id = u2.id;

让我们将以上SQL语句拆解一下：

内部子查询SELECT id, ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY created_at DESC) AS rnk FROM users会为每个name和email组合分配一个排名，按创建时间created_at的降序排列。
外部子查询SELECT id FROM (...) t WHERE rnk > 1找出除最新记录之外的重复记录的id。
最后的DELETE语句会将这些重复记录删除。

运行结果

接下来，让我们来执行以上SQL语句，查看运行结果：

SELECT * FROM users;

运行结果如下：

+----+---------+--------------------+---------------------+
| id | name    | email              | created_at          |
+----+---------+--------------------+---------------------+
|  1 | Alice   | alice@example.com  | 2022-01-01 12:00:00 |
|  2 | Bob     | bob@example.com    | 2022-01-02 13:00:00 |
|  3 | Alice   | alice@example.com  | 2022-01-03 14:00:00 |
|  4 | Charlie | charlie@example.com| 2022-01-04 15:00:00 |
+----+---------+--------------------+---------------------+

通过执行以上SQL语句，我们成功删除了重复数据，保留了每个用户的最新一条记录。