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

admin管理员组

文章数量:1794759

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

一段时间后，大多数应用程序可能由于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'),
('Bob', 'bob@example'),
('Alice', 'alice@example'),  -- 重复数据
('Charlie', 'charlie@example'),
('Bob', 'bob@example');      -- 重复数据

巧用 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   |
|  2 | Bob     | bob@example     |
|  3 | Alice   | alice@example   |
|  4 | Charlie | charlie@example |
|  5 | Bob     | bob@example     |
+----+---------+---------------------+
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   |
|  4 | Charlie | charlie@example |
|  5 | Bob     | bob@example     |
+----+---------+---------------------+
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中这是有效的语法，但在其他一些数据库系统中可能需要稍作调整。

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

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

这种方法可以有效地清理数据库中的重复用户记录，同时保留每组重复记录中最新的（假设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(); 来调用并执行此存储过程。

总结

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

本文标签： 巧用 CTE 公共表达式删除 MySQL 重复数据