MySQL 批量匯入資料完整指南：INSERT SELECT、跨伺服器、大量資料優化

核心語法：INSERT INTO ... SELECT

MySQL 支援直接用一條 SQL 將查詢結果寫入目標表，是批量匯入最高效、最常用的方式。

場景一：同資料庫、結構完全相同，全欄位複製

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

場景二：同資料庫、僅複製指定欄位

INSERT INTO `target_table` (`col1`, `col2`, `col3`)
SELECT `col1`, `col2`, `col3`
FROM `source_table`
WHERE `status` = 1;  -- 可加條件篩選

注意：來源與目標欄位順序、數量、型別必須一致，否則會報錯或資料截斷。

場景三：跨資料庫（同一 MySQL 實例）

INSERT INTO `db_target`.`target_table` (`col1`, `col2`)
SELECT `col1`, `col2`
FROM `db_source`.`source_table`;

大量資料匯入效能優化策略

1. 關閉自動提交，手動控制事務

SET autocommit = 0;
INSERT INTO `target` SELECT * FROM `source` WHERE ...;
COMMIT;

減少磁碟 I/O，匯入百萬筆資料可提升 10~50 倍速度。

2. 批次大小控制（避免單次事務過大）

建議每 5,000~10,000 筆提交一次：

-- 假設有主鍵 id 可做分頁
INSERT INTO `target` SELECT * FROM `source` WHERE `id` BETWEEN 1 AND 10000;
COMMIT;
INSERT INTO `target` SELECT * FROM `source` WHERE `id` BETWEEN 10001 AND 20000;
COMMIT;
-- ...

3. 匯入前暫停索引更新（MyISAM / InnoDB 5.7+）

ALTER TABLE `target` DISABLE KEYS;  -- MyISAM 專用
-- 大量 INSERT ...
ALTER TABLE `target` ENABLE KEYS;

InnoDB 無 DISABLE KEYS，但可先刪除非唯一索引，匯入完成後重建：

ALTER TABLE `target` DROP INDEX `idx_name`;
-- 大量 INSERT ...
ALTER TABLE `target` ADD INDEX `idx_name` (`col`);

4. 使用 LOAD DATA INFILE（最快，適用 CSV/TSV 檔案）

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE `target`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`col1`, `col2`, `col3`);

• 需 FILE 權限與 secure_file_priv 目錄許可
• 比 INSERT 快 10~20 倍，適合 TB 級資料

跨伺服器資料複製方案

方案 A：mysqldump + pipe（推薦，單向同步）

# 來源伺服器導出並直接管線導入目標伺服器
mysqldump -h source_host -u user -p --single-transaction --quick db_name table_name \
| mysql -h target_host -u user -p db_name

• --single-transaction：InnoDB 一致性備份不鎖表
• --quick：逐行串流，低記憶體

方案 B：FEDERATED 引擎（雙向同步、即時查詢）

-- 在目標伺服器建立映射表
CREATE TABLE `remote_table` (
    `id` INT NOT NULL,
    `data` VARCHAR(255)
) ENGINE=FEDERATED
CONNECTION='mysql://user:pass@source_host:3306/db_name/table_name';

-- 現在可直接查詢/寫入遠端表
INSERT INTO `local_table` SELECT * FROM `remote_table` WHERE ...;

限制：不支援事務、DDL、索引維護成本高、網路延遲敏感。

方案 C：複製複寫 / CDC 工具（生產級）

• MySQL 原生 Replication（主從/主主）
• Canal / Debezium / Maxwell（Binlog 解析，異步同步至 Kafka/ES/其他 DB）

常見錯誤與排查

程式語言層面批量寫入範例

Python (PyMySQL) 批次參數化寫入

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='pass', db='db', charset='utf8mb4')
cursor = conn.cursor()

batch_size = 5000
sql = "INSERT INTO `target` (`col1`, `col2`) VALUES (%s, %s)"

for i in range(0, len(data_list), batch_size):
    batch = data_list[i:i+batch_size]
    cursor.executemany(sql, batch)
    conn.commit()
    print(f"已寫入 {i+len(batch)} 筆")

cursor.close()
conn.close()

PHP (PDO) 批次寫入

$pdo = new PDO("mysql:host=localhost;dbname=db;charset=utf8mb4", 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$batchSize = 5000;
$stmt = $pdo->prepare("INSERT INTO `target` (`col1`, `col2`) VALUES (?, ?)");

foreach (array_chunk($dataList, $batchSize) as $batch) {
    $pdo->beginTransaction();
    foreach ($batch as $row) {
        $stmt->execute([$row['col1'], $row['col2']]);
    }
    $pdo->commit();
    echo "已寫入 " . count($batch) . " 筆\n";
}

總結建議

1. 同庫小量（<10 萬）：直接 INSERT ... SELECT 最簡單
2. 同庫大量（>10 萬）：分批事務 + 暫停索引 + 並行處理
3. 跨庫/跨伺服器：mysqldump | mysql 管線最穩健
4. 極大量（GB/TB 級）：LOAD DATA INFILE 或專業 ETL/CDC 工具
5. 生產環境：務必先在測試環境驗證資料完整性與效能，制定回滾方案