MySQL 刪除表中指定字段重複記錄的三種 SQL 寫法對比

場景定義

表 event：id (PK, AI)、sid (業務鍵)、detail。需保留每個 sid 的**最小 id** 記錄，刪除其餘重複行。

方案一：相關子查詢 + MIN(id)

DELETE FROM event AS e
WHERE id != (SELECT MIN(id) FROM event WHERE sid = e.sid);

• 直觀易懂，逐行執行子查詢，大表性能差（O(N²)）。
• MySQL 5.7+ 優化器可能轉為 Semi-Join，但仍不穩定。

方案二：NOT IN + 派生表（去重後的最小 id 集合）

DELETE FROM event
WHERE sid NOT IN (
    SELECT mid FROM (
        SELECT sid, MIN(id) AS mid FROM event GROUP BY sid
    ) AS t
);

• 子查詢物化為臨時表，再反向匹配，性能優於方案一。
• 注意 NOT IN 若子查詢結果含 NULL 會導致全表刪除，**sid 定義 NOT NULL 才安全**。

方案三：ALTER IGNORE TABLE + 唯一索引（MySQL 5.7 前神技，8.0 移除）

ALTER IGNORE TABLE event ADD UNIQUE INDEX uniq_sid (sid);
ALTER TABLE event DROP INDEX uniq_sid;

• IGNORE 讓重複鍵報警降級為警告，自動保留第一行刪除後續行。
• **極快**，底層按索引構建天然去重。
• MySQL 5.7 已棄用，8.0 完全移除 IGNORE 子句**，不可用於新版本。

-- 1. 創建新表保留去重數據
CREATE TABLE event_dedup AS
SELECT * FROM event WHERE id IN (
    SELECT MIN(id) FROM event GROUP BY sid
);

-- 2. 原子替換
RENAME TABLE event TO event_old, event_dedup TO event;

-- 3. 重建索引/外鍵/權限
-- 4. 驗證無誤後 DROP TABLE event_old;