資料庫優化：刪除重複記錄僅保留單筆（多種方案對比）

問題背景

前端未去重、無唯一索引、資料遷移等原因導致表中存在重複列。需刪除重複，保留一筆。

方案一：建立臨時表去重（通用、簡單）

-- 1. 建立去重臨時表
CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM original_table;

-- 2. 刪除原表
DROP TABLE original_table;

-- 3. 重命名臨時表
RENAME TABLE tmp_dedup TO original_table;

-- 4. 加上唯一索引防止復發
ALTER TABLE original_table ADD UNIQUE KEY uk_cols (col1, col2, ...);

適用於無主鍵、全列重複的小中型表。大表會鎖表久，建議用 pt-online-schema-change 或分批處理。

方案二：利用 ROW_NUMBER() 窗口函數（MySQL 8.0+ / SQL Server / PostgreSQL / Oracle）

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY dup_col1, dup_col2 ORDER BY id) AS rn
    FROM original_table
)
DELETE FROM original_table
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

優點：單條 SQL、可指定保留哪一筆（如最新/最舊/最大 ID）。

方案三：自連接刪除（MySQL 5.7 相容寫法）

DELETE t1 FROM original_table t1
INNER JOIN original_table t2
WHERE t1.id > t2.id
  AND t1.dup_col1 = t2.dup_col1
  AND t1.dup_col2 = t2.dup_col2;

保留 id 最小的一筆。需確保 id 為主鍵或唯一欄位。

方案四：各資料庫特有技巧

SQL Server 2005+：CTE + ROW_NUMBER

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dup_col ORDER BY id) AS rn
    FROM original_table
)
DELETE FROM cte WHERE rn > 1;

Oracle：ROWID 偽欄位

DELETE FROM original_table
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM original_table GROUP BY dup_col1, dup_col2
);

Oracle 每列唯一 ROWID 可直接定位物理行。

MySQL 8.0 前無 ROW_NUMBER 時：用戶變數模擬

SET @row_num = 0, @prev_val = '';
DELETE FROM original_table
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               @row_num := IF(@prev_val = CONCAT(dup_col1, dup_col2), @row_num + 1, 1) AS rn,
               @prev_val := CONCAT(dup_col1, dup_col2)
        FROM original_table
        ORDER BY dup_col1, dup_col2, id
    ) t WHERE rn > 1
);

方案五：游標循環刪除（大表、低並發窗口）

DELIMITER //
CREATE PROCEDURE dedup_cursor()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_cnt INT;
    DECLARE cur CURSOR FOR SELECT id, cnt FROM (
        SELECT id, COUNT(*) AS cnt FROM original_table GROUP BY dup_col1, dup_col2 HAVING cnt > 1
    ) 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;
        SET @limit = v_cnt - 1;
        PREPARE stmt FROM 'DELETE FROM original_table WHERE id = ? LIMIT ?';
        EXECUTE stmt USING v_id, @limit;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

CALL dedup_cursor();

效能與選型建議