萬盛學電腦網

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

MySQL SELECT INTO 與 INSERT INTO SELECT 用法詳解與避坑指南

核心區別

語句 用途 目標表 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;

大數據量遷移效能優化

  1. 分批提交:避免超大事務鎖表、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 間隔
    }
  2. 禁用索引後重建(MyISAM/InnoDB 大批量導入):
    ALTER TABLE target DISABLE KEYS;  -- MyISAM 有效 / InnoDB 8.0+ 支援
    -- 大量 INSERT ...
    ALTER TABLE target ENABLE KEYS;
  3. LOAD DATA INFILE:比 INSERT 快 20 倍,配合 --local-infile=1。
  4. 並行導入: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 倍。
copyright © 萬盛學電腦網 all rights reserved