業務背景

機場資料表 airport，欄位：id (PK), name, c_name, code (三字碼), city_name。業務要求 code 唯一，但爬蟲抓取產生重複三字碼，需清洗後再加唯一索引。

表結構

CREATE TABLE airport (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  c_name VARCHAR(100),
  code CHAR(3) NOT NULL,
  city_name VARCHAR(100)
);

步驟 1：移除現有唯一索引（若有）

SHOW INDEX FROM airport;
DROP INDEX code ON airport;

步驟 2：識別重複資料並保留「最大 ID」

邏輯：同一 code 下，保留 MAX(id)，刪除其餘。

-- 先建臨時表存放待刪除 ID
CREATE TEMPORARY TABLE tmp_dup AS
SELECT id FROM airport
WHERE id IN (
    SELECT id FROM airport 
    GROUP BY code HAVING COUNT(*) > 1
)
AND id NOT IN (
    SELECT MAX(id) FROM airport 
    GROUP BY code HAVING COUNT(*) > 1
);

為什麼用臨時表？ MySQL 不允許在同一語句中既查詢又修改同一張表（You can't specify target table for update in FROM clause）。

步驟 3：執行刪除

DELETE FROM airport WHERE id IN (SELECT id FROM tmp_dup);

步驟 4：驗證清洗結果

SELECT code, COUNT(*) FROM airport GROUP BY code HAVING COUNT(*) > 1;
-- 應返回空集合

步驟 5：重建唯一索引

ALTER TABLE airport ADD UNIQUE INDEX uk_code (code);

進階：單條 SQL 完成（MySQL 8.0+ CTE）

WITH to_delete AS (
    SELECT id FROM airport
    WHERE id IN (
        SELECT id FROM airport GROUP BY code HAVING COUNT(*) > 1
    )
    AND id NOT IN (
        SELECT MAX(id) FROM airport GROUP BY code HAVING COUNT(*) > 1
    )
)
DELETE a FROM airport a JOIN to_delete d ON a.id = d.id;

效能優化提示

• 大表建議分批刪除（每次 1000-5000 行），避免長事務鎖表、Undo Log 膨脹。
• 確保 code 上有索引（即使非唯一），加速 GROUP BY。
• 若資料量極大（億級），考慮 pt-archiver 或建新表 INSERT ... SELECT DISTINCT ON (code) ... 切換。