MySQL 删除重复数据：使用存储过程高效处理

使用存储过程删除表重复数据

概述

本文件提供指导，说明如何使用两个 MySQL 存储过程 DeleteDuplicatesSingleTable 和 BatchDeleteDuplicates 来删除数据库表中的重复记录。这两个存储过程设计用于高效处理单个或多个表，包含错误处理和分批处理功能，以最小化性能影响。

前提条件

MySQL 版本：5.7 或更高版本
用户权限：用户需具备目标数据库的 CREATE、ALTER、DELETE、SELECT 和 EXECUTE 权限。
表结构：表必须包含一个 id 列作为主键或唯一标识符。
备份：在执行删除操作前，始终备份目标数据表。

存储过程

1. `DeleteDuplicatesSingleTable`


CREATE  PROCEDURE `DeleteDuplicatesSingleTable`(
    IN current_table VARCHAR(100),
    IN unique_keys VARCHAR(255)
)
BEGIN
    DECLARE min_id BIGINT;
    DECLARE max_id BIGINT;
    DECLARE current_min BIGINT;
    DECLARE current_max BIGINT;
    DECLARE batch_size INT DEFAULT 10000;
    DECLARE group_by_clause TEXT;
    DECLARE exit_handler BOOLEAN DEFAULT FALSE;
    DECLARE err_msg TEXT;

    -- 错误处理
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET exit_handler = TRUE;
        GET DIAGNOSTICS CONDITION 1 err_msg = MESSAGE_TEXT;
    END;

    -- 准备 GROUP BY 子句（例如 'col1,col2' -> 'col1, col2'）
    SET group_by_clause = REPLACE(unique_keys, ',', ', ');

    -- 开启事务
    START TRANSACTION;

    -- 获取 min_id 和 max_id
    SET @sql_min = CONCAT('SELECT MIN(id) INTO @min_val FROM `', current_table, '`');
    PREPARE stmt FROM @sql_min;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET min_id = IFNULL(@min_val, 0);

    SET @sql_max = CONCAT('SELECT MAX(id) INTO @max_val FROM `', current_table, '`');
    PREPARE stmt FROM @sql_max;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET max_id = IFNULL(@max_val, 0);

    IF min_id = 0 OR max_id = 0 OR min_id > max_id THEN
        COMMIT;
        SELECT CONCAT('表 ', current_table, ' 为空或无数据，跳过。') AS message;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '表为空，跳过'; -- 直接返回
    END IF;

    -- 创建临时表（保留最小 ID）
    SET @create_temp = CONCAT(
        'CREATE TEMPORARY TABLE temp_duplicates AS ',
        'SELECT MIN(id) AS id ',
        'FROM `', current_table, '` ',
        'GROUP BY ', group_by_clause
    );
    PREPARE stmt FROM @create_temp;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    IF exit_handler THEN
        ROLLBACK;
        SELECT CONCAT('为表 ', current_table, ' 创建临时表失败：', err_msg) AS message;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '创建临时表失败';
    END IF;

    -- 在临时表创建索引
    SET @create_index = 'CREATE INDEX idx_temp_id ON temp_duplicates (id)';
    PREPARE stmt FROM @create_index;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 分批删除
    SET current_min = min_id;
    WHILE current_min <= max_id DO
        SET current_max = LEAST(current_min + batch_size - 1, max_id);

        SET @delete_batch = CONCAT(
            'DELETE FROM `', current_table, '` ',
            'WHERE id BETWEEN ', current_min, ' AND ', current_max, ' ',
            'AND id NOT IN (SELECT id FROM temp_duplicates)'
        );
        PREPARE stmt FROM @delete_batch;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF exit_handler THEN
            ROLLBACK;
            SELECT CONCAT('表 ', current_table, ' 批量删除失败：', err_msg) AS message;
            DROP TEMPORARY TABLE IF EXISTS temp_duplicates;
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '批量删除失败';
        END IF;

        SET current_min = current_max + 1;
    END WHILE;

    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_duplicates;

    -- 提交事务
    COMMIT;

    SELECT CONCAT('表 ', current_table, ' 处理成功') AS message;
END

此存储过程根据指定的唯一键从单个表中删除重复记录，保留 id 最小的记录。

参数

current_table (VARCHAR(100))：要处理的表名。
unique_keys (VARCHAR(255))：定义唯一性的列名列表，用逗号分隔（例如：’column1,column2’）。

功能

获取表中 id 的最小值和最大值。
创建临时表（temp_duplicates），存储每个唯一键组合的最小 id。
分批删除重复记录（默认批次大小：10,000），删除 id 不在临时表中的记录。
使用事务确保数据完整性，发生错误时回滚。
处理完成后删除临时表。

使用示例

从名为 user_data 的表中删除重复记录，其中 email 和 phone 定义唯一性：


CALL DeleteDuplicatesSingleTable('user_data', 'email,phone');

输出

成功：表 user_data 处理成功
错误：描述性错误信息（例如：为表 user_data 创建临时表失败：...）
空表：表 user_data 为空或无数据，跳过。

2. `BatchDeleteDuplicates`


CREATE PROCEDURE `BatchDeleteDuplicates`(
    IN table_prefix VARCHAR(100),
    IN unique_keys VARCHAR(255)
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_tables INT DEFAULT 2;
    DECLARE current_table VARCHAR(100);
    DECLARE exit_handler BOOLEAN DEFAULT FALSE;
    DECLARE err_msg TEXT;

    -- 错误处理
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET exit_handler = TRUE;
        GET DIAGNOSTICS CONDITION 1 err_msg = MESSAGE_TEXT;
        SELECT CONCAT('处理表 ', current_table, ' 时出错：', err_msg) AS message;
    END;

    -- 循环处理 table_prefix1 到 table_prefix32
    WHILE i <= max_tables DO
        -- 如果 table_prefix 不以数字结尾，则只处理单表
        SET current_table = CONCAT(table_prefix, i);

        SET exit_handler = FALSE;

        -- 直接调用子过程处理单表
        CALL DeleteDuplicatesSingleTable(current_table, unique_keys);

        SET i = i + 1;
    END WHILE;

    SELECT '所有表处理完成' AS final_message;
END

此存储过程处理多个表（表名前缀相同，例如 table_prefix1, table_prefix2, …, 最多到 table_prefix32）或单个表（如果前缀不以数字结尾）。

参数

table_prefix (VARCHAR(100))：要处理的表名或表名前缀。
unique_keys (VARCHAR(255))：定义唯一性的列名列表，用逗号分隔。

功能

循环处理表名 table_prefix1 到 table_prefix32（最多 32 个表）。
如果 table_prefix 不以数字结尾，则仅处理单个名为 table_prefix 的表。
为每个表调用 DeleteDuplicatesSingleTable。
包含错误处理，报告每个表的处理问题。

使用示例

单表：处理名为 customer 的单个表：


CALL BatchDeleteDuplicates('customer', 'email,phone');

多表：处理表 log1, log2, …, log32：

CALL BatchDeleteDuplicates('log', 'user_id,action');

输出

成功：所有表处理完成
每表错误：处理表 log1 时出错：...
完成：所有表处理完成

实施步骤

部署存储过程：

将提供的两个存储过程的 SQL 代码复制到 MySQL 客户端（例如 MySQL Workbench、Navicat）。
执行 SQL 代码，在目标数据库中创建存储过程。

验证表结构：

确保每个表包含 id 列以及 unique_keys 中指定的列。
建议为 unique_keys 中的列创建索引以提高性能（可选但推荐）。

备份数据库：

备份目标数据表

执行存储过程：

根据需求选择调用 DeleteDuplicatesSingleTable（单表）或 BatchDeleteDuplicates（多表或单表）。
确保正确指定表名和唯一键。
示例：

检查结果：

查看存储过程的输出消息，确认处理是否成功。
如果发生错误，检查错误信息并根据需要修复表结构或数据问题。
验证表中是否已删除重复记录：如果无结果返回，表示重复记录已删除。

性能优化（可选）：

调整 DeleteDuplicatesSingleTable 中的 batch_size（默认 10,000），根据表大小和服务器性能优化批次大小。
确保表和 unique_keys 列已正确索引，以减少查询和删除时间。

注意事项

数据安全：删除操作不可逆，务必在操作前备份数据。
性能影响：对于大型表，分批处理可减少锁表时间，但仍可能影响数据库性能。建议在低峰时段运行。
错误处理：存储过程包含错误处理机制，会在发生错误时回滚事务并返回错误信息。实施人员需检查输出消息。
表名限制：表名和列名需符合 MySQL 命名规范，避免使用保留字。
最大表数：BatchDeleteDuplicates 默认处理最多 32 个表，可根据需要修改 max_tables 参数。

故障排除

错误：表为空或无数据：
确认表名是否正确，表中是否有数据。
错误：创建临时表失败：
检查用户权限是否足够，数据库是否有足够空间。
错误：批量删除失败：
检查 id 列是否存在，unique_keys 中的列是否有效。
查看详细错误信息（存储在 err_msg 中）。
性能问题：
确保 unique_keys 列已建立索引。
减小 batch_size 以降低每次删除的资源消耗。

示例场景

假设数据库中有表 order_log1 到 order_log10，每表包含 user_id 和 order_date 列，需删除重复记录（以 user_id,order_date 作为唯一键）：

CALL BatchDeleteDuplicates('order_log', 'user_id,order_date');

此命令将依次处理 order_log1 到 order_log10，删除重复记录，并保留每组唯一键的最小 id 记录。

维护建议

定期检查表中是否产生新的重复记录，必要时重复运行存储过程。
在表结构或数据量发生重大变化时，重新评估索引和批次大小设置。
记录每次运行的输出消息，以便后续审计和问题排查。

上篇LCR170-优化逆序对计数算法：从暴力解法到归并排序

使用存储过程删除表重复数据

概述

前提条件

存储过程

1. DeleteDuplicatesSingleTable

参数

功能

使用示例

输出

2. BatchDeleteDuplicates

参数

功能

使用示例

输出

实施步骤

注意事项

故障排除

示例场景

维护建议

1. `DeleteDuplicatesSingleTable`

2. `BatchDeleteDuplicates`