MySQL 刪除重複數據進階技巧：多層子查詢與別名繞過限制

MySQL 刪除重複數據進階技巧：多層子查詢與別名繞過限制

MySQL 不允許直接在 DELETE 的子查詢中引用同一張表，會報錯 You can't specify target table for update in FROM clause。本文揭秘多層嵌套+別名繞過限制的核心模式。

核心模式

DELETE FROM target_table
WHERE id IN (
    SELECT id FROM (
        SELECT MIN(id) AS id
        FROM target_table
        GROUP BY duplicate_column
        HAVING COUNT(*) > 1
    ) AS alias_name
);

關鍵點解析

1. 最內層：按去重列分組，取 MIN(id) 保留最早記錄，HAVING COUNT(*) > 1 篩選重複組。
2. 中間層：必須加別名 (如 AS alias_name)，將結果物質化為派生表，打破「同表更新查詢」限制。
3. 外層：DELETE ... WHERE id IN (...) 精準刪除標記的重複行。

實戰場景：清理標題重複文章

DELETE FROM articles
WHERE id IN (
    SELECT id FROM (
        SELECT MIN(id) AS id
        FROM articles
        GROUP BY title
        HAVING COUNT(*) > 1
    ) AS dup_ids
);

擴展：保留最新記錄

DELETE FROM articles
WHERE id IN (
    SELECT id FROM (
        SELECT MAX(id) AS id
        FROM articles
        GROUP BY title
        HAVING COUNT(*) > 1
    ) AS dup_ids
);

大表優化建議

• 分批刪除：LIMIT 1000 循環執行，避免長事務鎖表。
• 創建臨時表存主鍵，再 JOIN DELETE。
• 利用 ROW_NUMBER() 窗口函數 (MySQL 8.0+) 更優雅：

WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY title ORDER BY id) AS rn
    FROM articles
)
DELETE FROM articles WHERE id IN (SELECT id FROM ranked WHERE rn > 1);