場景

表 folder 無主鍵或主鍵非業務唯一鍵，導致 (serverIp, userId, name, path) 組合出現重複，需保留每組第一筆（最小 id）。

經典臨時表法（MySQL 5.x 通用）

-- 1. 建立去重臨時表（按業務鍵分組，保留最小 id）
CREATE TABLE folder_tmp AS
SELECT * FROM folder
GROUP BY serverIp, userId, name, path;

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

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

-- 4. 重建索引、主鍵、外鍵（臨時表不保留）
ALTER TABLE folder ADD PRIMARY KEY (id);
-- ... 其他索引 ...

**注意**：GROUP BY 配合 SELECT * 在 ONLY_FULL_GROUP_BY 模式下會報錯，需改用聚合函數或關閉該模式。

現代窗口函數法（MySQL 8.0+）

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY serverIp, userId, name, path ORDER BY id) AS rn
    FROM folder
)
DELETE f FROM folder f
JOIN ranked r ON f.id = r.id
WHERE r.rn > 1;

優點：單條 SQL、無鎖表風險、保留完整索引結構、可預覽刪除行：

SELECT * FROM ranked WHERE rn > 1;  -- 預覽將刪除的重複行

大表分批刪除（避免長事務鎖表）

-- 每次刪 5000 行，配合睡眠
REPEAT
    DELETE f FROM folder f
    JOIN (
        SELECT id FROM folder
        WHERE (serverIp, userId, name, path) IN (
            SELECT serverIp, userId, name, path
            FROM folder
            GROUP BY serverIp, userId, name, path
            HAVING COUNT(*) > 1
        )
        ORDER BY id
        LIMIT 5000
    ) t ON f.id = t.id;
    SELECT SLEEP(0.5);
UNTIL ROW_COUNT() = 0 END REPEAT;

預防重複的根治措施

1. **加唯一索引**：ALTER TABLE folder ADD UNIQUE KEY uk_biz (serverIp, userId, name, path);
2. **應用層冪寫**：寫入前 SELECT ... FOR UPDATE 或 INSERT ... ON DUPLICATE KEY UPDATE
3. **定期巡檢**：pt-duplicate-key-checker 或自訂腳本定期掃描

**原則：約束在資料庫層面，重複在應用層面預防，清理在維護窗口分批執行。**