場景

同庫建立備份表、測試表、分表模板，需快速複製結構 ± 資料。

一、僅複製表結構

方法 1：CREATE TABLE ... LIKE (最推薦、最標準)

CREATE TABLE new_table LIKE source_table;
-- 完整複製：欄位定義、索引、約束、預設值、字元集、引擎、AUTO_INCREMENT 當前值
-- 不複製：資料、TRIGGER、PARTITION (5.1+ 支援 partition 複製)、DATA DIRECTORY/INDEX DIRECTORY

**優點**：原子化、語意清晰、效能極佳 (僅字典操作)。

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

CREATE TABLE new_table SELECT * FROM source_table LIMIT 0;
-- 複製：欄位名、類型、NULL/NOT NULL、DEFAULT
-- 不複製：索引、主鍵、AUTO_INCREMENT、外鍵、觸發器、分區、字元集 (預設庫字元集)

**缺點**：丟失索引/約束，**不適合生產備份表**，僅適合臨時中繼表。

方法 3：SHOW CREATE TABLE → 手動修改 → 執行

SHOW CREATE TABLE source_table\G
-- 複製輸出的 SQL，修改表名、AUTO_INCREMENT 起始值，再執行

**適用**：跨資料庫/跨實例遷移結構、需微調定義 (如改引擎、加分區) 時。

二、複製結構 + 資料

方法 1：CREATE TABLE ... SELECT * (最常用)

CREATE TABLE new_table SELECT * FROM source_table;
-- 同方法 2，但帶資料。同樣**不複製索引/主鍵/AUTO_INCREMENT**

**補救**：建表後手動加索引：ALTER TABLE new_table ADD PRIMARY KEY (id), ADD INDEX idx_name (name);

方法 2：條件複製 (靈活篩選欄位/行)

-- 指定欄位 + 條件
CREATE TABLE new_table SELECT col1, col2, col3 FROM source_table WHERE status=1;

-- 別名重命名
CREATE TABLE new_table SELECT id AS user_id, name AS user_name FROM source_table;

方法 3：先建結構 (LIKE) 再 INSERT SELECT (最嚴謹)

CREATE TABLE new_table LIKE source_table;
INSERT INTO new_table SELECT * FROM source_table;
-- 或指定欄位
INSERT INTO new_table (col1, col2) SELECT col1, col2 FROM source_table;

**優點**：完整保留索引/約束/引擎/字元集，**資料與結構零差異**。

三、分表批量建立腳本範例

# 建立 100 張分表 (inv_crawl_weibo_qq_00 ~ 99)
for i in $(seq -w 0 99); do
  mysql -uroot -p'pass' -e "
    CREATE TABLE inv_crawl_weibo_qq_$i LIKE inv_crawl_weibo_qq_template;
  " prod_db
done

# 批量 DISCARD TABLESPACE (準備導入 .ibd)
for i in $(seq -w 0 99); do
  mysql -uroot -p'pass' -e "
    ALTER TABLE inv_crawl_weibo_qq_$i DISCARD TABLESPACE;
  " prod_db
done

# 複製 .ibd 檔案 (注意權限 chown mysql:mysql)

# 批量 IMPORT TABLESPACE
for i in $(seq -w 0 99); do
  mysql -uroot -p'pass' -e "
    ALTER TABLE inv_crawl_weibo_qq_$i IMPORT TABLESPACE;
  " prod_db
done

方法選擇決策表

需求	推薦方法	關鍵指令
同庫備份表 (結構+資料+索引)	LIKE + INSERT SELECT	CREATE TABLE t_bak LIKE t; INSERT INTO t_bak SELECT * FROM t;
跨庫/跨實例遷移結構	SHOW CREATE TABLE	手動調整後執行
臨時中繼表 (僅結構、無索引)	SELECT LIMIT 0	CREATE TABLE tmp SELECT * FROM t LIMIT 0;
條件篩選建新表	CREATE TABLE ... SELECT ... WHERE	靈活指定欄位/條件
分表模板批量建表	LIKE + 循環腳本	Shell/Python 循環呼叫

避坑提醒

• CREATE TABLE ... SELECT **不複製外鍵、觸發器、分區、檢查約束** (MySQL 8.0.16+ 支援 CHECK 但 SELECT 不複製)
• AUTO_INCREMENT 值在 LIKE 會複製，在 SELECT 不會 (新表從 1 開始)
• 大表 INSERT SELECT 會產生大量 Binlog/Redo Log，建議分批 (每 1 萬/10 萬行) 或用 pt-archiver
• 跨引擎複製 (MyISAM → InnoDB) 需顯式 ALTER TABLE ... ENGINE=InnoDB