MySQL 查詢重複記錄完整方法集

單欄位判重

-- 查詢重複（保留所有重複行）
SELECT * FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(peopleId) > 1
);

-- 刪除重複（保留最小 id）
DELETE FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(peopleId) > 1
) AND id NOT IN (
    SELECT MIN(id) FROM people
    GROUP BY peopleId HAVING COUNT(peopleId) > 1
);

多欄位組合判重

-- 查詢重複
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
);

統計重複分布

-- 單欄位
SELECT Name, COUNT(*) FROM A
GROUP BY Name HAVING COUNT(*) > 1;

-- 多欄位
SELECT Name, Sex, COUNT(*) FROM A
GROUP BY Name, Sex HAVING COUNT(*) > 1;

游標逐行處理（大量資料適用）

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT 主鍵, COUNT(*) FROM 表
    GROUP BY 主鍵 HAVING COUNT(*) > 1;

OPEN cur;
FETCH cur INTO @id, @max;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @max = @max - 1;
    SET ROWCOUNT @max;
    DELETE FROM 表 WHERE 主鍵 = @id;
    FETCH cur INTO @id, @max;
END
CLOSE cur;
SET ROWCOUNT 0;

效能建議

• 小表：直接用 GROUP BY ... HAVING COUNT(*) > 1
• 大表：建立臨時表存放重複鍵值，再 JOIN 刪除
• 刪除前務必備份，驗證保留邏輯正確
• 考慮加上 UNIQUE INDEX 防止未來重複