SQL 重複資料查詢與刪除完整實戰

重複資料清理是資料治理常態作業。本文涵蓋 DISTINCT 查詢去重、GROUP BY 找出重複群組、以及實際刪除保留一筆的多種策略。

查詢階段：發現重複

單欄位去重查詢

SELECT DISTINCT column_name FROM table_name;

多欄位組合去重

SELECT DISTINCT col1, col2, col3 FROM table_name;

找出重複群組 (含計數)

-- 單欄位
SELECT name, COUNT(*) AS cnt FROM a GROUP BY name HAVING COUNT(*) > 1;

-- 多欄位組合
SELECT name, sex, COUNT(*) FROM a GROUP BY name, sex HAVING COUNT(*) > 1;

刪除階段：保留一筆刪除其餘

策略一：利用唯一鍵/主鍵 (最推薦)

-- 假設有 id 主鍵，保留每組最小 id
DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
ON t1.dup_col = t2.dup_col AND t1.id > t2.id;

策略二：MySQL 8.0+ 窗口函數 (最現代)

WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY id) AS rn
    FROM table_name
)
DELETE FROM table_name WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

策略三：暫存表重建 (大量資料最快)

-- 1. 建立去重後的新表
CREATE TABLE new_table AS 
SELECT DISTINCT * FROM old_table;  -- 或 GROUP BY 關鍵欄位

-- 2. 重建索引/約束
ALTER TABLE new_table ADD PRIMARY KEY (id);

-- 3. 替換舊表
RENAME TABLE old_table TO old_table_bak, new_table TO old_table;

策略四：游標逐筆刪除 (舊版/特殊邏輯)

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR 
    SELECT pk_col, COUNT(*) FROM table_name GROUP BY pk_col HAVING COUNT(*) > 1;
OPEN cur;
FETCH NEXT FROM cur INTO @id, @max;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @max = @max - 1;
    SET ROWCOUNT @max;  -- SQL Server 特有，MySQL 用 LIMIT
    DELETE FROM table_name WHERE pk_col = @id;
    FETCH NEXT FROM cur INTO @id, @max;
END;
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

策略五：INSERT ... ON DUPLICATE KEY UPDATE (寫入時去重)

-- 合併兩表，以表2為主
SELECT a,b FROM table1 WHERE a NOT IN (SELECT a FROM table2)
UNION
SELECT a,b FROM table2;

實戰案例：清理用戶表重複 Email

-- 1. 確認重複範圍
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- 2. 保留註冊最早 (id 最小) 的記錄
DELETE u1 FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;

-- 3. 加上唯一索引防止未來重複
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

效能與安全建議

• 先備份：刪除前務必備份或建立快照
• 分批處理：百萬級資料分批 LIMIT 10000 刪除，避免長鎖表
• 離線時段：業務低峰期執行
• 驗證結果：刪除後再次執行重複查詢確認歸零
• 加上約束：清理後必須加上 UNIQUE 索引從源頭防重