萬盛學電腦網

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

MySQL 刪除重複資料記錄 SQL 語句大全(保留一條/按條件刪除)

核心思路

刪除重複資料的本質:**識別重複群組 → 保留目標行(通常是最小/最大 ID) → 刪除其餘**。

場景一:單一欄位判斷重複(如 username)

方法 1:自連接刪除(保留最小 ID)

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

方法 2:NOT IN 子查詢(保留最小 ID)

DELETE FROM t_user
WHERE id NOT IN (
    SELECT MIN(id) FROM t_user GROUP BY username
);

注意:MySQL 不允許同一張表在子查詢中被修改,需包裝一層:

DELETE FROM t_user
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id FROM t_user GROUP BY username
    ) AS tmp
);

場景二:多欄位組合判斷重複(如 peopleId + seq)

DELETE a FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae
    GROUP BY peopleId, seq
    HAVING COUNT(*) > 1
)
AND a.rowid NOT IN (
    SELECT MIN(rowid) FROM vitae
    GROUP BY peopleId, seq
    HAVING COUNT(*) > 1
);

註:rowid 為 Oracle/PostgreSQL 概念,MySQL 請用主鍵 id 替代。

場景三:查詢重複資料(不刪除,先核對)

查所有重複行(含第一筆)

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

查除保留行外的重複行(準備刪除的目標)

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

完整測試案例

-- 建表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
    id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(10),
    age INT(3)
);

-- 插入重複測試資料
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
) AS t2 ON t_user.id = t2.id;

-- 結果:每個 username 保留最後一筆(id 2,4,6,8)

DISTINCT 去重查詢(僅查詢,不刪除)

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

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

-- 取得去重後的完整行(取每組第一筆)
SELECT * FROM t_user GROUP BY username;
-- 或(MySQL 8.0+ 窗口函數)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY username ORDER BY id) AS rn
    FROM t_user
) t WHERE rn = 1;

效能提示

  • 重複判斷欄位**務必加索引**,否則大表群組/連接極慢
  • 大量刪除建議分批次:DELETE ... LIMIT 10000 循環執行
  • 刪除前先 SELECT 核對,確認無誤再執行 DELETE
  • 考慮建立唯一索引防止未來重複:ALTER TABLE t_user ADD UNIQUE KEY uk_username (username);
copyright © 萬盛學電腦網 all rights reserved