MySQL - 删除重复记录

什么是重复记录？

当表中的多行在某个或多个关键业务列（例如，相同的电子邮件地址、相同的产品 SKU）中包含相同的值时，就会出现重复记录（duplicate records）。数据冗余（Data redundancy）可能源于应用程序错误、数据导入错误或缺少适当的数据库约束（database constraints）。删除重复项对于数据完整性（data integrity）、准确的报告和最优的数据库性能至关重要。

示例设置

我们来创建一个 contacts 表，其中可能存在重复条目。请注意，id 是唯一的 PRIMARY KEY（主键），但业务数据（first_name、last_name、email）可以重复。

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 插入记录，包括 'john.doe@example.com' 的重复项
INSERT INTO contacts (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),      -- 保留此项
('Jane', 'Smith', 'jane.smith@example.com'),
('John', 'Doe', 'john.doe@example.com'),      -- 重复项
('Peter', 'Jones', 'peter.jones@example.com'),
('John', 'Doe', 'john.doe@example.com');      -- 重复项

步骤 1：识别重复记录

在删除任何内容之前，您必须首先识别哪些记录是重复的。GROUP BY 子句与 COUNT() 函数结合使用非常适合此目的。我们根据定义重复项的列进行分组，并计算出现次数。

SELECT
    email, -- 定义重复项的列
    COUNT(email) AS occurrences
FROM
    contacts
GROUP BY
    email
HAVING
    COUNT(email) > 1;

此查询将显示哪些电子邮件出现了多次。

email	occurrences
john.doe@example.com	3

步骤 2：删除重复记录

一旦识别出重复项，您就可以删除它们。目标是保留一个“原始”记录并删除其余记录。以下是两种常用方法。

方法 A：使用带有 ROW_NUMBER() 的 CTE（推荐）

这种现代方法（在 MySQL 8.0+ 中可用）在公共表表达式（Common Table Expression，CTE）中使用了窗口函数（window function）ROW_NUMBER()。它清晰、可读且功能强大。我们根据定义重复项的列对数据进行分区（partition），并为分区中的每行分配一个序列号。任何数字大于 1 的行都被视为重复项。

WITH NumberedContacts AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as row_num
    FROM
        contacts
)
DELETE FROM contacts
WHERE id IN (
    SELECT id FROM NumberedContacts WHERE row_num > 1
);

解释：

WITH NumberedContacts AS (...)：定义一个临时、命名的结果集（CTE）。
ROW_NUMBER() OVER (...)：对于每个 email 组（PARTITION BY email），它从 1 开始为行编号。我们使用 ORDER BY id 来确保 id 值最低的行被视为原始行（获得 row_num = 1）。
DELETE ... WHERE id IN (...)：主 DELETE 语句随后删除所有其 id 对应于 row_num 大于 1 的行。

运行此命令后，将删除两行。您可以通过 SELECT * FROM contacts; 进行验证。

方法 B：使用 DELETE 与自连接（经典方法）

此方法适用于较旧的 MySQL 版本，它使用 DELETE 语句与同一表上的 INNER JOIN。JOIN 条件查找具有相同 email 但不同 id 的行对。WHERE 子句确保我们删除 id 值较高的行。

-- 注意：请在原始表中运行此操作，而不是在使用方法 A 之后。
DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
    t1.id > t2.id AND
    t1.email = t2.email;

此语句简洁，但对于复杂的重复定义（多列）而言，可能不如 CTE 方法直观。

步骤 3：防止未来重复

最佳策略是在数据库层面防止重复。清理数据后，向不应有重复项的列添加 UNIQUE 约束。

-- 如果仍然存在重复项，此操作将失败。
ALTER TABLE contacts
ADD CONSTRAINT uq_email UNIQUE (email);

有了此约束，任何未来尝试创建重复电子邮件的 INSERT 或 UPDATE 操作都将被数据库拒绝并报错，从而确保了数据完整性。

安全第一：使用事务和备份

执行批量删除是高风险操作。请务必遵循以下安全协议：

1.  **备份：** 在运行任何 `DELETE` 语句之前，创建表的备份。
    `CREATE TABLE contacts_backup LIKE contacts;`
    `INSERT INTO contacts_backup SELECT * FROM contacts;`

2.  **使用事务：** 将您的 `DELETE` 语句包裹在事务（transaction）中。这允许您预览更改并在出现问题时进行 `ROLLBACK`（回滚）。
    `START TRANSACTION;`
    `-- 在此处放置您的 DELETE 语句...`
    `-- 使用 SELECT 语句验证结果。`
    `SELECT * FROM contacts;`
    `-- 如果正确，提交更改。`
    `COMMIT;`
    `-- 如果不正确，撤销更改。`
    `-- ROLLBACK;`

客户端程序示例已移除，因为此任务主要是一个管理任务，最好直接通过 SQL 执行以确保安全和控制。现代应用程序通常在数据输入时强制执行唯一性。