MySQL 查找並刪除重複資料完整方案：單欄/多欄/存儲過程/臨時表實戰

場景一：單欄判重（如 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 id IN (
    SELECT id FROM (
        SELECT id FROM questions
        WHERE question_title IN (
            SELECT question_title FROM questions
            GROUP BY question_title HAVING COUNT(*) > 1
        )
        AND id NOT IN (
            SELECT MIN(id) FROM questions
            GROUP BY question_title HAVING COUNT(*) > 1
        )
    ) AS tmp
);

MySQL 不支援直接 DELETE ... WHERE id IN (SELECT ... FROM same_table)，需套一層衍生表 AS tmp 物化。

場景二：多欄組合判重（question_title + question_scope）

標準刪除語句（同樣需衍生表）

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
);

臨時表法（避開衍生表限制，效能更佳）

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;

場景三：存儲過程游標法（適合極大表分批）

DELIMITER //
CREATE PROCEDURE dedup_big_table()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id BIGINT;
    DECLARE v_cnt INT;
    DECLARE cur CURSOR FOR
        SELECT id, cnt FROM (
            SELECT MIN(id) AS id, COUNT(*) AS cnt
            FROM big_table
            GROUP BY col_a, col_b
            HAVING COUNT(*) > 1
        ) AS t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_cnt;
        IF done THEN LEAVE read_loop; END IF;
        -- 刪除該組除最小 id 外的其餘行
        SET @sql = CONCAT('DELETE FROM big_table WHERE (col_a, col_b) = (SELECT col_a, col_b FROM big_table WHERE id = ', v_id, ') AND id > ', v_id, ' LIMIT ', v_cnt - 1);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        -- 可加 SLEEP(0.1) 降低衝擊
    END LOOP;
    CLOSE cur;
END//
DELIMITER ;

完整驗證案例（含建表、測試資料、查重、刪重）

/* 建表 */
CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20), add VARCHAR(20)
) ENGINE=InnoDB;

/* 插入 23 筆測試資料（含多組重複） */
INSERT INTO t1(name, add) VALUES
('abc','123'),('abc','123'),('abc','321'),('abc','123'),
('xzy','123'),('xzy','456'),('xzy','456'),('xzy','456'),
('xzy','789'),('xzy','987'),('xzy','789'),
('ijk','147'),('ijk','147'),('ijk','852'),
('opq','852'),('opq','963'),('opq','741'),
('tpk','741'),('tpk','963'),('tpk','963'),
('wer','546'),('wer','546'),('once','546');

/* 查重：找出每組最小 id */
SELECT MIN(id) FROM t1 GROUP BY name, add HAVING COUNT(*) > 1;
/* 1, 6, 9, 12, 19, 21 */

/* 查重：所有重複行 */
SELECT t1.* FROM t1
JOIN (SELECT name, add FROM t1 GROUP BY name, add HAVING COUNT(*) > 1) t2
  ON t1.name = t2.name AND t1.add = t2.add;

效能建議

• 判重欄位**必須建立聯合索引** INDEX idx_name_add (name, add)。
• 大表採用臨時表法或存儲過程分批，避免長事務鎖表。
• 刪除前**務必備份**或在從庫驗證腳本正確性。