MySQL 重複數據處理大全：防止、查找、去重、刪除重複行

1. 源頭防止：約束

語句行為適用場景
INSERT IGNORE重複靜默忽略，不報錯日誌導入、數據同步
REPLACE INTO重複則刪舊插新（所有列重置）需全量覆蓋的業務
INSERT ... ON DUPLICATE KEY UPDATE重複則更新指定列（保留其他列）計數器、最後登錄時間等


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

4. 查詢結果去重
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;

方法 B：添加主鍵/唯一索引自動去重（MySQL 特有）
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY last_name, first_name ORDER BY id) AS rn
    FROM person_tbl
)
DELETE FROM person_tbl WHERE id IN (SELECT id FROM ranked WHERE rn > 1);