MySQL 重複數據處理全方案：防重、統計、去重查詢、刪除重複三大場景

場景一：防止重複寫入（表設計層面）

1. 主鍵/唯一索引

CREATE TABLE person_tbl (
  first_name CHAR(20) NOT NULL,
  last_name CHAR(20) NOT NULL,
  PRIMARY KEY (last_name, first_name)
);

2. INSERT IGNORE 靜默跳過

INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ('Jay', 'Thomas');

3. REPLACE INTO 覆蓋寫入

REPLACE INTO person_tbl (last_name, first_name) VALUES ('Ajay', 'Kumar');

本質：DELETE + INSERT，觸發自增 ID 變化、外鍵級聯，慎用。

4. 唯一索引替代主鍵

UNIQUE (last_name, first_name)

場景二：統計與識別重複

SELECT COUNT(*) AS repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 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;

場景四：刪除表中重複行（保留一條）

方法一：新建臨時表法

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;

方法二：ALTER IGNORE ADD PRIMARY KEY（MySQL 5.7+ 已移除）

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

方法三：DELETE JOIN 保留最小 ID

DELETE p1 FROM person_tbl p1
JOIN person_tbl p2
  ON p1.last_name = p2.last_name AND p1.first_name = p2.first_name
  AND p1.id > p2.id;

效能與選型建議