表間資料複製是資料庫開發高頻操作。本文系統梳理目標表存在/不存在、全欄位/部分欄位、條件過濾等場景的標準 SQL 寫法與效能考量。

場景一：目標表已存在、結構相同、全量複製

INSERT INTO `target_table` SELECT * FROM `source_table`;

• ✅ 簡潔高效
• ⚠️ 目標表有主鍵/唯一鍵衝突會報錯
• ⚠️ 自增主鍵會重新生成，原值不保留

場景二：目標表已存在、部分欄位複製/重新映射

INSERT INTO `target_table` (`col1`, `col2`, `col3`, `col4`)
SELECT `col1`, `col2`, `col3`, `col4` 
FROM `source_table`
WHERE `status` = 'active';

• ✅ 明確欄位映射，結構變更不易出錯
• ✅ 可加 WHERE 過濾、JOIN 關聯、表達式運算
• ✅ 目標表未列欄位將使用預設值或 NULL

場景三：目標表不存在、結構同步建立（CTAS）

-- MySQL / PostgreSQL / SQL Server / Oracle 通用語法
CREATE TABLE `new_table` AS 
SELECT * FROM `source_table`;

• ✅ 一條指令完成建表+複製
• ❌ 不複製索引、主鍵、外鍵、觸發器、分區、AUTO_INCREMENT 屬性
• ❌ 僅複製欄位定義與資料

指定欄位建表

CREATE TABLE `new_table` AS
SELECT `id`, `name`, `email`, `created_at` 
FROM `source_table`
WHERE `status` = 1;

場景四：大數據量分批複製（避免長事務、鎖升級、Binlog 膨脹）

-- 範例：每批 5000 行，按主鍵範圍分批
SET @batch_size = 5000;
SET @max_id = (SELECT MAX(`id`) FROM `source_table`);
SET @min_id = (SELECT MIN(`id`) FROM `source_table`);

WHILE @min_id <= @max_id DO
    INSERT INTO `target_table` 
    SELECT * FROM `source_table` 
    WHERE `id` >= @min_id AND `id` < @min_id + @batch_size;
    
    SET @min_id = @min_id + @batch_size;
    -- 可選：COMMIT; 釋放鎖、刷新 Binlog
END WHILE;

生產環境建議寫成存儲過程或應用層腳本執行，並監控 Innodb_row_lock_waits、Innodb_buffer_pool_wait_free 指標。

場景五：跨資料庫/跨伺服器複製

同實例跨庫

INSERT INTO `db_target`.`target_table` SELECT * FROM `db_source`.`source_table`;

異構/遠程伺服器：FEDERATED 引擎

-- 1. 建立連結遠程表的 FEDERATED 表
CREATE TABLE `remote_source` (
    `id` INT NOT NULL,
    `data` VARCHAR(255)
) ENGINE=FEDERATED
CONNECTION='mysql://user:pass@remote_host:3306/db_name/source_table';

-- 2. 像操作本地表一樣複製
INSERT INTO `local_target` SELECT * FROM `remote_source`;

異構/遠程伺服器：mysqldump 管道流（效能最優）

mysqldump -h source_host -u user -p db_name table_name \
  --single-transaction --quick --no-create-info \
  | mysql -h target_host -u user -p target_db

效能優化關鍵點

1. 停用非必要索引：複製前 DROP INDEX，複製後 CREATE INDEX，大幅加速寫入
2. 調整 InnoDB 參數：innodb_flush_log_at_trx_commit=2、innodb_log_file_size=1G 臨時調大
3. 使用 LOAD DATA INFILE：先 SELECT ... INTO OUTFILE，再 LOAD DATA INFILE，比 INSERT 快 10-20 倍
4. 平行複製：按主鍵範圍分 N 個批次，開 N 個連線並行（注意鎖衝突）

常見陷阱避坑指南

• ❌ INSERT INTO t1 SELECT * FROM t2 當 t1、t2 結構不同時 → 明確列出欄位清單
• ❌ 自增主鍵衝突 → 目標表不設自增，或 INSERT 時指定 ID
• ❌ 字元集不匹配導致亂碼 → 確保連線、表、欄位字元集一致
• ❌ 長事務導致 Undo Log 膨脹 → 分批提交、設定 innodb_max_undo_log_size
• ❌ 複製觸發器/存儲過程 → 需單獨 mysqldump --routines --triggers 匯出

表間複製看似簡單，實則涉及鎖、日誌、索引、字元集、事務隔離等核心機制。根據資料量、業務容忍度、架構拓撲選擇合適方案，是資料庫工程師的基本功。