PHP 使用 fgetcsv 匯入 CSV 至 MySQL 並解決中文亂碼問題

前言

大量 Excel 資料匯入 MySQL 時，常見流程：Excel → 另存為 CSV → PHP fgetcsv() 逐行讀取 → SQL 寫入。但中文編碼問題（CSV 為 UTF-8、Big5、GBK、含/不含 BOM）常導致匯入後亂碼。本文提供完整匯入範例，並詳細說明 setlocale() 與 iconv() 雙重保障機制。

基礎匯入範例

<?php
$csvFile = 'data.csv';
$row = 1;

if (($handle = fopen($csvFile, 'r')) !== false) {
    // 逐行讀取，1000 為行緩衝長度（可省略），"," 為分隔符
    while (($data = fgetcsv($handle, 1000, ',')) !== false) {
        $num = count($data);
        echo "<p>第 $row 行有 $num 個欄位</p>";
        
        // $data 為索引陣列：[0]=>欄位1, [1]=>欄位2, ...
        for ($c = 0; $c < $num; $c++) {
            echo "欄位 $c: " . htmlspecialchars($data[$c]) . "<br>";
        }
        $row++;
    }
    fclose($handle);
}
?>

中文亂碼根因分析

完整解決方案：setlocale + iconv 雙重保障

<?php
/**
 * 將 CSV 匯入 MySQL，自動處理編碼問題
 *
 * @param string $csvPath     CSV 檔案路徑
 * @param string $table       目標資料表
 * @param PDO    $pdo         PDO 連線物件（已設定 charset=utf8mb4）
 * @param array  $columnMap   CSV 欄位索引 => 資料表欄位名 對照表
 * @param string $csvEncoding CSV 實際編碼，預設 'UTF-8'
 * @return int 成功匯入筆數
 */
function importCsvToMysql($csvPath, $table, PDO $pdo, array $columnMap, $csvEncoding = 'UTF-8') {
    // 1. 關鍵：設定 locale 讓 fgetcsv 正確處理多位元組字元
    // Linux/Mac: 'zh_TW.UTF-8', 'en_US.UTF-8', 'C.UTF-8'
    // Windows: 'Chinese_Taiwan.65001' (UTF-8), 'Chinese_Taiwan.950' (Big5)
    $localeSet = false;
    foreach (['zh_TW.UTF-8', 'en_US.UTF-8', 'C.UTF-8', 'Chinese_Taiwan.65001'] as $locale) {
        if (setlocale(LC_ALL, $locale) !== false) {
            $localeSet = true;
            break;
        }
    }
    if (!$localeSet) {
        // 最後嘗試不指定 locale（視系統預設）
        setlocale(LC_ALL, '');
    }

    $imported = 0;
    $handle = fopen($csvPath, 'r');
    if (!$handle) throw new Exception("無法開啟檔案: $csvPath");

    // 2. 讀取標題列（可選：驗證欄位順序）
    $header = fgetcsv($handle);
    if ($header === false) throw new Exception('CSV 檔案為空');

    // 移除可能的 BOM
    if ($header[0] !== '' && ord($header[0][0]) === 0xEF) {
        $header[0] = ltrim($header[0], "\xEF\xBB\xBF");
    }

    // 3. 準備 SQL（使用參數綁定防注入）
    $columns = implode(', ', array_values($columnMap));
    $placeholders = implode(', ', array_fill(0, count($columnMap), '?'));
    $sql = "INSERT INTO `$table` ($columns) VALUES ($placeholders)";
    $stmt = $pdo->prepare($sql);

    // 4. 逐行處理
    while (($row = fgetcsv($handle)) !== false) {
        // 跳過空行
        if (count(array_filter($row, fn($v) => $v !== '')) === 0) continue;

        // 對應欄位並轉碼
        $values = [];
        foreach ($columnMap as $csvIdx => $dbCol) {
            $val = $row[$csvIdx] ?? '';
            
            // 關鍵：若 CSV 非 UTF-8，轉碼為 UTF-8
            if (strtoupper($csvEncoding) !== 'UTF-8') {
                $val = iconv($csvEncoding, 'UTF-8//IGNORE', $val);
            }
            
            // 移除 BOM 殘留（防第一欄）
            if ($csvIdx === 0) {
                $val = ltrim($val, "\xEF\xBB\xBF");
            }
            
            $values[] = $val;
        }

        try {
            $stmt->execute($values);
            $imported++;
        } catch (PDOException $e) {
            error_log("CSV 匯入失敗 (行 $imported): " . $e->getMessage() . " | Data: " . json_encode($values));
            // 視需求決定是否 continue 或 throw
        }
    }

    fclose($handle);
    return $imported;
}

// 使用範例
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$columnMap = [
    0 => 'product_name',  // CSV 第 1 欄 -> product_name
    1 => 'price',
    2 => 'stock',
    3 => 'description',
];

try {
    $count = importCsvToMysql('products.csv', 'products', $pdo, $columnMap, 'UTF-8');
    echo "成功匯入 $count 筆資料";
} catch (Exception $e) {
    echo "錯誤: " . $e->getMessage();
}
?>

關鍵函式說明

setlocale(LC_ALL, 'zh_TW.UTF-8')

• 影響 fgetcsv()、strftime()、money_format() 等區域相關函式。
• 必須在 fopen() 之前 呼叫。
• Linux 需安裝對應 locale：locale-gen zh_TW.UTF-8；Windows 使用 Chinese_Taiwan.65001 (UTF-8) 或 Chinese_Taiwan.950 (Big5)。
• 若系統不支援指定 locale，setlocale() 回傳 false，程式碼中已加入多組 fallback。

iconv('Big5', 'UTF-8//IGNORE', $str)

• //IGNORE：遇到無法轉換的字元直接略過（避免中斷），或改用 //TRANSLIT 嘗試近似字元替換。
• 常見來源編碼：Big5 (繁體)、GBK/CP936 (簡體)、SJIS (日文)。

進階：大檔案分批匯入與效能優化

<?php
function importLargeCsv($csvPath, $table, PDO $pdo, array $columnMap, $batchSize = 500) {
    // ... 前置相同 ...
    
    $stmt = $pdo->prepare($sql);
    $batch = [];
    $imported = 0;
    
    while (($row = fgetcsv($handle)) !== false) {
        // ... 轉碼邏輯 ...
        $batch[] = $values;
        
        if (count($batch) >= $batchSize) {
            $pdo->beginTransaction();
            try {
                foreach ($batch as $vals) {
                    $stmt->execute($vals);
                }
                $pdo->commit();
                $imported += count($batch);
                $batch = [];
            } catch (Exception $e) {
                $pdo->rollBack();
                error_log("批次寫入失敗: " . $e->getMessage());
                throw $e;
            }
        }
    }
    
    // 處理剩餘不足一批的資料
    if ($batch) {
        $pdo->beginTransaction();
        foreach ($batch as $vals) $stmt->execute($vals);
        $pdo->commit();
        $imported += count($batch);
    }
    
    return $imported;
}
?>

常見問題排查

1. 第一欄位多出 BOM：匯入前用編輯器另存為「UTF-8 無 BOM」，或程式碼中 ltrim($val, "\xEF\xBB\xBF") 移除。
2. 欄位內含換行符：fgetcsv() 原生支援雙引號包裹的跨行欄位，確保 CSV 產出符合 RFC 4180。
3. 分隔符非逗號：第三參數指定，如分號 fgetcsv($handle, 0, ';')。
4. Windows 系統 setlocale 失效：嘗試 setlocale(LC_ALL, 'Chinese_Taiwan.65001') 或升級 PHP 7.1+（改善 UTF-8 locale 支援）。