萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 重複數據處理:防止、查找、刪除全策略

MySQL 重複數據治理全攻略:預防、識別、清理、維護

重複數據是數據質量頭號殺手。本文系統梳理從表設計預防、業務層去重、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 靜默忽略重複

INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ('Jay', 'Thomas');
-- 重複時:Query OK, 0 rows affected (不報錯)

REPLACE INTO 覆蓋式插入

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;

通用識別模板

  1. 確定業務鍵列(如 user_id、phone、email、(order_id, product_id))
  2. SELECT 列表包含業務鍵 + COUNT(*)
  3. GROUP BY 業務鍵
  4. HAVING COUNT(*) > 1

去重刪除策略

策略一:創建新表替換(推薦大表、索引重建)

CREATE 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 ADD PRIMARY KEY (MySQL 5.7 前)

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);
-- 自動刪除重複行,保留首行
-- ⚠️ MySQL 8.0 已移除 IGNORE 子句

策略三:自連接刪除(保留最小/最大 ID)

-- 保留最小 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;

策略四:ROW_NUMBER() 窗口函數 (MySQL 8.0+)

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;

查詢去重:DISTINCT vs GROUP BY

-- 等價效果
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;

持續治理體系

  1. 數據字典:明確每張表業務主鍵、唯一鍵定義
  2. 入口攔截:應用層幂等性設計(唯一業務流水號、分佈式鎖)
  3. 定期巡檢:每日/每周跑重複掃描任務,告警推送
  4. 歸檔策略:歷史數據歸檔至只讀存儲,減少主表寫入壓力
  5. 變更管控:DDL 變更走審批,防止誤刪唯一約束

工具推薦

  • pt-duplicate-key-checker:檢測冗余/重複索引
  • pt-archiver:歸檔歷史數據
  • 數據質量平台:Great Expectations、Deequ、自研規則引擎
copyright © 萬盛學電腦網 all rights reserved