MySQL 刪除重復數據的 4 種高效方法：去重最佳實踐

場景定義

表 users(id PK, name)，name 存在重複，需保留每組第一條 (最小 id)，刪除其餘重複行。

方法對比總覽

方法一：子查詢 + IN (修正版)

-- ❌ 錯誤：MySQL 不允許 DELETE 目標表同時出現在子查詢中
DELETE FROM users 
WHERE id IN (SELECT MIN(id) FROM users GROUP BY name HAVING COUNT(*) > 1);
-- ERROR 1093: You can't specify target table 'users' for update in FROM clause

-- ✅ 正確：包裝一層衍生表 (MySQL 會物化衍生表)
DELETE FROM users 
WHERE id IN (
    SELECT * FROM (
        SELECT MIN(id) FROM users GROUP BY name HAVING COUNT(*) > 1
    ) AS t
);

原理

內層子查詢生成衍生表 t，物化為臨時表，外層 IN 查詢臨時表，繞過「目標表不能出現在 FROM 子句」限制。

方法二：多表 DELETE JOIN (推薦大表)

DELETE u1 
FROM users u1
INNER JOIN (
    SELECT MIN(id) AS min_id, name 
    FROM users 
    GROUP BY name 
    HAVING COUNT(*) > 1
) u2 ON u1.name = u2.name AND u1.id > u2.min_id;

優勢

• 單條 SQL，無衍生表物化開銷
• 直接按主鍵 id > min_id 定位刪除，利用索引
• 可加 LIMIT 分批刪除，避免長事務鎖表

分批刪除腳本 (避免長事務)

DELIMITER $$
CREATE PROCEDURE batch_dedup()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    
    WHILE NOT done DO
        DELETE u1 
        FROM users u1
        INNER JOIN (
            SELECT MIN(id) AS min_id, name 
            FROM users 
            GROUP BY name 
            HAVING COUNT(*) > 1
        ) u2 ON u1.name = u2.name AND u1.id > u2.min_id
        LIMIT batch_size;
        
        IF ROW_COUNT() < batch_size THEN
            SET done = 1;
        END IF;
        
        -- 可選：釋放鎖間隔
        DO SLEEP(0.1);
    END WHILE;
END$$
DELIMITER ;

方法三：臨時表重建 (超大表/需 OPTIMIZE)

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

-- 2. 清空原表 (TRUNCATE 釋放空間、重置自增 ID)
TRUNCATE TABLE users;

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

-- 4. 刪除臨時表
DROP TABLE tmp_users;

優勢

• 速度最快：批量寫入 vs 逐行刪除
• 順便整理碎片、重建索引、重置自增 ID
• 鎖表時間極短 (僅 TRUNCATE + INSERT 期間)

注意

• 需足夠磁盤空間存放臨時表
• 外鍵關聯表需先處理關聯
• 大表建議 INSERT ... SELECT 分批

方法四：ROW_NUMBER 窗口函數 (MySQL 8.0+ 推薦)

-- 1. 標記重複行
WITH ranked AS (
    SELECT id, name,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM users
)
-- 2. 刪除 rn > 1 的行
DELETE FROM users 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

優勢

• 語法標準、可讀性最強
• 支持複雜去重邏輯 (如按多列分組、按時間保留最新)
• 優化器可下推，性能優秀

預防重複數據的根治方案

-- 業務允許：name 唯一
ALTER TABLE users ADD UNIQUE INDEX uk_name (name);

-- 業務需允許重名但同用戶不重複：復合唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_user_name (user_id, name);

去重檢查清單

□ 確認去重鍵 (單列/多列)
□ 確認保留策略 (最小 id / 最大 id / 最新時間 / 業務邏輯)
□ 評估數據量選擇方法 (萬級<子查詢、百萬級1)
□ 重建索引 / ANALYZE TABLE
□ 添加唯一約束防止復發

總結

MySQL 去重四大法：子查詢 IN (衍生表包裝)、多表 DELETE JOIN、臨時表重建、ROW_NUMBER 窗口函數。中小表用子查詢，大表用 JOIN 或窗口函數，超大表重建最快。根治唯有唯一約束，去重只是治標。