CTE + ROW_NUMBER() 是唯一可靠方案:通过PARTITION BY分组、ORDER BY确定保留行、删除rn>1的重复行,需显式别名、注意NULL处理、添加联合索引并先验证再执行。

CTE + ROW_NUMBER() 是唯一可靠方案
直接用 DELETE 配合 CTE 删除重复行,必须依赖窗口函数生成唯一序号,否则无法区分“保留哪一条”。MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持,但 SQLite 不支持在 DELETE 中直接引用 CTE(需改用临时表或子查询)。
核心逻辑是:对重复字段分组,用 ROW_NUMBER() 标记每组内的行序号,只删序号 > 1 的行。
- 重复判断字段必须明确指定,不能笼统说“整行重复”——
SELECT DISTINCT语义和删除逻辑不等价 -
PARTITION BY列要覆盖所有判定重复的字段,漏一个就会把本该去重的记录当成不同组 - 排序字段(
ORDER BY)决定“保留哪条”,比如按created_at DESC可保留最新记录
标准写法:带别名的 CTE 必须显式命名列
很多报错源于 CTE 定义中未给 ROW_NUMBER() 结果起别名,导致后续 WHERE rn > 1 找不到列。CTE 内部字段名必须清晰可引用。
WITH duplicates AS (
SELECT id, name, email,
ROW_NUMBER() OVER (
PARTITION BY name, email
ORDER BY created_at DESC
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM duplicates WHERE rn > 1
);- PostgreSQL 和 SQL Server 允许更简洁的
DELETE FROM users USING duplicates WHERE ...,但跨库兼容性差,不推荐 - MySQL 要求 CTE 后的
DELETE必须用子查询包装,不能直接DELETE FROM users WHERE id IN (SELECT ... FROM duplicates)—— 某些旧版本会报错You can't specify target table 'users' for update in FROM clause - 如果表无主键(如日志表),需用
ctid(PostgreSQL)或%%physloc%%(SQL Server)等物理标识替代id,否则无法精确定位行
误删风险最高的三个操作习惯
重复删除最常不是语法错,而是执行前没验证、没备份、没加事务控制。
- 永远先跑一遍 CTE 查询本身:
SELECT * FROM duplicates WHERE rn > 1,确认要删的数据是否符合预期 - 不要跳过
BEGIN TRANSACTION/ROLLBACK测试环节,尤其在生产环境;CTE 删除不可回退(除非有备份或 binlog) - 避免在
PARTITION BY中混用可空字段(如email为 NULL)——多数数据库中NULL = NULL为 false,会导致所有 NULL 值被各自分成独立一组,看似不重复实则全保留
性能卡点:大表必须加联合索引
当表超百万行,PARTITION BY a, b ORDER BY c 的开销直线上升。没有对应索引时,ROW_NUMBER() 会强制全表扫描并排序。
应创建覆盖索引:CREATE INDEX idx_dup_check ON users (name, email, created_at DESC);
- 索引顺序必须和
PARTITION BY+ORDER BY严格一致,否则优化器可能弃用 - 如果只按
name去重,但经常要保留最新记录,则索引只需(name, created_at DESC),不必包含email - 执行前查执行计划:
EXPLAIN或EXPLAIN ANALYZE,确认是否走了索引、是否出现Sort节点
CTE 删除重复的本质是“标记后筛选”,不是魔法。真正容易被忽略的是 NULL 处理和索引缺失——这两点在测试库看不出问题,一上生产就慢或删错。

















