MySQL基于行号删除重复ID行失效，请求技术协助

嘿，这个问题我之前处理过好几次！当你用SELECT能精准找到要删除的重复行，但换成DELETE就毫无反应，大概率是踩了MySQL同表删除+子查询的限制这个坑，下面给你拆解原因和可行的解决方案：

为什么原来的DELETE没效果？ #

MySQL默认不允许在DELETE语句的子查询中直接引用正在被修改的表。比如你可能写了类似这样的语句：

DELETE FROM your_table
WHERE row_num NOT IN (
    SELECT MIN(row_num)
    FROM your_table
    GROUP BY ID
);

这种写法里，子查询和DELETE操作都指向了同一个your_table，MySQL会因为表数据正在被修改，无法正确匹配子查询的结果，最终导致删除操作没有生效。

可行的解决方案 #

方案1：用JOIN关联派生表删除 #

把要保留的行信息放到一个派生表里，再通过JOIN关联原表进行删除，绕过同表引用限制：

DELETE t1
FROM your_table t1
JOIN (
    -- 先查出每个ID要保留的最小行号
    SELECT ID, MIN(row_num) AS keep_row_num
    FROM your_table
    GROUP BY ID
) t2 ON t1.ID = t2.ID
-- 删除那些行号不等于保留行号的记录
WHERE t1.row_num != t2.keep_row_num;

方案2：用NOT EXISTS+嵌套子查询 #

如果更习惯用EXISTS语法，同样通过嵌套子查询来规避限制：

DELETE FROM your_table t1
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT ID, MIN(row_num) AS keep_row_num
        FROM your_table
        GROUP BY ID
    ) t2
    WHERE t1.ID = t2.ID AND t1.row_num != t2.keep_row_num
);

方案3：用CTE（MySQL 8.0+适用） #

如果你的MySQL版本是8.0及以上，用CTE（公共表表达式）会更直观，尤其是你之前的SELECT用了窗口函数的话：

WITH ranked_rows AS (
    -- 给每个ID分组的行按行号排序，标记行号
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY row_num) AS rn
    FROM your_table
)
DELETE FROM your_table
WHERE (ID, row_num) IN (
    -- 筛选出每个ID里排序大于1的行（即重复的行）
    SELECT ID, row_num
    FROM ranked_rows
    WHERE rn > 1
);

方案4：临时表中转（最稳妥） #

如果怕出错，或者数据量不大，用临时表中转是最安全的方式：

-- 1. 创建临时表，保存所有需要保留的行
CREATE TEMPORARY TABLE temp_table AS
SELECT *
FROM your_table t1
WHERE row_num = (
    SELECT MIN(row_num)
    FROM your_table t2
    WHERE t2.ID = t1.ID
);

-- 2. 清空原表（注意：操作前一定要备份！）
TRUNCATE TABLE your_table;

-- 3. 把临时表的数据导回原表
INSERT INTO your_table
SELECT * FROM temp_table;

-- 4. 可选：删除临时表（会话结束后会自动删除）
DROP TEMPORARY TABLE temp_table;

重要提醒 #

不管用哪种方案，操作前一定要先备份数据！比如先运行SELECT * FROM your_table导出数据，或者用mysqldump备份整个表，避免误删重要数据。

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