MySQL 複製資料表結構與資料的三種實用方法

在開發或維護過程中，常需快速備份資料表結構與資料。本文整理三種複製結構、兩種複製結構+資料的常用技巧，並說明各方法優缺點與適用場景。

一、僅複製表結構

方法 1：CREATE TABLE ... LIKE（推薦）

CREATE TABLE new_tbl LIKE old_tbl;

• 完整複製欄位定義、索引、主鍵、引擎、字符集等屬性。
• 不複製資料、AUTO_INCREMENT 計數器、觸發器、外鍵約束（需手動處理）。

方法 2：CREATE TABLE ... SELECT ... LIMIT 0

CREATE TABLE new_tbl SELECT * FROM old_tbl LIMIT 0;

• 語法簡短，但不會複製索引、主鍵、AUTO_INCREMENT、預設值、註解等元資料。
• 僅保留欄位名稱與資料型態。

方法 3：SHOW CREATE TABLE 手動調整

SHOW CREATE TABLE old_tbl\G

• 複製輸出的 SQL，修改表名、AUTO_INCREMENT 起始值後執行。
• 彈性最高，適合跨資料庫/實例遷移或需調整結構時使用。

二、同時複製結構與資料

方法 1：CREATE TABLE ... SELECT *（全量複製）

CREATE TABLE new_tbl SELECT * FROM old_tbl;

• 一條指令完成結構+資料複製，但同樣不保留索引、主鍵、AUTO_INCREMENT。
• 適合臨時建立測試資料集。

方法 2：CREATE TABLE ... SELECT 指定欄位 WHERE 條件（彈性複製）

CREATE TABLE new_tbl SELECT col1, col2 FROM old_tbl WHERE id > 100;

• 可依條件篩選資料、僅複製所需欄位。

方法 3：先建空表再 INSERT ... SELECT（標準做法）

CREATE TABLE new_tbl LIKE old_tbl;
INSERT INTO new_tbl SELECT * FROM old_tbl;

• 完整保留索引、主鍵、約束等結構特性。
• 支援分批次寫入大量資料，配合交易控制更安全。

選擇建議