MySQL SELECT INTO vs INSERT INTO SELECT：表數據複製遷移的正確姿勢

開發測試階段頻繁需要「表數據遷移/備份/構造測試集」。MySQL 支持 INSERT INTO SELECT，但不支持標準 SQL 的 SELECT INTO TABLE（會報 1327 Undeclared variable）。本文對比兩者語法、適用場景，並給出 MySQL 專用替代方案。

一、 INSERT INTO SELECT（目標表已存在）

-- 指定列映射，支持常量、表達式、函數
INSERT INTO `target_table` (`col1`, `col2`, `col3`, `static_col`)
SELECT `src_col1`, `src_col2`, UPPER(`src_col3`), 'v2.0'
FROM `source_table`
WHERE `status` = 1;

• ✅ 目標表結構、索引、約束預先存在
• ✅ 可選列插入、字段重命名、常量填充
• ✅ 支持 IGNORE/ON DUPLICATE KEY UPDATE 處理衝突

二、 SELECT INTO TABLE（目標表不存在，MySQL 不支持）

標準語法：SELECT col1, col2 INTO new_table FROM src_table;
MySQL 執行報錯：ERROR 1327 (42000): Undeclared variable: new_table

三、 MySQL 替代方案：CREATE TABLE ... SELECT

-- 一條語句：建表 + 複製數據 + 推導列類型
CREATE TABLE `new_table` 
SELECT `id`, `name`, `email` 
FROM `source_table` 
WHERE `active` = 1;

• ✅ 自動推導列定義（類型、NULL、默認值）
• ❌ 不複製：主鍵、索引、外鍵、AUTO_INCREMENT、注释、字符集
• ⚠️ 僅適用於臨時表、測試集、歸檔快照

四、 完整遷移最佳實踐（保留結構+數據）

1. 結構克隆：CREATE TABLE `new_table` LIKE `source_table`;
2. 數據複製：INSERT INTO `new_table` SELECT * FROM `source_table`;
3. 大表分批：按主鍵範圍分批 INSERT ... SELECT ... WHERE id BETWEEN ? AND ?，避免長事務、主從延遲。
4. 驗證：CHECKSUM TABLE source_table, new_table; 對比行數與校驗和。

五、 進階：跨庫/跨實例遷移

-- 同實例跨庫
INSERT INTO `db2`.`target` SELECT * FROM `db1`.`source`;

-- 不同實例：mysqldump + 管道導入（推薦）
mysqldump -h src_host -u user -p db1 source_table | mysql -h dst_host -u user -p db2

記住：MySQL 做表級複製，CREATE TABLE LIKE + INSERT SELECT 是通用標準動作；CREATE TABLE SELECT 僅作臨時探查用。