MySQL 處理重複資料完整指南：防止、統計、過濾、刪除四大場景

MySQL 重複資料處理完整指南

資料表中出現重複紀錄是常見問題。本文系統整理四大場景：防止寫入、統計重複、查詢去重、刪除重複，附實戰 SQL。

一、防止表中出現重複資料

1. 主鍵 / 唯一索引（最根本）

-- 雙主鍵（複合主鍵），欄位不可為 NULL
CREATE TABLE person_tbl (
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    sex CHAR(10),
    PRIMARY KEY (last_name, first_name)
);

2. 唯一索引（允許 NULL）

CREATE TABLE person_tbl (
    first_name CHAR(20) NOT NULL,
    last_name CHAR(20) NOT NULL,
    sex CHAR(10),
    UNIQUE (last_name, first_name)
);

3. INSERT IGNORE INTO（靜默跳過重複）

INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ('Jay', 'Thomas');
-- 首次執行：1 row affected
-- 再次執行：0 rows affected（僅警告，不報錯）

差異： REPLACE INTO 遇重複主鍵/唯一鍵會**先刪後插**；INSERT IGNORE 則**保留舊資料、忽略新資料**。

二、統計重複資料

SELECT COUNT(*) AS repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;

通用步驟：① 確定可能重複的欄位 ② SELECT 列出該欄位 + COUNT(*) ③ GROUP BY 該欄位 ④ HAVING COUNT(*) > 1。

三、過濾重複資料（查詢去重）

• DISTINCT： SELECT DISTINCT last_name, first_name FROM person_tbl ORDER BY last_name;
• GROUP BY： SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

四、刪除重複資料（保留一筆）

方法 1：建新表 → 刪舊表 → 改名（通用、安全）

CREATE TABLE tmp SELECT last_name, first_name, sex
FROM person_tbl GROUP BY (last_name, first_name);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;

方法 2：加上 IGNORE 索引/主鍵直接去重（快速、原地）

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

注意： MySQL 5.7+ 已移除 ALTER IGNORE TABLE 語法，需改用方法 1 或程式邏輯處理。

總結建議

• **源頭治理**：建表即加主鍵/唯一索引。
• **寫入端**：視業務用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE。
• **清理歷史**：小表用建新表法，大表分批次 DELETE ... WHERE id NOT IN (SELECT MIN(id) ... GROUP BY ...) 避免長鎖表。