MySQL 查找與刪除重複記錄：單欄/多欄完整語法大全

場景一：單欄判斷重複 (peopleId)

查重複

SELECT * FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(*) > 1
);

刪除重複，保留 rowid 最小

DELETE FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(*) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid) FROM people
    GROUP BY peopleId HAVING COUNT(*) > 1
);

場景二：多欄判斷重複 (peopleId, seq)

查重複

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

刪除重複，保留 rowid 最小

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

效能優化建議

1. 大表先建臨時表存重複鍵值，再 JOIN 刪除，避免子查詢重複掃描。
2. 加上 LIMIT 分批刪除，防止長事務鎖表：
   DELETE ... LIMIT 1000; 循環執行至影響行數為 0。
3. 根治：設計階段加 UNIQUE INDEX，從源頭阻擋髒資料。