表 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 禁止在同一條語句中**讀取並修改同一張表**(衍生表物化除外)。
CREATE TEMPORARY TABLE tmp_del_ids (id BIGINT PRIMARY KEY);
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);
DELETE FROM students WHERE id IN (SELECT id FROM tmp_del_ids);
DROP TEMPORARY TABLE tmp_del_ids;
優勢:臨時表會話隔離、自動清理、不產生 Binlog(若 binlog_format=STATEMENT 需注意),規避 1093 錯誤。
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);
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 限制。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** 簡化代碼;大表必須**分批提交**控制事務大小與主從延遲。去重前務必備份、驗證業務主鍵邏輯、確認外鍵引用影響。