錯誤重現

UPDATE student_quiz_answer 
SET article_id = (
    SELECT qa.article_id 
    FROM student_quiz_answer sqa 
    LEFT JOIN quiz_answer qa ON sqa.answer_id = qa.answer_id 
    WHERE qa.article_id > 0
);
-- ERROR 1093 (HY000): You can't specify target table 'student_quiz_answer' for update in FROM clause

根因

MySQL 禁止 **同一條語句中同時讀取和修改同一張表** (標準 SQL 限制，防止語義模糊)。子查詢引用了目標表 student_quiz_answer。

解決方案

方案一：派生表包裝子查詢 (最通用)

UPDATE student_quiz_answer t1
JOIN (
    SELECT sqa.answer_id, qa.article_id
    FROM student_quiz_answer sqa
    LEFT JOIN quiz_answer qa ON sqa.answer_id = qa.answer_id
    WHERE qa.article_id > 0
) t2 ON t1.answer_id = t2.answer_id
SET t1.article_id = t2.article_id;

原理：子查詢在派生表 t2 中物化，不再直接引用目標表，繞過限制。

方案二：多表 UPDATE 語法 (MySQL 專有，效能最佳)

UPDATE student_quiz_answer sqa
JOIN quiz_answer qa ON sqa.answer_id = qa.answer_id
SET sqa.article_id = qa.article_id
WHERE qa.article_id > 0;

優勢：單條 SQL、無派生表物化開銷、優化器可直接使用索引 JOIN。

方案三：用戶變數中轉 (舊版兼容)

UPDATE student_quiz_answer sqa
JOIN (
    SELECT @aid := answer_id, @art := article_id
    FROM quiz_answer
    WHERE article_id > 0
) qa ON sqa.answer_id = qa.@aid
SET sqa.article_id = qa.@art;

INSERT 同類錯誤

-- 錯誤
INSERT INTO gg SET id3 = (SELECT MAX(id3) FROM gg);

-- 正確：派生表
INSERT INTO gg SET id3 = (SELECT a+1 FROM (SELECT MAX(id3) AS a FROM gg) c);

效能對比

方案	適用版本	效能	備註
多表 UPDATE JOIN	所有版本	⭐⭐⭐ 最佳	推薦首選，語法簡潔
派生表 JOIN	所有版本	⭐⭐ 良好	邏輯複雜需子查詢時
用戶變數	5.0+	⭐ 一般	舊版兼容，8.0 棄用警告

最佳實踐檢查清單

• ✅ 能用多表 UPDATE 就用，避免子查詢
• ✅ 必須子查詢時，用派生表 (SELECT ...) t 包裹
• ✅ 確保 JOIN 條件欄位有索引 (answer_id)
• ✅ 大批量更新分批 LIMIT 10000 循環，避免長事務鎖表
• ✅ 更新前 SELECT ... FOR UPDATE 驗證影響行數