MySQL 刪除重複數據 3 種高效方法：子查詢、連接刪除、臨時表法

場景

表 `users(id PK, name)` 中 `name` 有重複，需保留每組 `name` 的**最小 `id`**，刪除其餘重複行。

方法一：子查詢 + 衍生表 (MySQL 限制繞過)

MySQL 不允許 `DELETE` 目標表同時出現在子查詢中 (報錯 1093)。**解決：用衍生表包裝子查詢**。

DELETE FROM users 
WHERE id IN (
    SELECT * FROM (  -- 關鍵：衍生表物化，繞過 1093 錯誤
        SELECT MIN(id) 
        FROM users 
        GROUP BY name 
        HAVING COUNT(name) > 1
    ) AS t
);

方法二：多表連接刪除 (標準 SQL、高效)

DELETE a 
FROM users AS a
INNER JOIN (
    SELECT MIN(id) AS id, name 
    FROM users 
    GROUP BY name 
    HAVING COUNT(name) > 1
) AS b ON a.name = b.name AND a.id <> b.id;

• 刪除 `a` 表中與 `b` (保留最小 id) 同名但 id 不同的行。
• 利用 `INNER JOIN` 語義清晰，優化器通常能生成高效計劃。

方法三：臨時表重建法 (超大表、鎖表時間可控)

-- 1. 創建臨時表存儲去重後的數據
CREATE TABLE tmp_users AS 
SELECT MIN(id) AS id, name FROM users GROUP BY name;

-- 2. 清空原表 (TRUNCATE 瞬間完成，不產生 Binlog/Undo Log)
TRUNCATE TABLE users;

-- 3. 寫回去重數據
INSERT INTO users SELECT * FROM tmp_users;

-- 4. 清理
DROP TABLE tmp_users;

優勢：避免大事務長鎖表，`TRUNCATE` + `INSERT` 速度極快，適合千萬級大表。劣勢：需短暫鎖表 (TRUNCATE/INSERT)，主鍵 ID 會重排 (若業務依賴 ID 不變則不可用)。

方法對比與選擇建議

進階：保留最大 ID / 按時間保留最新

-- 保留最大 ID (最新插入)
-- 方法二修改：
DELETE a FROM users a
INNER JOIN (
    SELECT MAX(id) AS id, name FROM users GROUP BY name HAVING COUNT(*) > 1
) b ON a.name = b.name AND a.id <> b.id;

-- 保留最新時間 (假設有 create_time)
DELETE a FROM users a
INNER JOIN (
    SELECT name, MAX(create_time) AS max_time FROM users GROUP BY name HAVING COUNT(*) > 1
) b ON a.name = b.name AND a.create_time < b.max_time;

添加唯一索引防止未來重複

-- 去重完成後，添加唯一約束
ALTER TABLE users ADD UNIQUE KEY uk_name (name);
-- 或複合唯一
ALTER TABLE users ADD UNIQUE KEY uk_name_phone (name, phone);

總結

• **中小表、需保留原 ID**：優先 **方法二 (連接刪除)**，標準、高效、語義清晰。
• **超大表、允許 ID 變更、維護窗口短**：**方法三 (臨時表重建)** 最快最穩。
• **快速臨時修復**：方法一 (衍生表) 可用，注意 MySQL 版本差異 (8.0+ CTE 更優雅)。
• **事後必加唯一索引**，從源頭杜絕重複。