删除表内重复记录的DELETE语句不生效问题求助

你的SQL语句存在两个核心问题，导致无法完成预期的删重操作：

• 删除逻辑完全相反：WHERE p1.id < p2.id的条件会触发删除id更小的记录，和你要保留小id、删除大id重复项的需求相悖
• 关联删除语法不规范：绝大多数支持关联删除的数据库（比如MySQL）都要求在DELETE后明确指定要删除哪个关联表的数据，你原语句没有标注删除目标，会直接执行报错或无效果

适配MySQL的关联删除写法 #

建议先执行SELECT查询确认待删除的记录，确认无误后再执行删除操作，避免误删数据：

-- 第一步：查询确认要删除的重复记录
SELECT p1.* FROM people p1
INNER JOIN people p2 
ON p1.first_name = p2.first_name 
AND p1.timestamp = p2.timestamp 
WHERE p1.id > p2.id;

-- 第二步：确认记录符合预期后执行删除
DELETE p1 FROM people p1
INNER JOIN people p2 
ON p1.first_name = p2.first_name 
AND p1.timestamp = p2.timestamp 
WHERE p1.id > p2.id;

通用窗口函数写法（适配MySQL 8.0+、PostgreSQL、SQL Server等） #

使用窗口函数的写法逻辑更清晰，也更容易适配不同数据库：

WITH duplicate_rank AS (
    SELECT 
        id,
        -- 按first_name和timestamp分组，组内按id升序排序，小id排在第一位
        ROW_NUMBER() OVER (PARTITION BY first_name, timestamp ORDER BY id ASC) AS rank_num
    FROM people
)
-- 删除组内排序大于1的记录，也就是重复的大id记录
DELETE FROM people WHERE id IN (SELECT id FROM duplicate_rank WHERE rank_num > 1);

• 生产环境执行删除操作前，务必提前备份全量数据
• 如果表数据量较大，建议先给first_name和timestamp字段加联合索引，大幅提升删重效率
• 不管用哪种写法，都要先执行查询验证待删除数据是否正确，再执行删除操作

内容的提问来源于stack exchange，提问作者Mariusz Adamczewski