使用NOT IN和GROUP BY删除重复行无效问题排查

你遇到的核心问题是同表DELETE与SELECT子查询的执行上下文冲突。像MySQL这类数据库，当你在DELETE语句里直接引用同一个表的子查询时，子查询会读取到DELETE操作过程中的表中间状态，导致NOT IN的判断逻辑失效——相当于你在修改表的同时查询它，结果互相干扰，最终没有行被匹配删除。

而直接代入ID列表能正常执行，是因为此时数据库用的是固定的ID集合，不需要在删除过程中动态查询同一张表，自然不会有上下文冲突。

下面是几种可行的解决办法：

方法1：嵌套子查询生成临时结果集 #

通过嵌套一层子查询，强制数据库先计算出所有需要保留的ID，再执行删除操作：

DELETE FROM content_stacks 
WHERE id NOT IN (
    SELECT keep_id FROM (
        SELECT id AS keep_id 
        FROM content_stacks
        GROUP BY user_id, content_id
    ) AS temp_table
);

这里的临时表temp_table会先确定所有要保留的ID，避免了删除操作对子查询的干扰。

方法2：用JOIN方式删除重复行 #

这种方式逻辑更直观，直接关联同一张表，删除每组中ID较大的重复行（和你原逻辑保留每组首个ID一致）：

DELETE cs1
FROM content_stacks cs1
JOIN content_stacks cs2 
    ON cs1.user_id = cs2.user_id 
    AND cs1.content_id = cs2.content_id 
    AND cs1.id > cs2.id;

方法3：窗口函数标记重复行（适用于支持窗口函数的数据库） #

如果你的数据库是MySQL 8+、PostgreSQL等支持窗口函数的版本，可以用ROW_NUMBER()给每组重复行打标记，再删除标记大于1的行：

DELETE FROM content_stacks
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY user_id, content_id ORDER BY id) AS rn
        FROM content_stacks
    ) AS temp
    WHERE rn > 1
);

这种方式可以通过ORDER BY灵活控制保留哪一行（比如最早创建的、最新的）。

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