MySQL 複製表結構與資料完整教學：CREATE ... SELECT 與 CREATE LIKE 實戰

MySQL 資料表複製完整指南

在資料庫開發維護中，常需複製表結構與資料以備測試、備份或遷移。MySQL 提供 CREATE ... SELECT 與 CREATE TABLE ... LIKE 兩種主要方式，本文透過完整實例詳細說明操作步驟與差異。

方法一：CREATE TABLE ... SELECT — 複製結構與資料

1. 建立來源表 Topic 並填入測試資料

mysql> CREATE TABLE Topic(
    -> TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(50) NOT NULL,
    -> InStock SMALLINT UNSIGNED NOT NULL,
    -> OnOrder SMALLINT UNSIGNED NOT NULL,
    -> Reserved SMALLINT UNSIGNED NOT NULL,
    -> Department ENUM('Classical', 'Popular') NOT NULL,
    -> Category VARCHAR(20) NOT NULL,
    -> RowUpdate TIMESTAMP NOT NULL
    -> );

mysql> INSERT INTO Topic (Name, InStock, OnOrder, Reserved, Department, Category) VALUES
    -> ('Java', 10, 5, 3, 'Popular', 'Rock'),
    -> ('JavaScript', 10, 5, 3, 'Classical', 'Opera'),
    -> ('C Sharp', 17, 4, 1, 'Popular', 'Jazz'),
    -> ('C', 9, 4, 2, 'Classical', 'Dance'),
    -> ('C++', 24, 2, 5, 'Classical', 'General'),
    -> ('Perl', 16, 6, 8, 'Classical', 'Vocal'),
    -> ('Python', 2, 25, 6, 'Popular', 'Blues'),
    -> ('Php', 32, 3, 10, 'Popular', 'Jazz'),
    -> ('ASP.net', 12, 15, 13, 'Popular', 'Country'),
    -> ('VB.net', 5, 20, 10, 'Popular', 'New Age'),
    -> ('VC.net', 24, 11, 14, 'Popular', 'New Age'),
    -> ('UML', 42, 17, 17, 'Classical', 'General'),
    -> ('www.java2s.com', 25, 44, 28, 'Classical', 'Dance'),
    -> ('Oracle', 32, 15, 12, 'Classical', 'General'),
    -> ('Pl/SQL', 20, 10, 5, 'Classical', 'Opera'),
    -> ('Sql Server', 23, 12, 8, 'Classical', 'General');
Query OK, 16 rows affected (0.00 sec)

2. 一鍵複製結構與資料到 Topic2

mysql> CREATE TABLE Topic2
    -> (
    -> TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(50) NOT NULL,
    -> InStock SMALLINT UNSIGNED NOT NULL,
    -> OnOrder SMALLINT UNSIGNED NOT NULL,
    -> Reserved SMALLINT UNSIGNED NOT NULL,
    -> Department ENUM('Classical', 'Popular') NOT NULL,
    -> Category VARCHAR(20) NOT NULL,
    -> RowUpdate TIMESTAMP NOT NULL
    -> )
    -> SELECT * FROM Topic;

關鍵點：括號內定義目標表結構，SELECT * FROM Topic 將來源資料寫入。執行後 Topic2 擁有相同結構與 16 筆資料。

⚠️ 重要限制

• 索引、主鍵、AUTO_INCREMENT 屬性不會自動複製，需在括號內顯式定義
• SELECT 可加 WHERE 條件實現條件式複製
• 可指定欄位：SELECT Name, Category FROM Topic 僅複製特定欄位

方法二：CREATE TABLE ... LIKE — 僅複製結構

當只需要空表結構（含索引、約束、預設值）時：

CREATE TABLE new_table LIKE existing_table;

實例

CREATE TABLE Topic_structure_only LIKE Topic;

產生的 Topic_structure_only 完整繼承 Topic 的所有欄位定義、索引、主鍵、AUTO_INCREMENT 等屬性，但不包含任何資料。

兩種方法對照表

進階應用場景

場景 1：建立測試資料子集

CREATE TABLE Topic_popular
(
    TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Category VARCHAR(20) NOT NULL
)
SELECT TopicID, Name, Category FROM Topic WHERE Department = 'Popular';

場景 2：跨資料庫複製

CREATE TABLE db_backup.Topic_archive LIKE db_prod.Topic;
INSERT INTO db_backup.Topic_archive SELECT * FROM db_prod.Topic;

場景 3：大表分批複製避免長鎖定

CREATE TABLE Topic_part1 LIKE Topic;
INSERT INTO Topic_part1 SELECT * FROM Topic WHERE TopicID <= 1000;
-- 重複執行不同範圍...

最佳實踐建議

1. 大表優先用 LIKE + INSERT 分批，避免長鎖定影響線上服務
2. 備份用途請用 LIKE，確保索引約束完整保留
3. ETL 轉換用 SELECT，可同時完成欄位重組、資料清洗
4. 複製後驗證：CHECKSUM TABLE 或 SELECT COUNT(*) 比對筆數

熟練掌握這兩種複製方式，能大幅提升資料庫開發、測試、維運的效率與安全性。