核心場景分類
- 防重 (寫入端)： 業務邏輯/資料庫約束阻止重複寫入
- 查重 (讀取端)： 統計重複分布、過濾重複顯示
- 去重 (清理端)： 刪除現有重複資料、保留唯一記錄
一、防重：資料庫層面的唯一性保障 (最推薦)
1. 主鍵約束 (單欄/複合)
CREATE TABLE person_tbl (
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE KEY uk_name (last_name, first_name)
);
差異： 允許一個 NULL (InnoDB 視 NULL≠NULL)、表可有多個唯一索引、非叢集。
3. 插入策略對比
| 語句 | 重複時行為 | 返回值 | 適用場景 |
|---|
INSERT | 報錯 (Duplicate entry) | 錯誤碼 1062 | 嚴格業務、需感知衝突 |
INSERT IGNORE | 靜默跳過、不插入 | 受影響行數 0、Warning | 日誌/統計類、允許重複嘗試 |
REPLACE INTO | 刪除舊行 → 插入新行 | 刪除+插入行數和 | 需覆蓋更新、主鍵/唯一鍵衝突 |
INSERT ... ON DUPLICATE KEY UPDATE | 更新指定欄位 | 1(新增)/2(更新) | 最通用：計數器、最後更新時間、業務覆蓋 |
ON DUPLICATE KEY UPDATE 實戰範例
-- 核心模板: GROUP BY + HAVING COUNT(*) > 1
SELECT COUNT(*) AS repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1
ORDER BY repetitions DESC;
通用查重三步法
- 確定可能重複的業務鍵欄位 (如 user_id+order_no、phone、email)
SELECT 列表包含業務鍵 + COUNT(*)GROUP BY 業務鍵欄位HAVING COUNT(*) > 1
三、過濾重複：查詢去重 (不修改資料)
| 方法 | 語法 | 特點 | 效能 |
|---|
| DISTINCT | SELECT DISTINCT col1, col2 FROM tbl | 簡單、標準、所有欄位組合去重 | 需排序/哈希，大表較慢 |
| GROUP BY | SELECT col1, col2 FROM tbl GROUP BY col1, col2 | 可搭配聚合函數 (MAX/MIN/COUNT) | 同 DISTINCT，可利用索引 |
| ROW_NUMBER() 窗口函數 (8.0+) | SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY id) rn FROM tbl) t WHERE rn=1 | 保留特定行 (最新/最舊/最大 ID)、支援複雜排序 | 單次掃描，大表首選 |
四、刪除重複：物理去重方案對比
方案 1：建新表 → 刪舊表 → 改名 (最通用、可控)
-- 僅 5.6 及以下可用
ALTER IGNORE TABLE person_tbl ADD UNIQUE KEY uk_name (last_name, first_name);
-- 會自動刪除重複行，保留第一條，但行為不可控、已廢棄
方案 3：多表連接刪除 (保留最小/最大 ID)
WITH ranked AS (
SELECT id, ROW_NUMBER() OVER (PARTITION BY last_name, first_name ORDER BY id) rn
FROM person_tbl
)
DELETE FROM person_tbl
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
五、方案選型決策樹
| 場景 | 資料量 | MySQL 版本 | 推薦方案 |
|---|
| 防重寫入 | 任意 | 所有 | 唯一索引 + INSERT ... ON DUPLICATE KEY UPDATE |
| 事後清理 | < 10 萬 | 所有 | 建新表法 (方案 1) |
| 事後清理 | 10 萬 - 1 億 | 5.7/8.0 | CTE + 窗口函數 (方案 4) 或分批 DELETE |
| 事後清理 | > 1 億 | 8.0 | pt-archiver 歸檔重建 或 線上結構變更 |
| 保留最新/最舊 | 任意 | 8.0+ | ROW_NUMBER() 窗口函數 |
六、效能優化關鍵點
- 建立臨時唯一索引加速分組：
ALTER TABLE person_tbl ADD KEY idx_dedup (last_name, first_name);
- 分批處理大表：
DELETE ... LIMIT 10000 循環執行，避免長事務、鎖升級、Undo 爆漲
- 停服維護窗口： 大表去重建議業務低峰期、加鎖維護
- 備份先行： 任何刪除操作前必須
mysqldump --single-transaction 完整備份
總結
重複資料治理遵循 「源頭防重 > 過程查重 > 事後去重」 分層策略。資料庫約束 (主鍵/唯一索引) 是第一道防線，ON DUPLICATE KEY UPDATE 是寫入端最靈活工具。事後清理首選 建新表法 (通用可控) 或 CTE 窗口函數 (8.0+ 精準高效)，大表務必分批、備份、驗證。建立資料質量監控儀表板，定期掃描核心表重複率，納入 DBA 例巡指標。
