萬盛學電腦網

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

MySQL 去除重複記錄完整指南:DISTINCT、GROUP BY、窗口函數與刪除策略

查詢去重(不修改資料)

1. DISTINCT 單欄位/多欄位

-- 單欄位去重
SELECT DISTINCT name FROM users;

-- 多欄位去重(所有欄位相同才算重複)
SELECT DISTINCT name, email FROM users;

限制:無法直接取得去重後的完整行(含 ID 等其他欄位)。

2. GROUP BY 取每組第一筆(MySQL 5.7+ 預設 ONLY_FULL_GROUP_BY 需注意)

-- 取每個 name 的第一筆(ID 最小)
SELECT * FROM users GROUP BY name;
-- ⚠️ 非聚合欄位值不確定(MySQL 5.7+ 預設報錯)

-- 正確:配合聚合函數
SELECT MIN(id) AS id, name, email FROM users GROUP BY name, email;

3. 窗口函數 ROW_NUMBER()(MySQL 8.0+ 推薦)

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
    FROM users
) t WHERE rn = 1;
  • PARTITION BY 定義分組
  • ORDER BY 定義組內排序(保留最早/最新)
  • 標準 SQL,可移植,效能佳

4. 變數模擬 ROW_NUMBER(MySQL 5.7 相容)

SELECT id, name, email FROM (
    SELECT 
        id, name, email,
        @rn := IF(@prev = name, @rn + 1, 1) AS rn,
        @prev := name
    FROM users, (SELECT @rn := 0, @prev := '') vars
    ORDER BY name, id
) t WHERE rn = 1;

刪除重複資料(修改資料)

策略:保留最小 ID,刪除其餘

-- 方法 1:自連接刪除(高效、標準)
DELETE t1 FROM users t1
INNER JOIN (
    SELECT name, MIN(id) AS min_id
    FROM users
    GROUP BY name
    HAVING COUNT(*) > 1
) t2 ON t1.name = t2.name AND t1.id > t2.min_id;
-- 方法 2:NOT IN 子查詢(需包裝一層繞過 MySQL 限制)
DELETE FROM users
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id FROM users GROUP BY name
    ) tmp
);

多欄位組合重複(如 name + email)

DELETE t1 FROM users t1
INNER JOIN (
    SELECT name, email, MIN(id) AS min_id
    FROM users
    GROUP BY name, email
    HAVING COUNT(*) > 1
) t2 ON t1.name = t2.name AND t1.email = t2.email AND t1.id > t2.min_id;

查找重複資料(核對用)

查所有重複行(含保留行)

SELECT t1.* FROM users t1
INNER JOIN (
    SELECT name, email FROM users
    GROUP BY name, email
    HAVING COUNT(*) > 1
) t2 ON t1.name = t2.name AND t1.email = t2.email;

查待刪除行(除保留行外)

SELECT t1.* FROM users t1
INNER JOIN (
    SELECT name, email, MIN(id) AS min_id FROM users
    GROUP BY name, email
    HAVING COUNT(*) > 1
) t2 ON t1.name = t2.name AND t1.email = t2.email
WHERE t1.id > t2.min_id;

完整測試案例

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(10), age INT
);
INSERT INTO t_user(username, age) VALUES 
('aaa',20),('aaa',20),('bbb',20),('bbb',20),
('ccc',20),('ccc',20),('ddd',20),('ddd',20);

-- 刪除重複,保留最大 ID
DELETE t_user FROM t_user
INNER JOIN (
    SELECT id FROM t_user GROUP BY username HAVING COUNT(*) > 1
) t2 ON t_user.id = t2.id;

-- 結果:id 2,4,6,8 保留

效能與最佳實踐

  • 重複判斷欄位必須加索引:ADD INDEX idx_name (name) 或 ADD UNIQUE KEY uk_name (name) 防重複
  • 大表分批刪除:DELETE ... LIMIT 5000 循環,避免長事務鎖表
  • 刪除前必先 SELECT 核對,確認無誤再執行
  • 考慮 INSERT IGNORE / ON DUPLICATE KEY UPDATE 從源頭防重
  • MySQL 8.0+ 首選 ROW_NUMBER(),語義清晰、效能穩定
copyright © 萬盛學電腦網 all rights reserved