MySQL 刪除重複資料高效方案：利用臨時表保留最小 ID 實現去重

MySQL 刪除重複資料高效方案

資料表中因缺乏唯一約束或業務邏輯疏漏導致重複資料產生是常見問題。本文介紹一種經典且高效的「臨時表 + 子查詢」去重策略：依據業務鍵 (如 content) 分組，保留每組最小主鍵 ID，刪除其餘重複行。

實操 SQL 腳本

-- 1. 建立臨時表，儲存每組重複資料的最小 ID
CREATE TABLE tmp AS 
SELECT MIN(id) AS col1 FROM test GROUP BY content;

-- 2. 刪除主表中不在臨時表清單內的行 (即重複資料)
DELETE FROM test WHERE id NOT IN (SELECT col1 FROM tmp);

-- 3. 清理臨時表
DROP TABLE tmp;

原理解析

1. 分組去重鍵： GROUP BY content 將相同 content 的行歸為一組。
2. 保留基準： MIN(id) 取每組最小的主鍵 ID 作為「保留代表」。
3. 反向刪除： NOT IN 將非代表行標記為待刪除目標。
4. 資源釋放： 臨時表用畢即刪，避免佔用空間。

適用場景與注意事項

• 適用於單表去重、資料量中等 (萬~百萬級) 且有明確業務去重鍵的場景。
• 大表優化： 若資料量極大 (千萬級以上)，NOT IN 子查詢效能可能下降，建議改用 LEFT JOIN ... IS NULL 或 NOT EXISTS 寫法，並確保 id 與 content 有適當索引。
• 替代方案 (MySQL 8.0+)： 可使用 ROW_NUMBER() 窗口函數單條 SQL 完成，無需臨時表：

  WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY content ORDER BY id) AS rn
    FROM test
  )
  DELETE FROM test WHERE id IN (SELECT id FROM cte WHERE rn > 1);

• 預防重複： 根治之道是在表設計階段於業務鍵上建立 UNIQUE INDEX，從源頭阻擋髒資料寫入。

參考來源： CSDN 博客、百度知道相關技術討論整理。