業務系統中因缺乏唯一約束或並發寫入,常產生重複資料。本文介紹利用 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 最大)的一條。
SELECT userid,
SUBSTRING_INDEX(GROUP_CONCAT(atime ORDER BY atime DESC), ',', 1) AS atime
FROM t1
GROUP BY userid;
原理:
GROUP_CONCAT(atime ORDER BY atime DESC):組內按時間倒序拼接成字串SUBSTRING_INDEX(..., ',', 1):取第一個逗號前的子串,即最新時間結果:每 userid 一行,含最新 atime。
group_concat_max_len 預設 1024 字節,大組可能截斷,需調大:SET SESSION group_concat_max_len = 1000000;ROW_NUMBER(),語義更清晰、效能更穩定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。
-- 查找重複
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
);
-- 查找重複
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
);
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、外鍵、權限)選對去重策略,配合唯一約束預防,讓資料品質長期保持在可控狀態。