重複數據是數據質量頭號殺手。本文系統梳理從表設計預防、業務層去重、SQL 查找刪除到持續治理的完整鏈路。
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) -- 組合主鍵防重
);
INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ('Jay', 'Thomas');
-- 重複時:Query OK, 0 rows affected (不報錯)
REPLACE INTO person_tbl (last_name, first_name) VALUES ('Ajay', 'Kumar');
-- 新記錄:插入;重複:刪除舊行 + 插入新行 (影響行數=2)
-- ⚠️ 自增 ID 會變更,慎用於有外鍵引用的表
CREATE TABLE person_tbl (
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
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;
user_id、phone、email、(order_id, product_id))SELECT 列表包含業務鍵 + COUNT(*)GROUP BY 業務鍵HAVING COUNT(*) > 1CREATE TABLE tmp_dedup AS
SELECT * FROM person_tbl
GROUP BY last_name, first_name; -- 保留每組第一行
DROP TABLE person_tbl;
ALTER TABLE tmp_dedup RENAME TO person_tbl;
-- 重建索引
ALTER TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
-- 自動刪除重複行,保留首行
-- ⚠️ MySQL 8.0 已移除 IGNORE 子句
-- 保留最小 ID,刪除其餘
DELETE a FROM person a
JOIN (
SELECT MIN(id) AS min_id, name
FROM person
GROUP BY name
HAVING COUNT(*) > 1
) b ON a.name = b.name AND a.id > b.min_id;
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
FROM person
)
DELETE FROM person
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
-- 1. 單字段去重,保留 rowid 最小
DELETE FROM people
WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1)
AND rowid NOT IN (SELECT MIN(rowid) FROM people GROUP BY peopleId HAVING COUNT(*) > 1);
-- 2. 多字段去重,保留 rowid 最小
DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);
-- 3. 關聯子查詢去重 (保留最大 ID)
DELETE a FROM test1 a
JOIN (
SELECT subject, RECEIVER, MAX(id) AS max_id
FROM test1 WHERE status = 0
GROUP BY subject, RECEIVER
HAVING COUNT(*) > 1
) b ON a.subject = b.subject AND a.RECEIVER = b.RECEIVER AND a.id < b.max_id;
-- 等價效果
SELECT DISTINCT last_name, first_name FROM person_tbl ORDER BY last_name;
SELECT last_name, first_name FROM person_tbl GROUP BY last_name, first_name;
pt-duplicate-key-checker:檢測冗余/重複索引pt-archiver:歸檔歷史數據