第 12.5 课 · 阅读时间：约 7 分钟
重复行几乎出现在每个真实的数据库中：导入执行了两次、表单提交了两次、唯一约束一直没有加上。“查找重复”和“删除重复但保留一条”是经典的 SQL 面试题。在本课中，你将用 GROUP BY ... HAVING 查找重复值，用窗口函数列出每一份副本，在 MySQL 中安全地删除多余的副本，并用约束防止新的重复。
SQL 查找和删除重复行
首先要确定什么算重复。行很少会在所有列上都完全相同：它们通常有不同的 id 和创建时间。重复指的是相同的业务键：同一个电子邮件、同样的姓名和出生日期、同一个订单号。下面的所有查询都按这个键分组。
示例使用 MySQL 8.0 语法，并会创建自己的表，因此请在练习场中选择不含示例数据的数据库来运行。
示例数据
CREATE TABLE contact (
id INT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
full_name VARCHAR(50) NOT NULL
);
INSERT INTO contact VALUES
(1, 'anna@mail.com', 'Anna Lee'),
(2, 'bob@mail.com', 'Bob Stone'),
(3, 'anna@mail.com', 'Anna Lee'),
(4, 'carl@mail.com', 'Carl Diaz'),
(5, 'bob@mail.com', 'Bob Stone'),
(6, 'Anna@Mail.com', 'Anna Lee'),
(7, 'dina@mail.com', 'Dina Park');
第 1 步：查找重复值
按键分组，保留行数大于 1 的分组：
SELECT email, COUNT(*) AS copies
FROM contact
GROUP BY email
HAVING COUNT(*) > 1;
结果：anna@mail.com 有 3 份，bob@mail.com 有 2 份。
注意，Anna@Mail.com 被和 anna@mail.com 计在了一起。MySQL 默认的排序规则（collation）在比较字符串时不区分大小写，所以它们是同一个值。PostgreSQL 区分大小写，只会找到 Anna 的 2 份副本；要得到同样的结果，请按 LOWER(email) 分组。如果键由多列组成，就把它们全部列出：GROUP BY first_name, last_name。
第 2 步：查看每一行重复数据
GROUP BY 每个值只显示一行。要查看重复的行本身及其 id，可以用窗口函数统计副本数，并保留副本数大于 1 的行：
WITH counted AS (
SELECT id, email, full_name,
COUNT(*) OVER (PARTITION BY email) AS copies
FROM contact
)
SELECT id, email, full_name, copies
FROM counted
WHERE copies > 1
ORDER BY email, id;
结果：5 行：Anna 的 id 为 1、3、6，Bob 的 id 为 2、5。
在删除任何数据之前，先看看这些行：这些副本是否真的表示同一个对象？应该保留哪一行？
第 3 步：标记要删除的副本
用 ROW_NUMBER() 为每个分组内的行编号。编号为 1 的行是要保留的行；这里保留最早的那一行，即 id 最小的行：
SELECT id, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contact
ORDER BY email, id;
结果：id 1、2、4、7 得到 rn = 1；id 3、5、6 得到 2 或 3，它们就是多余的副本。
窗口内的 ORDER BY 决定保留哪一行：ORDER BY id DESC 保留最新的行，ORDER BY updated_at DESC 保留最近更新的行。
第 4 步：删除多余的副本
在 MySQL 中，DELETE 里的子查询不能读取正在删除数据的那张表（错误 1093）。把子查询包在派生表中，MySQL 会先将其物化：
DELETE FROM contact
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contact
) AS numbered
WHERE rn > 1
);
结果：删除了 3 行；剩下 id 1、2、4 和 7，每个电子邮件一行。
另一种较老的、MySQL 专有的写法使用自连接：如果存在电子邮件相同且 id 更小的行，就删除当前行：
DELETE c
FROM contact c
JOIN contact k ON k.email = c.email AND k.id < c.id;
结果：删除了同样的 3 行。
在 PostgreSQL 中，常用写法是 DELETE FROM contact c USING contact k WHERE k.email = c.email AND k.id < c.id;；在 SQL Server 中，可以直接从带 ROW_NUMBER() 的 CTE 中删除。
在执行 DELETE 之前先运行第 3 步的 SELECT，比较受影响的行数；在生产环境中，请在事务中执行删除。
第 5 步：防止新的重复
清理只能维持到下一次导入。唯一约束会让数据库拒绝重复数据：
ALTER TABLE contact ADD CONSTRAINT uq_contact_email UNIQUE (email);
请在清理之后再添加它：只要还有重复数据，创建约束就会失败。之后的导入可以使用 INSERT IGNORE 或 INSERT ... ON DUPLICATE KEY UPDATE（第 8.1 课）来跳过或更新已有的行。
练习
要点：
- 重复指的是业务键重复，而不一定是完全相同的行；请按这个键分组。
GROUP BY key HAVING COUNT(*) > 1查找重复值；COUNT(*) OVER (PARTITION BY key)显示每一行重复数据。ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...)标记要保留的行（rn = 1）和要删除的副本。- 在 MySQL 中，通过派生表或自连接来删除，以避免错误 1093。
- 大小写和排序规则决定了哪些值相等；最后加上
UNIQUE约束。
常见问题
如何在 SQL 中查找重复行？
按本应唯一的列分组并过滤分组：SELECT email, COUNT(*) FROM contact GROUP BY email HAVING COUNT(*) > 1;。要查看完整的行，请使用 COUNT(*) OVER (PARTITION BY email)，并保留该值大于 1 的行。
如何删除重复数据但保留一行？
用 ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) 为每个分组的行编号，然后删除编号大于 1 的行。在 MySQL 中，把编号放进 DELETE 内的派生表，或者使用自连接：DELETE c FROM t c JOIN t k ON k.key = c.key AND k.id < c.id。
为什么删除重复数据时 MySQL 报错 1093？
MySQL 不允许 DELETE 或 UPDATE 中的子查询直接读取同一张表。把子查询包在派生表中（SELECT id FROM (...) AS d），MySQL 会先将其物化，错误就会消失。
如何按多列查找重复？
把这些列全部写进 GROUP BY 和 PARTITION BY：GROUP BY first_name, last_name HAVING COUNT(*) > 1。只有当列出的所有列都相同时，这些行才算重复。