PHP 匯入 CSV 到 MySQL 完整指南：從基礎 LOAD DATA 到大檔案分批事務處理

前言：CSV 匯入的三大挑戰

1. 編碼與格式：BOM、分隔符、引號跳脫、換行符差異。
2. 效能與記憶體：百萬行資料不可一次性載入 PHP 陣列。
3. 資料完整性：主鍵衝突、外鍵關聯、型別轉換、部分失敗回滾。

方案對比

方案一：MySQL LOAD DATA INFILE (極速匯入)

<?php
// 前置：確保 MySQL 啟用 local_infile=1，PHP PDO 設定 MYSQL_ATTR_LOCAL_INFILE => true
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'user', 'pass', [
    PDO::MYSQL_ATTR_LOCAL_INFILE => true,
]);

$csvPath = '/absolute/path/to/data.csv'; // 必須絕對路徑
$sql = <<prepare($sql);
$stmt->bindParam(':file', $csvPath, PDO::PARAM_STR);
$stmt->execute();
echo "Imported: {$stmt->rowCount()} rows\n";

關鍵參數：CHARACTER SET 指定檔案編碼、FIELDS/LINES TERMINATED BY 對應 CSV 格式、IGNORE 1 LINES 跳過標題、SET 子句處理預設值/轉換。

方案二：PHP PDO 批次 Prepared Statement (彈性最高)

<?php
function importCsvWithPdo(string $csvPath, PDO $pdo, int $batchSize = 1000): int {
    $reader = new SplFileObject($csvPath, 'r');
    $reader->setFlags(SplFileObject::READ_CSV | SplFileObject::READ_AHEAD | SplFileObject::SKIP_EMPTY);
    $reader->setCsvControl(',', '"', '\\');

    $header = null;
    $batch = [];
    $total = 0;

    $pdo->beginTransaction();
    try {
        foreach ($reader as $row) {
            if ($header === null) {
                $header = $row;
                continue;
            }
            $data = array_combine($header, $row);
            // 業務驗證/轉換
            $data['email'] = strtolower(trim($data['email']));
            $data['created_at'] = $data['created_at'] ?? date('Y-m-d H:i:s');
            $batch[] = $data;

            if (count($batch) >= $batchSize) {
                $total += insertBatch($pdo, $batch);
                $batch = [];
            }
        }
        if ($batch) $total += insertBatch($pdo, $batch);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
    return $total;
}

function insertBatch(PDO $pdo, array $batch): int {
    $cols = array_keys($batch[0]);
    $placeholders = '(' . implode(',', array_fill(0, count($cols), '?')) . ')';
    $sql = "INSERT INTO users (" . implode(',', $cols) . ") VALUES " . implode(',', array_fill(0, count($batch), $placeholders));
    // 扁平化參數
    $params = array_merge(...array_values($batch));
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->rowCount();
}

方案三：League\Csv + 批次 Upsert (現代最佳實務)

composer require league/csv

use League\Csv\Reader;
use League\Csv\Statement;

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // 真正預處理
]);

$reader = Reader::createFromPath('data.csv', 'r');
$reader->setHeaderOffset(0);
$reader->setEncoding('UTF-8');

$stmt = (new Statement())
    ->where(fn($row) => !empty($row['email'])) // 簡單驗證
    ->limit(100000); // 安全上限

$pdo->beginTransaction();
try {
    $batch = [];
    foreach ($stmt->process($reader) as $row) {
        $row['email'] = strtolower($row['email']);
        $batch[] = $row;
        if (count($batch) >= 500) {
            upsertBatch($pdo, $batch);
            $batch = [];
        }
    }
    if ($batch) upsertBatch($pdo, $batch);
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

function upsertBatch(PDO $pdo, array $batch): void {
    $cols = array_keys($batch[0]);
    $updateCols = array_diff($cols, ['id', 'email']); // 唯一鍵不更新
    $sql = "INSERT INTO users (" . implode(',', $cols) . ") VALUES " .
           implode(',', array_fill(0, count($batch), '(' . implode(',', array_fill(0, count($cols), '?')) . ')')) .
           " ON DUPLICATE KEY UPDATE " .
           implode(',', array_map(fn($c) => "$c=VALUES($c)", $updateCols));
    $params = array_merge(...array_values($batch));
    $pdo->prepare($sql)->execute($params);
}

編碼與格式檢查清單

• ☐ 檔案編碼 (UTF-8/BIG5/GBK) → CHARACTER SET / setEncoding()
• ☐ BOM 處理 → fopen 讀前 3 bytes 判斷
• ☐ 分隔符 (逗號/分號/Tab) → FIELDS TERMINATED BY / setCsvControl()
• ☐ 引號字元 (雙引號/單引號) → ENCLOSED BY / setCsvControl()
• ☐ 換行符 (LF/CRLF) → LINES TERMINATED BY (MySQL 自動處理)
• ☐ 標題列存在/缺失 → IGNORE 1 LINES / setHeaderOffset()

大檔案 (>100MB) 進階優化

1. 分割檔案：split -l 100000 big.csv part_，平行匯入多張表再合併。
2. 停用索引：匯入前 ALTER TABLE users DISABLE KEYS (MyISAM) 或 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; (InnoDB)，匯入後重建。
3. 直接寫入 .ibd (InnoDB Transportable Tablespace)：極端大檔案專用。
4. 監控進度：pv big.csv | mysql ... 或 PHP 端記錄行數推送進度條。

完整專案 (含 Laravel Command、Symfony Console、進度條、錯誤行記錄) 見 GitHub。