MySQL 去重實戰：查詢重複數據、保留首條刪除其餘、單表多欄去重完整 SQL

場景

表 employee 中 emp_name 重複，需保留每組 emp_id 最小的一條，刪除其餘重複行。

三步走 SQL

-- 1. 查詢有重複的名稱
SELECT emp_name FROM employee GROUP BY emp_name HAVING COUNT(*) > 1;

-- 2. 定位可刪除行（排除每組最小 emp_id）
SELECT t1.* FROM employee t1
WHERE t1.emp_name IN (
    SELECT emp_name FROM employee GROUP BY emp_name HAVING COUNT(*) > 1
)
AND t1.emp_id NOT IN (
    SELECT MIN(emp_id) FROM employee GROUP BY emp_name HAVING COUNT(*) > 1
);

-- 3. 執行刪除
DELETE t1 FROM employee t1
WHERE t1.emp_name IN (
    SELECT emp_name FROM employee GROUP BY emp_name HAVING COUNT(*) > 1
)
AND t1.emp_id NOT IN (
    SELECT MIN(emp_id) FROM employee GROUP BY emp_name HAVING COUNT(*) > 1
);

多欄去重擴展

若按 (dept_id, emp_name) 組合去重：

DELETE t1 FROM employee t1
WHERE (t1.dept_id, t1.emp_name) IN (
    SELECT dept_id, emp_name FROM employee
    GROUP BY dept_id, emp_name HAVING COUNT(*) > 1
)
AND t1.emp_id NOT IN (
    SELECT MIN(emp_id) FROM employee
    GROUP BY dept_id, emp_name HAVING COUNT(*) > 1
);

大表優化建議

• 先建臨時表存主鍵：CREATE TEMPORARY TABLE tmp_ids SELECT MIN(id) id FROM t GROUP BY cols HAVING COUNT(*)>1;
• 再 DELETE t FROM t LEFT JOIN tmp_ids ON t.id=tmp_ids.id WHERE tmp_ids.id IS NULL;（利用 JOIN 避免子查詢性能問題）。
• 分批刪除（LIMIT 1000 循環），避免長事務鎖表。
• 備份後操作，驗證無誤再提交。