MySQL 删除重复数据并保留最小或最大 ID 的优雅方案 (实操 SQL)

在日常数据维护或由于业务代码未加唯一性约束时，数据库表中经常会出现内容完全重复或部分关键字段（如邮箱、手机号、身份证号）重复的脏数据。

我们通常的诉求是：找出这些重复的记录，将多余的副本全部删除，但只保留其中一条（通常是保留 id 最小的那条，或者 id 最大的最新记录）。

在 MySQL 中，如果直接写 DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY ...)，会直接遭遇经典的 MySQL 报错：

ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause

本文将带您避开这个陷阱，给出 3 种在 MySQL 中优雅删除重复数据的终极解法。

一、准备测试数据与场景复现

假设我们有一张用户联系表 contacts，其中 first_name 和 email 出现了多条重复录入：

DROP TABLE IF EXISTS contacts;

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

INSERT INTO contacts (first_name, email) VALUES
('Alice', '[email protected]'),   -- id: 1 (应保留)
('Bob',   '[email protected]'),     -- id: 2 (唯一)
('Alice', '[email protected]'),   -- id: 3 (重复，需删除)
('Charlie', '[email protected]'),  -- id: 4 (唯一)
('Alice', '[email protected]'),   -- id: 5 (重复，需删除)
('David', '[email protected]'),   -- id: 6 (唯一)
('David', '[email protected]');   -- id: 7 (重复，需删除)

查询当前数据表：

+----+------------+---------------------+
| id | first_name | email               |
+----+------------+---------------------+
|  1 | Alice      | [email protected]   |
|  2 | Bob        | [email protected]     |
|  3 | Alice      | [email protected]   |
|  4 | Charlie    | [email protected]    |
|  5 | Alice      | [email protected]   |
|  6 | David      | [email protected]   |
|  7 | David      | [email protected]   |
+----+------------+---------------------+

二、方案一：使用自连接 (Self Join) 一键删除（最推荐）

利用 MySQL 支持的多表 DELETE 语法，将同一张表进行自连接对比。

1. 保留最小 ID（保留最早插入的数据）：

DELETE c1
FROM contacts c1
INNER JOIN contacts c2 
    ON c1.email = c2.email          -- 相同业务字段重复
   AND c1.id > c2.id;               -- 只删除 id 较大的行

原理拆解：

当 c1.email = c2.email 时：
- c1 的 id 为 3，c2 的 id 为 1，满足 c1.id > c2.id，因此 c1 (id=3) 将被标记删除；
- c1 的 id 为 5，c2 的 id 为 1，满足 c1.id > c2.id，因此 c1 (id=5) 将被标记删除；
- 而 c1 的 id 为 1 时，找不到任何一条比它 id 更小的同 email 记录，因此 id=1 绝不会被删除！

2. 若要保留最大 ID（保留最新插入的数据）：

只需将比较符号反转即可：

DELETE c1
FROM contacts c1
INNER JOIN contacts c2 
    ON c1.email = c2.email 
   AND c1.id < c2.id;               -- 只删除 id 较小的行，保留最大 id

三、方案二：使用子查询并套一层临时表（突破 1093 报错）

如果你习惯使用 NOT IN (SELECT MIN(id) ...) 的思路，为了避开 MySQL 的 ERROR 1093，必须在子查询外面再套一层中间临时别名表：

DELETE FROM contacts 
WHERE id NOT IN (
    -- 在外层包裹一层 SELECT * FROM (...) AS tmp
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id
        FROM contacts
        GROUP BY email
    ) AS tmp
);

[!NOTE] MySQL 遇到外层嵌套的派生表（Derived Table）时，会先将其物化（Materialize）到内存临时表中，从而解除对目标表的加锁锁定限制。

四、方案三：针对超千万级生产大表的“换表大法”

对于上千万行数据的大型生产表，直接执行大面积的 DELETE 语句会导致：

全局行锁或间隙锁等待，引发业务接口阻塞超时；
产生海量 Undo/Redo Log；
删除后表空间碎片过多，物理磁盘文件不会自动缩小。

生产级安全换表流程：

-- 1. 创建一张与原表结构完全相同的新表
CREATE TABLE contacts_new LIKE contacts;

-- 2. 将去重后的干净数据直接按批过滤导入新表
INSERT INTO contacts_new
SELECT * FROM contacts
WHERE id IN (
    SELECT MIN(id) FROM contacts GROUP BY email
);

-- 3. 原子重命名交换数据表 (业务无感切换，耗时仅数毫秒)
RENAME TABLE contacts TO contacts_old, contacts_new TO contacts;

-- 4. 业务平稳后，在低峰期删除历史旧表释放磁盘空间
DROP TABLE contacts_old;

五、防患于未然：给关键字段添加唯一索引

去重完成后，为了防止日后再次产生脏数据，应及时为重复字段建立唯一约束（Unique Index）：

ALTER TABLE contacts ADD UNIQUE INDEX uk_email (email);

有了唯一索引的保护，未来任何重复数据的插入都会被数据库直接拦截拒收，从源头上保障数据完整性。

一、 准备测试数据与场景复现

二、 方案一：使用自连接 (Self Join) 一键删除（最推荐）