MySQL 複製表結構與資料四大模式：CREATE TABLE ... SELECT/LIKE 實戰

MySQL 複製表是開發、測試、遷移、備份的高頻操作。本文系統梳理四大複製模式：結構+資料、僅結構、同結構資料導入、異結構欄位映射，對比 SELECT、LIKE、INSERT ... SELECT 的版本差異與適用場景。

模式一：複製結構與資料（一步到位）

CREATE TABLE new_table
SELECT * FROM old_table;

• 新表擁有相同欄位、資料，**但無主鍵、索引、AUTO_INCREMENT、預設值、引擎、字元集**（均採用預設值）。
• 適用：快速建立測試表、資料快照、異構遷移中間表。

模式二：僅複製結構（無資料）

方法 A：WHERE 條件永假（全版本支援）

CREATE TABLE new_table
SELECT * FROM old_table WHERE 1=2;

方法 B：LIKE 子句（MySQL 4.1+ / 5.0+ 完美支援）

CREATE TABLE new_table LIKE old_table;

• LIKE 完整複製：欄位定義、主鍵、索引、AUTO_INCREMENT、預設值、引擎、字元集、註釋、分區定義。
• MySQL 4.0.x 不支援 LIKE，需用方法 A。

模式三：同結構表資料導入

INSERT INTO new_table
SELECT * FROM old_table;

• 前提：new_table 結構與 old_table 完全一致（欄位數、順序、型別）。
• 可配合 WHERE 條件增量同步。

模式四：異結構表欄位映射導入

INSERT INTO new_table (col_a, col_b, col_c)
SELECT src_x, src_y, src_z
FROM old_table
WHERE condition;

• 明確列出目標欄位與來源欄位，順序一一對應。
• 支援運算式、函數轉換：SELECT UPPER(name), DATE(created_at), 1 AS status。

版本兼容性與避坑表

進階：跨資料庫/跨伺服器複製

-- 跨庫同實例
CREATE TABLE db2.new_table LIKE db1.old_table;
INSERT INTO db2.new_table SELECT * FROM db1.old_table;

-- 跨伺服器（需 FEDERATED 引擎或 mysqldump 管道）
mysqldump -h src_host -u user -p db1 old_table | mysql -h dst_host -u user -p db2

最佳實踐清單

1. 測試/開發環境：CREATE TABLE test_tbl LIKE prod_tbl; INSERT INTO test_tbl SELECT * FROM prod_tbl LIMIT 10000; 快速構建小樣本。
2. 生產遷移：CREATE TABLE new_tbl LIKE old_tbl; ALTER TABLE new_tbl ENGINE=InnoDB; INSERT ... SELECT 確保引擎一致。
3. 大表分批：INSERT INTO new_tbl SELECT * FROM old_tbl WHERE id BETWEEN 1 AND 100000; 迴圈執行，避免長事務。
4. 驗證一致性：SELECT COUNT(*), SUM(id) FROM old_tbl UNION ALL SELECT COUNT(*), SUM(id) FROM new_tbl; 快速校驗。

延伸閱讀

• MySQL 分表處理實現方法
• MySQL 授權表使用示例
• MySQL 記憶體表效能陷阱
• 徹底清除 MySQL 殘留註冊表項目
• MySQL user 表權限欄位完整解析