-- 單欄位去重
SELECT DISTINCT name FROM users;
-- 多欄位去重(所有欄位相同才算重複)
SELECT DISTINCT name, email FROM users;
限制:無法直接取得去重後的完整行(含 ID 等其他欄位)。
-- 取每個 name 的第一筆(ID 最小)
SELECT * FROM users GROUP BY name;
-- ⚠️ 非聚合欄位值不確定(MySQL 5.7+ 預設報錯)
-- 正確:配合聚合函數
SELECT MIN(id) AS id, name, email FROM users GROUP BY name, email;
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
FROM users
) t WHERE rn = 1;
PARTITION BY 定義分組ORDER BY 定義組內排序(保留最早/最新)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;
-- 方法 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
);
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 從源頭防重ROW_NUMBER(),語義清晰、效能穩定