MySQL 快速查找表中重復記錄：一條 GROUP BY 即可搞定

最通用、性能最好的單表重複檢測語句：

SELECT user_name, COUNT(*) AS cnt
FROM user_table
GROUP BY user_name
HAVING cnt > 1;

原理解析

• GROUP BY user_name 將同名行聚合。
• COUNT(*) 計算每組行數。
• HAVING cnt > 1 過濾出現次數超過 1 的組。

進階變體

多列組合判重

SELECT col_a, col_b, COUNT(*)
FROM t
GROUP BY col_a, col_b
HAVING COUNT(*) > 1;

取得完整重複行（含主鍵）

SELECT t.*
FROM t
JOIN (
    SELECT col_a, col_b
    FROM t
    GROUP BY col_a, col_b
    HAVING COUNT(*) > 1
) dup USING (col_a, col_b);

刪除重複保留最小 id

DELETE t1 FROM t t1
JOIN t t2
  ON t1.col_a = t2.col_a AND t1.col_b = t2.col_b
 AND t1.id > t2.id;

性能提示

• 被 GROUP BY 的列上建立索引可大幅加速。
• 大表建議先 SELECT ... INTO OUTFILE 導出去重鍵，再回寫刪除，避免長事務鎖表。