MySQL 刪除重複資料僅保留一條：高效 SQL 寫法

核心 SQL

DELETE t1
FROM software t1
INNER JOIN (
    SELECT MIN(id) AS id, ident
    FROM software
    GROUP BY ident
    HAVING COUNT(1) > 1
) t2 ON t1.ident = t2.ident
WHERE t1.id > t2.id;

邏輯解析

1. 子查詢：按 ident 分組，取每組最小 id（保留目標），篩選有重複的組
2. 主查詢：刪除同 ident 但 id 大於最小值的記錄

替代寫法（MySQL 8.0+ CTE）

WITH duplicates AS (
    SELECT id, ident,
           ROW_NUMBER() OVER (PARTITION BY ident ORDER BY id) AS rn
    FROM software
)
DELETE FROM software
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

大表分批刪除避免鎖死

DELIMITER //
CREATE PROCEDURE batch_delete_duplicates()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE deleted_count INT DEFAULT 0;
    
    WHILE done = 0 DO
        DELETE t1
        FROM software t1
        INNER JOIN (
            SELECT MIN(id) AS id, ident
            FROM software
            GROUP BY ident
            HAVING COUNT(1) > 1
        ) t2 ON t1.ident = t2.ident
        WHERE t1.id > t2.id
        LIMIT batch_size;
        
        SET deleted_count = ROW_COUNT();
        IF deleted_count < batch_size THEN
            SET done = 1;
        END IF;
        COMMIT;
        DO SLEEP(0.5);
    END WHILE;
END//
DELIMITER ;

預防重複的最佳實踐

• 業務唯一鍵加 UNIQUE INDEX
• 應用層冪等性設計（唯一請求 ID、去重快取）
• 定期跑 pt-duplicate-key-checker 掃描