前言

複製 MySQL 表結構與數據是日常開發運維的高頻需求。本文對比多種方案，重點推薦兩種兼顧完整性、效率與通用性的標準做法，並分析各方案優缺點與適用場景。

方案對比速覽

方案	語法	複製內容	索引/約束	引擎/字符集	適用場景
推薦一	CREATE TABLE ... LIKE ...; INSERT ... SELECT ...	結構 + 數據	完整保留	完全一致	生產環境、跨庫、大表
推薦二	CREATE TABLE ... SELECT ...	結構 + 數據	不複製（需手動補）	使用系統默認值	臨時表、測試、不需索引
物理文件複製	停庫 + cp .frm/.MYD/.MYI	結構 + 數據 + 索引	保留	保留	僅限 MyISAM、同版本、同架構

推薦方案一：CREATE TABLE LIKE + INSERT SELECT（最通用）

-- 1. 完整複製表結構（含索引、約束、引擎、字符集、分區等）
CREATE TABLE new_table LIKE old_table;

-- 2. 複製數據（可加 WHERE 條件做篩選）
INSERT INTO new_table SELECT * FROM old_table;

優點

• Schema 100% 一致，包含主鍵、唯一鍵、外鍵、觸發器（觸發器需單獨導出）、分區定義。
• 支援跨資料庫：CREATE TABLE db2.new_table LIKE db1.old_table;。
• InnoDB/MyISAM 通用，無需停庫，線上可操作。
• 可配合 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 處理重複鍵。

大表優化技巧

• 分批提交：INSERT INTO new_table SELECT * FROM old_table WHERE id BETWEEN 1 AND 10000; 循環執行，避免長事務與大量 Undo Log。
• 禁用二進制日誌（Session 級）：SET sql_log_bin=0; 減少主從延遲與 Binlog 膨脹。
• 調大 bulk_insert_buffer_size（MyISAM）或 innodb_log_file_size（InnoDB）。

推薦方案二：CREATE TABLE SELECT（快速但不完整）

CREATE TABLE new_table SELECT * FROM old_table;
-- 必須手動補充主鍵、索引
ALTER TABLE new_table ADD PRIMARY KEY (id);

缺點

• 不複製：主鍵、唯一索引、普通索引、外鍵、AUTO_INCREMENT 屬性、DEFAULT 值、COMMENT、字符集/校對規則（使用系統默認）、存儲引擎（使用默認引擎）。
• 僅複製列定義與數據，適合建立臨時分析表、導出子集。

物理文件複製（僅限 MyISAM，不推薦常規使用）

/etc/init.d/mysql stop
cd /var/lib/mysql/db_name
cp old_table.MYD new_table.MYD
cp old_table.MYI new_table.MYI
cp old_table.frm new_table.frm   -- 5.7+ 無 .frm，需複製 .sdi
/etc/init.d/mysql start

風險

• 必須停庫，影響業務可用性。
• InnoDB 無法直接複製 .ibd 文件（需 DISCARD/IMPORT TABLESPACE 流程）。
• 版本、架構、頁大小不一致會導致損壞。

跨庫複製完整腳本範例

-- 在目標庫建立表結構
CREATE DATABASE IF NOT EXISTS target_db;
CREATE TABLE target_db.new_table LIKE source_db.old_table;

-- 分批導入數據（假設主鍵 id 連續）
SET @batch_size = 50000;
SET @max_id = (SELECT MAX(id) FROM source_db.old_table);
SET @start = 1;
WHILE @start <= @max_id DO
    INSERT INTO target_db.new_table
    SELECT * FROM source_db.old_table
    WHERE id >= @start AND id < @start + @batch_size;
    SET @start = @start + @batch_size;
    COMMIT;
END WHILE;

總結

生產環境請優先使用 CREATE TABLE LIKE + INSERT SELECT，保證 Schema 與數據完全一致；臨時分析或測試可用 CREATE TABLE SELECT 並手動補索引。物理文件複製僅限 MyISAM 且極其特殊場景，InnoDB 請使用官方 Tablespace Transport 方案。