萬盛學電腦網

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

MySQL 重複資料去重:保留最新/最舊記錄的高效 SQL 寫法

業務系統中因缺乏唯一約束或並發寫入,常產生重複資料。本文介紹利用 GROUP_CONCAT + SUBSTRING_INDEX 保留最新記錄的技巧,以及標準的重複資料查找與刪除模式。

場景:保留每組最新的一條記錄

表結構:

CREATE TABLE t1 (
  userid INT(11) DEFAULT NULL,
  atime DATETIME DEFAULT NULL,
  KEY idx_userid (userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

樣本資料:userid 重複,需保留每用戶最新(atime 最大)的一條。

技巧寫法:GROUP_CONCAT + SUBSTRING_INDEX

SELECT userid,
       SUBSTRING_INDEX(GROUP_CONCAT(atime ORDER BY atime DESC), ',', 1) AS atime
FROM t1
GROUP BY userid;

原理:

  1. GROUP_CONCAT(atime ORDER BY atime DESC):組內按時間倒序拼接成字串
  2. SUBSTRING_INDEX(..., ',', 1):取第一個逗號前的子串,即最新時間

結果:每 userid 一行,含最新 atime。

注意事項

  • group_concat_max_len 預設 1024 字節,大組可能截斷,需調大:SET SESSION group_concat_max_len = 1000000;
  • 僅適用於「取單一欄位值」場景,需取整行資料請用下方標準寫法
  • MySQL 8.0+ 建議改用窗口函數 ROW_NUMBER(),語義更清晰、效能更穩定

標準寫法:窗口函數(MySQL 8.0+ 推薦)

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY atime DESC) AS rn
  FROM t1
)
SELECT userid, atime FROM ranked WHERE rn = 1;

優勢:可任意選取整行欄位、支援複雜排序、效能可預期、標準 SQL。

重複資料查找與刪除標準模式

1. 單欄位去重(保留最小 id)

-- 查找重複
SELECT peopleId, COUNT(*) FROM people GROUP BY peopleId HAVING COUNT(*) > 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. 多欄位去重(組合唯一)

-- 查找重複
SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

-- 刪除重複(保留 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. 查找重複但不含保留行

SELECT * 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
);

現代最佳實踐:加唯一索引防重

治本之策是在表結構層面防止重複:

-- 單欄位唯一
ALTER TABLE people ADD UNIQUE uk_peopleId (peopleId);

-- 多欄位組合唯一
ALTER TABLE vitae ADD UNIQUE uk_people_seq (peopleId, seq);
  • 寫入端捕獲 Duplicate entry 錯誤處理(忽略/更新/報錯)
  • INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO 靈活控制衝突行為

大表去重效能策略

  • 分批處理:按主鍵範圍分批 DELETE ... WHERE id BETWEEN ... AND ...
  • 新表換舊:CREATE TABLE new_t AS SELECT DISTINCT ... FROM old_t 重建表(需處理自增 ID、外鍵、權限)
  • pt-archiver/pt-online-schema-change:Percona Toolkit 線上無鎖去重/重建

選對去重策略,配合唯一約束預防,讓資料品質長期保持在可控狀態。

copyright © 萬盛學電腦網 all rights reserved