場景定義

表 test(id INT PRIMARY KEY, name VARCHAR(20))，name 有重複值，需**保留每組第一條（最小 id）**，刪除其餘重複行。

方案一：臨時表 + 多表 DELETE（經典、通用）

-- 1. 建臨時表存每組最小 id
CREATE TEMPORARY TABLE tmp_ids AS
SELECT MIN(id) AS id, name FROM test GROUP BY name HAVING COUNT(*) > 1;

-- 2. 刪除非最小 id 的重複行
DELETE t FROM test t
INNER JOIN tmp_ids tmp ON t.name = tmp.name AND t.id > tmp.id;

-- 3. 清理
DROP TEMPORARY TABLE tmp_ids;

• 優點：MySQL 所有版本支援，邏輯清晰。
• 缺點：需建臨時表，大表可能較慢。

方案二：自連接 DELETE（無臨時表，單條 SQL）

DELETE t1 FROM test t1
INNER JOIN test t2 
  ON t1.name = t2.name AND t1.id > t2.id;

• 優點：單語句，無臨時表開銷。
• 缺點：大表自我連接產生巨大中間結果，可能鎖表久、甚至 OOM。

方案三：建新表換名（大量重複、鎖表時間敏感）

-- 1. 建新表保留去重資料
CREATE TABLE test_dedup AS
SELECT * FROM test WHERE id IN (
    SELECT MIN(id) FROM test GROUP BY name
);

-- 2. 重建索引/約束
ALTER TABLE test_dedup ADD PRIMARY KEY (id);
-- 其他索引...

-- 3. 原子切換
RENAME TABLE test TO test_old, test_dedup TO test;

-- 4. 驗證無誤後刪舊表
DROP TABLE test_old;

• 優點：鎖表時間極短（RENAME 原子操作），適合億級大表。
• 缺點：需額外磁碟空間，外鍵/觸發器/權限需重建。

方案四：MySQL 8.0+ CTE + 窗口函數（現代、優雅）

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

• ROW_NUMBER() 為每組按 id 排序打標，rn=1 保留。
• 可讀性最強，優化器通常能轉為高效執行計劃。

方案五：加唯一索引強制去重（MySQL 專屬技巧）

-- 1. 忽略錯誤建立唯一索引，自動刪除重複行（保留第一條）
ALTER IGNORE TABLE test ADD UNIQUE INDEX uniq_name (name);
-- ⚠️ MySQL 5.7+ 移除 IGNORE 子句，此法失效

MySQL 5.6 及以下有效，新版不可用。保留作歷史參考。

效能對比與選型建議

資料量	重複率	推薦方案	備註
< 10 萬	任意	方案一或二	單機秒級完成
10 萬 ~ 1000 萬	低 (<10%)	方案一（臨時表）	配合索引較快
10 萬 ~ 1000 萬	高 (>10%)	方案三（新表換名）	避免長鎖、碎片整理
> 1000 萬	任意	方案三 + 分批/pt-archiver	配合線上變更工具
MySQL 8.0 環境	任意	方案四（CTE+窗口）	語義清晰、優化器友好

預防重複的根治方案

1. 業務層去重：應用層檢查唯一性再寫入。
2. 資料庫層約束：ALTER TABLE test ADD UNIQUE INDEX uniq_name (name); 寫入時拋錯，應用捕獲處理。
3. INSERT IGNORE / ON DUPLICATE KEY UPDATE：

INSERT IGNORE INTO test (name) VALUES ('duplicate');  -- 靜默忽略
INSERT INTO test (name) VALUES ('dup') ON DUPLICATE KEY UPDATE name=VALUES(name);  -- 更新或忽略

總結

刪除重複資料無銀彈。**小表用自連接 DELETE，大表用新表換名（RENAME），MySQL 8.0 首選 CTE+窗口函數**。最重要的是：在源頭加唯一索引約束，從架構層面杜絕重複產生。