MySQL 查找並刪除重複數據全方案：單字段、多字段、存儲過程、臨時表實戰

業務系統中因缺乏唯一約束、並發寫入、數據遷移等原因產生重複數據是常態。本文提供單字段去重、多字段組合去重、大表分批去重、存儲過程自動化四大方案，附實測 SQL、性能對比、避坑指南。

場景定義

表 questions：question_id (PK)、question_title、question_scope、peopleId。需保留最早插入記錄 (最小 question_id)。

方案一：單字段去重 (question_title)

1. 查找重複

SELECT * FROM questions
WHERE question_title IN (
    SELECT question_title FROM questions
    GROUP BY question_title HAVING COUNT(*) > 1
);

2. 刪除重複 (保留最小 ID)

DELETE FROM questions
WHERE question_id NOT IN (
    SELECT MIN(question_id) FROM questions
    GROUP BY question_title HAVING COUNT(*) > 1
)
AND question_title IN (
    SELECT question_title FROM questions
    GROUP BY question_title HAVING COUNT(*) > 1
);

方案二：多字段組合去重 (question_title + question_scope)

1. 標準刪除語句 (MySQL 不支持直接引用同表子查詢刪除)

-- 會報錯: You can't specify target table 'questions' for update in FROM clause
DELETE FROM questions
WHERE (question_title, question_scope) IN (
    SELECT question_title, question_scope FROM questions
    GROUP BY question_title, question_scope HAVING COUNT(*) > 1
)
AND question_id NOT IN (
    SELECT MIN(question_id) FROM questions
    GROUP BY question_title, question_scope HAVING COUNT(*) > 1
);

2. 臨時表中轉法 (通用、穩健)

CREATE TEMPORARY TABLE tmp_dup AS
SELECT question_id FROM questions
WHERE (question_title, question_scope) IN (
    SELECT question_title, question_scope FROM questions
    GROUP BY question_title, question_scope HAVING COUNT(*) > 1
)
AND question_id NOT IN (
    SELECT MIN(question_id) FROM questions
    GROUP BY question_title, question_scope HAVING COUNT(*) > 1
);

DELETE FROM questions WHERE question_id IN (SELECT question_id FROM tmp_dup);
DROP TEMPORARY TABLE tmp_dup;

3. 多表 JOIN 刪除法 (MySQL 8.0+ CTE / 衍生表物化)

DELETE q1 FROM questions q1
JOIN (
    SELECT question_title, question_scope, MIN(question_id) AS keep_id
    FROM questions
    GROUP BY question_title, question_scope
    HAVING COUNT(*) > 1
) q2 ON q1.question_title = q2.question_title
   AND q1.question_scope = q2.question_scope
   AND q1.question_id > q2.keep_id;

方案三：大表分批去重 (避免長事務鎖表、Binlog 膨脹)

-- 存儲過程：按主鍵範圍分批處理
DELIMITER //
CREATE PROCEDURE batch_dedupe()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE min_id, max_id, batch_size BIGINT DEFAULT 10000;
    DECLARE cur_min, cur_max BIGINT;

    SELECT MIN(question_id), MAX(question_id) INTO min_id, max_id FROM questions;
    SET cur_min = min_id;

    WHILE cur_min <= max_id DO
        SET cur_max = cur_min + batch_size - 1;

        DELETE q1 FROM questions q1
        JOIN (
            SELECT question_title, question_scope, MIN(question_id) AS keep_id
            FROM questions
            WHERE question_id BETWEEN cur_min AND cur_max
            GROUP BY question_title, question_scope
            HAVING COUNT(*) > 1
        ) q2 ON q1.question_title = q2.question_title
           AND q1.question_scope = q2.question_scope
           AND q1.question_id > q2.keep_id
           AND q1.question_id BETWEEN cur_min AND cur_max;

        SET cur_min = cur_max + 1;
        DO SLEEP(0.1);  -- 降低主從壓力
    END WHILE;
END//
DELIMITER ;

CALL batch_dedupe();

方案四：存儲過程通用去重框架 (游標遍歷分組)

DELIMITER //
CREATE PROCEDURE dedupe_by_columns(IN tbl VARCHAR(64), IN cols VARCHAR(255), IN pk VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_cols, v_pk, v_tbl VARCHAR(255);
    DECLARE v_sql TEXT;
    DECLARE cur CURSOR FOR
        SELECT cols, pk, tbl;  -- 簡化示意，實際需動態構建
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 此處為框架示意，生產環境建議用 pt-archiver / pt-duplicate-key-checker
    -- 或按方案三分批 DELETE JOIN 實現
END//
DELIMITER ;

⚠️ 建議：通用動態 SQL 複雜且難調試，**生產環境優先用 Percona Toolkit pt-archiver --purge --where 或 pt-duplicate-key-checker**，或按方案三手寫分批 DELETE JOIN。

實測數據驗證 (樣本 23 行)

-- 建表與數據見原文
-- 查找重複 (保留最小 ID)
SELECT DISTINCT MIN(id) AS id FROM t1 GROUP BY name, add HAVING COUNT(1) > 1;
-- 結果: 1, 6, 9, 12, 19, 21

-- 查找所有重複行
SELECT t1.* FROM t1
JOIN (
    SELECT name, add FROM t1 GROUP BY name, add HAVING COUNT(1) > 1
) t2 ON t1.name = t2.name AND t1.add = t2.add;
-- 返回 14 行重複記錄

方案選型決策表

總結

去重核心邏輯：**分組識別重複組 → 確定保留行 (MIN/MAX PK) → 刪除其餘行**。MySQL 同表子查詢限制用「臨時表中轉」或「多表 JOIN 刪除」繞過。大表必須分批、低峰、監控主從延遲。建立唯一索引、應用層冪等寫入，才是防重根本。