刪除重複資料的本質:**識別重複群組 → 保留目標行(通常是最小/最大 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;
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
);
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)
-- 單欄位去重
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 核對,確認無誤再執行 DELETEALTER TABLE t_user ADD UNIQUE KEY uk_username (username);