萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 百萬級數據去重刪除:臨時表中轉法避開「You can't specify target table」錯誤

故障場景

表 students 含重複 md 字段,需刪除重複行保留每組 id 最小的一行。直接刪除報錯:


DELETE FROM students
WHERE md IN (SELECT md FROM students GROUP BY md HAVING COUNT(md) > 1)
  AND id NOT IN (SELECT MIN(id) FROM students GROUP BY md HAVING COUNT(md) > 1);
-- ERROR 1093 (HY000): You can't specify target table 'students' for update in FROM clause

MySQL 禁止在同一條語句中**讀取並修改同一張表**(衍生表物化除外)。

標準解法:臨時表中轉(萬能、兼容所有版本)

  1. 建立臨時表存儲待刪除 ID:
    
    CREATE TEMPORARY TABLE tmp_del_ids (id BIGINT PRIMARY KEY);
    
  2. 寫入待刪除 ID:
    
    INSERT INTO tmp_del_ids (id)
    SELECT id FROM students
    WHERE md IN (SELECT md FROM students GROUP BY md HAVING COUNT(md) > 1)
      AND id NOT IN (SELECT MIN(id) FROM students GROUP BY md HAVING COUNT(md) > 1);
    
  3. 執行刪除:
    
    DELETE FROM students WHERE id IN (SELECT id FROM tmp_del_ids);
    
  4. 清理:
    
    DROP TEMPORARY TABLE tmp_del_ids;
    

優勢:臨時表會話隔離、自動清理、不產生 Binlog(若 binlog_format=STATEMENT 需注意),規避 1093 錯誤。

進階單條語句寫法(MySQL 8.0+ CTE / 衍生表物化)

CTE 寫法(推薦,可讀性最佳)


WITH to_delete AS (
  SELECT id FROM students
  WHERE md IN (SELECT md FROM students GROUP BY md HAVING COUNT(md) > 1)
    AND id NOT IN (SELECT MIN(id) FROM students GROUP BY md HAVING COUNT(md) > 1)
)
DELETE FROM students WHERE id IN (SELECT id FROM to_delete);

衍生表物化(5.6+ 優化器自動物化)


DELETE FROM students
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM students
    WHERE md IN (SELECT md FROM students GROUP BY md HAVING COUNT(md) > 1)
      AND id NOT IN (SELECT MIN(id) FROM students GROUP BY md HAVING COUNT(md) > 1)
  ) AS derived
);
  • 內層 SELECT ... FROM (subquery) AS derived 強制物化為臨時表,繞過 1093 限制。
  • 5.6+ 默認 optimizer_switch='derived_merge=on' 可能將衍生表合併導致失效,可臨時關閉:SET SESSION optimizer_switch='derived_merge=off';。

大表分批刪除避免長事務與主從延遲


-- 1. 獲取待刪總數
SELECT COUNT(*) INTO @total FROM tmp_del_ids;
SET @batch = 5000;
SET @deleted = 0;

WHILE @deleted < @total DO
  DELETE FROM students
  WHERE id IN (SELECT id FROM tmp_del_ids LIMIT @batch);
  SET @deleted = @deleted + ROW_COUNT();
  COMMIT;  -- 釋放鎖、減少 Undo Log、緩解主從延遲
  DO SLEEP(0.1);  -- 降低 CPU 尖峰
END WHILE;

或使用 pt-archiver (Percona Toolkit) 自動化分批歸檔/刪除。

驗證去重結果


-- 剩餘唯一 md 數
SELECT COUNT(DISTINCT md) FROM students;

-- 確認無重複
SELECT md, COUNT(*) FROM students GROUP BY md HAVING COUNT(*) > 1;
-- 應返回空集

總結

MySQL 1093 錯誤源於「同語句讀寫同表」限制。**臨時表中轉**是通用、穩健、版本無關的標準解法;MySQL 8.0+ 推薦 **CTE** 簡化代碼;大表必須**分批提交**控制事務大小與主從延遲。去重前務必備份、驗證業務主鍵邏輯、確認外鍵引用影響。

copyright © 萬盛學電腦網 all rights reserved