PHP 實現 CSV 大文件高效導入 MySQL：LOAD DATA INFILE 實戰與避坑指南

為什麼選擇 LOAD DATA INFILE？

對於百萬行以上的 CSV，逐行 INSERT 效能極差（網絡往返、解析開銷、事務日誌）。MySQL 原生 LOAD DATA INFILE 直接由服務端讀取文件、解析、批量寫入，**速度快 10-100 倍**，且佔用極少 PHP 記憶體。

最小可用代碼

<?php
// 資料庫配置
$host = '192.168.1.10';
$user = 'root';
$pass = '11111';
$db   = 'csvimport';

$pdo = new PDO("mysql:host={$host};dbname={$db};charset=utf8mb4", $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// CSV 文件路徑（必須為 MySQL 服務端可訪問的絕對路徑）
$csvFile = '/root/bill20120813121249.csv';

// 構建 SQL
$sql = <<exec($sql);
    echo "導入成功，影響行數：{$pdo->exec('SELECT ROW_COUNT()')}";
} catch (PDOException $e) {
    die("導入失敗：" . $e->getMessage());
}
?>

參數詳細解析

權限與安全必讀

1. FILE 權限：執行用戶需擁有 FILE 權限：GRANT FILE ON *.* TO 'user'@'host';
2. secure_file_priv 限制：
   MySQL 5.6+ 預設啟用 secure_file_priv，限制導入文件目錄。查看：
   SHOW VARIABLES LIKE 'secure_file_priv';
   - 若為 NULL → 禁用導入
   - 若為 /var/lib/mysql-files/ → 文件必須放在此目錄下
   - 若為空字串 → 無目錄限制（不推薦生產環境）
   解決：將 CSV 複製到允許目錄，或修改 my.cnf 重啟服務。
3. 本地文件導入（客戶端讀取）：若 CSV 在 Web 伺服器而非 DB 伺服器，使用 LOAD DATA LOCAL INFILE，需雙端開啟 local_infile=1，PDO 連接參數加入 PDO::MYSQL_ATTR_LOCAL_INFILE => true。

進階場景：數據清洗與轉換

LOAD DATA INFILE '/path/file.csv'
INTO TABLE `orders`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@order_no, @raw_date, @amount, @status)
SET 
  `order_no` = TRIM(@order_no),
  `order_date` = STR_TO_DATE(@raw_date, '%Y/%m/%d'),
  `amount` = CAST(REPLACE(@amount, ',', '') AS DECIMAL(10,2)),
  `status` = CASE TRIM(@status) WHEN 'paid' THEN 1 ELSE 0 END;

利用用戶變數 @var 接收原始列，再在 SET 中用 MySQL 函數清洗，避免 PHP 端逐行處理。

效能對比參考（百萬行測試）

常見錯誤排查

• ERROR 29 (HY000): File not found → 路徑錯誤、權限不足、secure_file_priv 限制
• ERROR 1265 (01000): Data truncated for column → 列長度不足、編碼不匹配、數值超出範圍
• ERROR 1300 (HY000): Invalid utf8mb4 character string → CSV 實際編碼非 UTF-8，需先 iconv -f gbk -t utf8mb4 file.csv > file_utf8.csv
• 導入行數與預期不符 → 檢查 LINES TERMINATED BY 是否匹配文件實際換行符（用 hexdump -C file.csv | head 查看）

總結

LOAD DATA INFILE 是 MySQL 導入大量結構化數據的「核武器」。掌握其參數、權限、編碼、清洗技巧，能將原本小時級的導入任務壓縮至秒級。生產環境建議結合定時任務、臨時表、驗證邏輯構建穩健的 ETL 流程。