MySQL 刪除表中重複記錄：臨時表法與窗口函數法

業務表 folder 因缺乏唯一約束，出現重複資料 (依 serverIp, userId, name, path 判斷)。本文提供兩種去重方案：MySQL 5.x 相容的臨時表法、MySQL 8.0+ 窗口函數法，並說明去重後必須重建的索引/外鍵/主鍵。

方案一：臨時表法 (MySQL 5.0+ 通用)

-- 1. 建立去重後的臨時表 (保留最小 id)
CREATE TABLE folder_tmp AS
SELECT * FROM folder
GROUP BY serverIp, userId, name, path;
-- MySQL 允許 GROUP BY 非聚合欄位，預設取每組第一行 (最小 id)

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

-- 3. 重命名臨時表
ALTER TABLE folder_tmp RENAME TO folder;

-- 4. 【關鍵】重建主鍵、索引、外鍵、AUTO_INCREMENT
ALTER TABLE folder
  ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
  ADD UNIQUE INDEX uk_folder (serverIp, userId, name, path),
  ADD INDEX idx_user (userId);
-- 外鍵需參考原定義重建
-- ALTER TABLE folder ADD CONSTRAINT fk_folder_user FOREIGN KEY (userId) REFERENCES users(id);

方案二：窗口函數法 (MySQL 8.0+ 推薦，單條 SQL、無鎖表風險)

-- 1. 標記重複行 (rn > 1 為重複)
WITH ranked AS (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY serverIp, userId, name, path ORDER BY id) AS rn
    FROM folder
)
-- 2. 刪除重複行
DELETE f FROM folder f
JOIN ranked r ON f.id = r.id
WHERE r.rn > 1;

優點：原地刪除、無需鎖表重建、保留原主鍵 ID 連續性、可在線上低峰期執行 (配合 LIMIT 分批)。

方案三：新增唯一索引自動去重 (MySQL 5.7+ IGNORE 選項)

-- 嘗試加入唯一索引，自動刪除重複行 (僅保留第一行)
ALTER IGNORE TABLE folder
ADD UNIQUE INDEX uk_folder (serverIp, userId, name, path);
-- 注意：MySQL 8.0 移除 IGNORE 選項，此法失效。

去重後必做檢查清單

生產環境操作建議

• 先在備份庫/測試庫驗證腳本與結果。
• 大表 (>1 億行) 分批處理：DELETE ... WHERE id BETWEEN ... LIMIT 10000 迴圈，避免長事務阻塞。
• 維護窗口期操作，或使用 pt-online-schema-change (臨時表法) 實現無鎖去重。
• 去重前後對比關鍵業務指標 (如用戶數、文件夾數) 確保業務邏輯一致。