MySQL INSERT 與 SELECT 嵌套：多表字段組合插入實戰

基礎語法：單表到單表

INSERT INTO db1.tbl1 (field1, field2)
SELECT field1, field2 FROM db2.tbl2;

進階：多表 JOIN 組合字段插入

場景

• 目標表：a(field1, field2)
• 源表：b(f1), c(f2)
• 需將 b.f1 和 c.f2 組合插入 a

解決方案：衍生表 + 別名

INSERT INTO a (field1, field2)
SELECT * FROM (
    SELECT b.f1, c.f2
    FROM b
    JOIN c ON b.id = c.b_id
) AS tb;  -- 必須有別名！

關鍵點：衍生表別名強制要求

-- 錯誤
INSERT INTO a SELECT * FROM (SELECT f1, f2 FROM b JOIN c);
-- ERROR 1248 (42000): Every derived TABLE must have its own alias

-- 正確
INSERT INTO a SELECT * FROM (SELECT f1, f2 FROM b JOIN c) AS tmp;

MySQL 強制要求衍生表（子查詢結果集）必須指定別名，否則語法錯誤。

擴展：三表及以上 JOIN

INSERT INTO target (c1, c2, c3, c4)
SELECT t1.f1, t2.f2, t3.f3, t4.f4
FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
JOIN t4 ON t3.id = t4.t3_id;

條件過濾插入

INSERT INTO a (field1, field2)
SELECT b.f1, c.f2
FROM b
JOIN c ON b.id = c.b_id
WHERE b.status = 1 AND c.type = 'A';

同表複製擴展

-- 複製表內數據並修改某字段
INSERT INTO tbl (col1, col2, status)
SELECT col1, col2, 'archived' FROM tbl WHERE create_time < '2023-01-01';

性能與最佳實踐

1. ✅ 大批量插入優先 INSERT ... SELECT，避免應用層循環
2. ✅ 目標表無索引/外鍵時速度最快，大量導入可先 Drop Index 後重建
3. ✅ 使用 /*+ MAX_EXECUTION_TIME(1000) */ 防止長鎖
4. ✅ 衍生表場景確保 JOIN 走索引，EXPLAIN 驗證
5. ⚠️ 同表自增 ID 複製會衝突，需顯式指定列表排除自增列

對比：INSERT VALUES vs INSERT SELECT