核心區別
| 語句 |
用途 |
目標表 |
MySQL 支援 |
SELECT ... INTO OUTFILE/DUMPFILE |
導出文件到服務器磁盤 |
無(生成文件) |
✅ 支援 |
SELECT ... INTO @var1, @var2 |
賦值給用戶變數 |
無 |
✅ 支援 |
SELECT ... INTO new_table |
建新表並插入(CTAS) |
**自動建立** |
❌ **MySQL 不支援**(SQL Server/PostgreSQL 支援) |
INSERT INTO target SELECT ... |
查詢結果插入**已存在**目標表 |
**必須預先建立** |
✅ 標準寫法 |
MySQL 正確寫法:CREATE TABLE ... AS SELECT (CTAS)
-- 建表並複製結構+數據(不複製索引/外鍵/觸發器)
CREATE TABLE new_table AS
SELECT id, name, email FROM old_table WHERE status = 'active';
-- 僅複製結構(無數據)
CREATE TABLE empty_table LIKE old_table;
-- 或
CREATE TABLE empty_table AS SELECT * FROM old_table WHERE 1=0;
INSERT INTO SELECT 進階用法
指定列映射
INSERT INTO target (col_a, col_b, col_c)
SELECT source_x, source_y, 'fixed_value'
FROM source
WHERE condition;
多表關聯插入
INSERT INTO order_summary (order_id, user_name, total_amount, item_count)
SELECT o.id, u.name, SUM(oi.price * oi.qty), COUNT(*)
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY o.id, u.name;
配合 ON DUPLICATE KEY UPDATE 實現 UPSERT
INSERT INTO daily_stats (stat_date, metric, value)
SELECT DATE(created_at), 'orders', COUNT(*)
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY DATE(created_at)
ON DUPLICATE KEY UPDATE value = VALUES(value);
常見錯誤與避坑
❌ 錯誤 1:SELECT * INTO new_table FROM old_table
ERROR 1327 (42000): Undeclared variable: new_table
-- MySQL 不支援 SELECT INTO 建表,必須用 CREATE TABLE ... AS SELECT
❌ 錯誤 2:INSERT INTO target VALUES SELECT ...
INSERT INTO target (a,b) VALUES SELECT x,y FROM src;
ERROR 1064: You have an error in your SQL syntax
-- 正確:INSERT INTO target (a,b) SELECT x,y FROM src; (無 VALUES)
❌ 錯誤 3:衍生表缺少別名
SELECT * FROM (SELECT a,b FROM t1 JOIN t2 ON t1.id=t2.id);
ERROR 1248: Every derived table must have its own alias
-- 正確:... ) AS tmp;
大數據量遷移效能優化
- 分批提交:避免超大事務鎖表、Undo Log 膨脹。
<?php
$batch = 5000;
$offset = 0;
while (true) {
$affected = $pdo->exec("
INSERT INTO target (id, data)
SELECT id, data FROM source
WHERE id > $offset ORDER BY id LIMIT $batch
");
if ($affected === 0) break;
$offset += $batch;
usleep(100000); // 100ms 間隔
}
- 禁用索引後重建(MyISAM/InnoDB 大批量導入):
ALTER TABLE target DISABLE KEYS; -- MyISAM 有效 / InnoDB 8.0+ 支援
-- 大量 INSERT ...
ALTER TABLE target ENABLE KEYS;
- LOAD DATA INFILE:比 INSERT 快 20 倍,配合
--local-infile=1。
- 並行導入:
mydumper/myloader 或按主鍵範圍分片並行。
權限要求
CREATE TABLE ... AS SELECT:需目標庫 CREATE、源表 SELECT。
INSERT INTO ... SELECT:需目標表 INSERT、源表 SELECT。
SELECT ... INTO OUTFILE:需 FILE 權限、secure_file_priv 目錄寫權限。
總結
- MySQL **不支援**
SELECT * INTO new_table,請用 CREATE TABLE new_table AS SELECT ...。
INSERT INTO target SELECT ... 是標準跨表複製寫法,目標表必須預建。
- 大量資料遷移:分批 + 禁用索引 + LOAD DATA + 並行,效能提升 10-50 倍。