萬盛學電腦網

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

MySQL 字段去重與過濾技巧:DISTINCT 陷阱、GROUP BY 聚合、窗口函數現代寫法

DISTINCT 的局限性


-- 表數據
id | username | password | trdesc
1  | A        | abcdef   | QR
2  | A        | abcdef   | W34
3  | A        | bbbbbb   | AD
4  | B        | aaaaaa   | asdf

❌ 錯誤嘗試:多列 DISTINCT


SELECT DISTINCT username, password, trdesc FROM mytable;
-- 結果: 4 行全保留 (因 trdesc 不同),無法達到「username+password 去重」目的

原因: DISTINCT 作用於**整行**,所有選中列組合唯一才去重。

✅ 方案一:聚合函數 + GROUP BY (經典通用)


-- 需求: username+password 去重,保留 ID 最大的行
SELECT * FROM mytable 
WHERE id IN (
    SELECT MAX(id) FROM mytable 
    GROUP BY username, password
);

-- 保留 ID 最小
SELECT * FROM mytable 
WHERE id IN (
    SELECT MIN(id) FROM mytable 
    GROUP BY username, password
);

原理: 分組聚合定位目標主鍵 → 外層回表查完整行。

✅ 方案二:連接查詢 (大數據量更高效)


SELECT t1.* 
FROM mytable t1
JOIN (
    SELECT username, password, MAX(id) AS max_id
    FROM mytable
    GROUP BY username, password
) t2 ON t1.username = t2.username AND t1.password = t2.password AND t1.id = t2.max_id;

✅ 方案三:窗口函數 (MySQL 8.0+,最優雅高效)


-- 保留每組第一行 (按 id 降序)
WITH ranked AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY username, password ORDER BY id DESC) AS rn
    FROM mytable
)
SELECT id, username, password, trdesc
FROM ranked
WHERE rn = 1;

-- 可靈活控制保留策略: ORDER BY id ASC (保留最小)、ORDER BY created_at DESC (保留最新)

優勢: 單次掃描、無子查詢/臨時表、支持複雜排序規則、可輸出排名列供調試。

進階場景:保留最新記錄、刪除重複

保留最新 (按 created_at)


-- 窗口函數版
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY username, password ORDER BY created_at DESC) AS rn
    FROM mytable
)
SELECT * FROM ranked WHERE rn = 1;

-- 刪除重複 (保留最新)
DELETE t1 FROM mytable t1
JOIN (
    SELECT username, password, MAX(created_at) AS max_dt
    FROM mytable
    GROUP BY username, password
) t2 ON t1.username = t2.username AND t1.password = t2.password
WHERE t1.created_at < t2.max_dt;

刪除完全重複行 (全列相同)


-- 假設有主鍵 id
DELETE t1 FROM mytable t1
JOIN mytable t2 ON t1.username=t2.username AND t1.password=t2.password AND t1.trdesc=t2.trdesc
WHERE t1.id > t2.id;  -- 保留最小 id

性能對比與選擇建議

方案MySQL 版本性能可讀性適用場景
DISTINCT所有高 (單列)高單列去重、全行去重
GROUP BY + IN所有中 (子查詢)中小中表、簡單去重
JOIN 衍生表所有高 (可用索引)中大表、需關聯其他表
窗口函數 ROW_NUMBER8.0+最高 (單掃描)高**首選**,複雜去重邏輯

避坑提醒

  • GROUP BY 非聚合列需在 ONLY_FULL_GROUP_BY 模式下兼容 (8.0 默認開啟)
  • 大表 DELETE ... JOIN 易產生長鎖,建議分批 LIMIT 1000 循環刪除
  • 去重前**備份數據**,驗證保留邏輯正確性
copyright © 萬盛學電腦網 all rights reserved