MySQL 刪除重複數據完整方案：CTE、臨時表、JOIN 刪除多維對比

直接 DELETE 子查詢的陷阱

DELETE FROM zqzrdp WHERE tel IN (
    SELECT MIN(dpxx_id) FROM zqzrdp GROUP BY tel HAVING COUNT(tel) > 1
);

報錯：You can't specify target table 'zqzrdp' for update in FROM clause

MySQL 不允許在同一語句中既更新目標表又從該表讀取子查詢。

五種可行方案對比

方案一：多表 DELETE JOIN（MySQL 專用語法）

DELETE a
FROM test1 a
JOIN (
    SELECT subject, receiver, MAX(id) AS max_id
    FROM test1
    WHERE status = 0
    GROUP BY receiver, subject
    HAVING COUNT(*) > 1
) b ON a.subject = b.subject AND a.receiver = b.receiver AND a.id < b.max_id;

• 保留每組最大 ID 的行
• 需對 subject、receiver 建立索引，否則全表掃描極慢

方案二：臨時表中轉（通用、穩健）

-- 1. 建立臨時表存儲要保留的主鍵
CREATE TEMPORARY TABLE tmp_keep AS
SELECT MIN(id) AS id FROM zqzrdp GROUP BY tel HAVING COUNT(*) > 1;

-- 2. 刪除不在保留集合中的重複行
DELETE FROM zqzrdp
WHERE tel IN (SELECT tel FROM zqzrdp GROUP BY tel HAVING COUNT(*) > 1)
  AND id NOT IN (SELECT id FROM tmp_keep);

• 臨時表會話結束自動釋放
• 適合一次性清理腳本

方案三：新表重建法（大表最快）

ALTER TABLE zqzrdp ADD autoID INT AUTO_INCREMENT NOT NULL;
CREATE TABLE tmp SELECT MIN(autoID) AS autoID FROM zqzrdp GROUP BY Name, Address;
CREATE TABLE tmp2 SELECT zqzrdp.* FROM zqzrdp JOIN tmp ON zqzrdp.autoID = tmp.autoID;
DROP TABLE zqzrdp;
RENAME TABLE tmp2 TO zqzrdp;

• 避開 DELETE 產生的碎片與鎖爭用
• 大表耗時遠小於逐行刪除
• 需業務停寫窗口

方案四：ROW_NUMBER() 窗口函數 (MySQL 8.0+)

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

• 語義清晰、效能優異
• 需 8.0.2+ 支援 CTE + 窗口函數

方案五：唯一索引強制去重（預防為主）

ALTER IGNORE TABLE zqzrdp ADD UNIQUE INDEX uk_tel (tel);

• IGNORE 會自動刪除重複行（保留第一行）
• 5.7+ 已移除 IGNORE 子句，僅適用舊版本
• 新版本需先建唯一索引報錯，再手動清理

選型建議