巧用 CTE 公共表达式删除 MySQL 重复数据

原创

贺春旸的技术博客

修改于 2024-10-12 08:47:38

5600

一段时间后，大多数应用程序可能由于bug而出现重复行，这不仅影响用户体验，还增加了存储需求并降低数据库性能。可以通过一个 SQL 查询来完成整个清理过程，从而有效解决这一问题。

使用范例

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 插入数据，包括重复数据
INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Alice', 'alice@example.com'),  -- 重复数据
('Charlie', 'charlie@example.com'),
('Bob', 'bob@example.com');      -- 重复数据

巧用 CTE 公共表达式删除 MySQL 重复数据

WITH duplicates AS (
  SELECT id, ROW_NUMBER() OVER(
    PARTITION BY name, email
    ORDER BY id DESC
  ) AS rownum
  FROM users
)
DELETE users 
FROM users 
JOIN duplicates USING(id) 
WHERE duplicates.rownum > 1;

输出结果：

mysql> select * from users;
+----+---------+---------------------+
| id | name    | email               |
+----+---------+---------------------+
|  1 | Alice   | alice@example.com   |
|  2 | Bob     | bob@example.com     |
|  3 | Alice   | alice@example.com   |
|  4 | Charlie | charlie@example.com |
|  5 | Bob     | bob@example.com     |
+----+---------+---------------------+
5 rows in set (0.00 sec)

mysql> WITH duplicates AS (
    ->   SELECT id, ROW_NUMBER() OVER(
    ->     PARTITION BY name, email
    ->     ORDER BY id DESC
    ->   ) AS rownum
    ->   FROM users
    -> )
    -> DELETE users 
    -> FROM users 
    -> JOIN duplicates USING(id) 
    -> WHERE duplicates.rownum > 1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------------------+
| id | name    | email               |
+----+---------+---------------------+
|  3 | Alice   | alice@example.com   |
|  4 | Charlie | charlie@example.com |
|  5 | Bob     | bob@example.com     |
+----+---------+---------------------+
3 rows in set (0.00 sec)

解读：

这个SQL语句可以分为两个主要部分

1）WITH子句（公共表表达式，CTE）：

这部分创建了一个名为duplicates的临时结果集。它对users表进行操作：

使用PARTITION BY name, email对名字和邮箱相同的记录进行分组。
在每个分组内，使用ORDER BY id DESC按id降序排序。
为每条记录分配一个rownum，这个数字在每个分组内从1开始计数。

2）DELETE语句：

这部分执行实际的删除操作：

它将users表与我们刚刚创建的duplicates结果集进行JOIN。
删除条件是WHERE duplicates.rownum > 1，意味着它会删除每组重复记录中除了第一条（rownum = 1）之外的所有记录。

需要注意的是，这个DELETE语句的语法可能不被所有数据库系统支持。例如，在MySQL中这是有效的语法，但在其他一些数据库系统中可能需要稍作调整。

总的来说，这个查询的目的是：

找出users表中名字和邮箱相同的记录。
对于每组重复记录，保留id最大的那一条（因为是按id降序排序）。
删除其他所有重复记录。

这种方法可以有效地清理数据库中的重复用户记录，同时保留每组重复记录中最新的（假设id越大越新）一条记录。

补充：

当表很大的情况，需要进行分批次删除处理，这样会减缓主库IO，减缓主从复制延迟。

DELIMITER //

CREATE PROCEDURE batch_delete_duplicates()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE sleep_interval INT DEFAULT 1;
    DECLARE last_id INT DEFAULT 0;
    
    -- 创建临时表来存储重复记录的ID
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_duplicates (
        id INT PRIMARY KEY
    );
    
    -- 使用CTE找出所有重复记录，并插入临时表
    INSERT INTO temp_duplicates (id)
    WITH duplicates AS (
        SELECT id, ROW_NUMBER() OVER(
            PARTITION BY name, email
            ORDER BY id DESC
        ) AS rownum
        FROM users
    )
    SELECT id
    FROM duplicates
    WHERE rownum > 1;
    
    -- 开始循环删除
    WHILE NOT done DO
        -- 删除一批重复记录
        DELETE u FROM users u
        INNER JOIN (
            SELECT id 
            FROM temp_duplicates 
            WHERE id > last_id
            ORDER BY id
            LIMIT batch_size
        ) AS td ON u.id = td.id;
        
        -- 更新last_id
        SET last_id = (
            SELECT IFNULL(MAX(id), last_id)
            FROM temp_duplicates
            WHERE id > last_id
            ORDER BY id
            LIMIT batch_size
        );
        
        -- 从临时表中删除已处理的记录
        DELETE FROM temp_duplicates
        WHERE id <= last_id;
        
        -- 检查是否还有记录需要删除
        IF (SELECT COUNT(*) FROM temp_duplicates) = 0 THEN
            SET done = TRUE;
        ELSE
            -- 休眠指定的秒数
            DO SLEEP(sleep_interval);
        END IF;
    END WHILE;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_duplicates;
END //

DELIMITER ;

CALL batch_delete_duplicates();

这个存储过程 batch_delete_duplicates() 用于批量删除表 users 中的重复数据。它会以每次删除1000行的方式进行，直到所有重复数据被删除完毕，每次删除完成后会休眠1秒。

解读：

1. 声明变量

done: 用于判断是否已完成所有重复记录的删除，初始值为 FALSE。
batch_size: 每次删除的行数，默认值为1000。
sleep_interval: 每次删除操作后休眠的秒数，默认为1秒。
last_id: 用于跟踪上一次删除操作的最大 id，初始值为0。

2. 创建临时表

使用 CREATE TEMPORARY TABLE 创建了一个名为 temp_duplicates 的临时表，用来存储 users 表中重复记录的 id。

3. 查找并插入重复记录

使用 CTE (Common Table Expression) duplicates 找出 users 表中的重复记录。它根据 name 和 email 字段进行分组，并按照 id 倒序排序。
然后，通过 ROW_NUMBER() 为每个分组内的行编号，保留 rownum 值大于1的行，即将重复记录插入 temp_duplicates 表。

4. 循环删除重复记录

使用 WHILE NOT done DO 循环结构，每次循环都删除一批 batch_size（即1000）条重复记录。
- 通过 DELETE u FROM users u 与临时表 temp_duplicates 连接，仅删除 temp_duplicates 中的重复 id。
- 通过 SET last_id 语句更新 last_id 的值，使其指向当前批次删除的最大 id。
- 从 temp_duplicates 表中删除已处理的记录，以避免重复处理。
每次删除后，检查临时表 temp_duplicates 是否还有未处理的记录。如果没有剩余记录，将 done 设置为 TRUE，退出循环。否则，程序会执行 DO SLEEP(sleep_interval) 休眠1秒。

5. 删除临时表

在删除所有重复记录后，最后删除 temp_duplicates 临时表，清理数据库。

6. 调用存储过程

使用 CALL batch_delete_duplicates(); 来调用并执行此存储过程。

总结

这个过程通过批次删除的方式来处理大量重复记录，以减少数据库的锁定时间并避免过高的资源消耗，同时通过休眠操作使得删除过程更加平稳。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

登录后参与评论

0 条评论

热度

巧用 CTE 公共表达式删除 MySQL 重复数据

巧用 CTE 公共表达式删除 MySQL 重复数据

使用范例

巧用 CTE 公共表达式删除 MySQL 重复数据

输出结果：

解读：

这个SQL语句可以分为两个主要部分

1）WITH子句（公共表表达式，CTE）：

2）DELETE语句：

总的来说，这个查询的目的是：

补充：

当表很大的情况，需要进行分批次删除处理，这样会减缓主库IO，减缓主从复制延迟。

解读：

1. 声明变量

2. 创建临时表

3. 查找并插入重复记录

4. 循环删除重复记录

5. 删除临时表

6. 调用存储过程

总结

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐