處理 5 萬條以上記錄的文本文件導入數據庫是常見的數據遷移場景。本文詳細解析使用 PHP file() 函數逐行讀取、批量插入的完整流程，並提供內存優化、事務批量、錯誤處理、進度顯示等生產級優化方案。
<?php
// 包含數據庫連接
require_once '../inc/connect.php';
class TextFileImporter {
private $filename = 'number.txt';
private $pdo;
private $batchSize = 1000; // 批次大小
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
// 核心導入邏輯
public function import() {
if (!file_exists($this->filename)) {
throw new Exception("文件不存在: {$this->filename}");
}
// file() 將文件按行讀入數組（大文件慎用，見下文優化）
$lines = file($this->filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
if (!is_array($lines)) {
throw new Exception("文件讀取失敗或格式錯誤");
}
echo "共讀取 " . count($lines) . " 行數據
";
// 使用事務批量插入
$this->batchInsert($lines);
}
private function batchInsert(array $lines) {
$total = count($lines);
$success = 0;
$failed = 0;
$batch = [];
$this->pdo->beginTransaction();
try {
$stmt = $this->pdo->prepare("INSERT INTO wk_card (card_id) VALUES (?)");
foreach ($lines as $i => $line) {
$line = trim($line);
if (empty($line)) continue;
$batch[] = [$line];
// 達到批次大小或最後一行時執行
if (count($batch) >= $this->batchSize || $i === $total - 1) {
foreach ($batch as $params) {
try {
$stmt->execute($params);
$success++;
} catch (PDOException $e) {
$failed++;
error_log("插入失敗: {$params[0]} - " . $e->getMessage());
}
}
// 進度顯示
$processed = min($i + 1, $total);
$percent = round($processed / $total * 100, 1);
echo "進度: {$processed}/{$total} ({$percent}%) - 成功: {$success} 失敗: {$failed}
";
// 刷新輸出緩衝
if (ob_get_level()) ob_flush();
flush();
$batch = [];
}
}
$this->pdo->commit();
echo "
導入完成！總計: {$total}, 成功: {$success}, 失敗: {$failed}";
} catch (Exception $e) {
$this->pdo->rollBack();
throw new Exception("事務執行失敗: " . $e->getMessage());
}
}
}
// 使用示例
try {
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$importer = new TextFileImporter($pdo);
$importer->import();
} catch (Exception $e) {
echo "錯誤: " . $e->getMessage();
}
?>file() 會將整個文件載入內存，對於超大文件（>100MB）會導致內存耗盡。使用生成器逐行處理：
class LargeFileImporter {
private $filename;
private $pdo;
private $batchSize = 1000;
public function __construct($filename, PDO $pdo) {
$this->filename = $filename;
$this->pdo = $pdo;
}
// 生成器：逐行讀取，內存常數級
private function readLines() {
$handle = fopen($this->filename, 'r');
if (!$handle) throw new Exception("無法打開文件");
try {
while (($line = fgets($handle)) !== false) {
yield rtrim($line, "\r\n");
}
} finally {
fclose($handle);
}
}
public function import() {
$stmt = $this->pdo->prepare("INSERT INTO wk_card (card_id) VALUES (?)");
$batch = [];
$total = $success = $failed = 0;
$this->pdo->beginTransaction();
try {
foreach ($this->readLines() as $line) {
if (empty(trim($line))) continue;
$batch[] = [trim($line)];
$total++;
if (count($batch) >= $this->batchSize) {
$this->executeBatch($stmt, $batch, $success, $failed);
$batch = [];
$this->showProgress($total, $success, $failed);
}
}
// 處理剩餘數據
if ($batch) {
$this->executeBatch($stmt, $batch, $success, $failed);
}
$this->pdo->commit();
echo "完成: 總計 {$total}, 成功 {$success}, 失敗 {$failed}";
} catch (Exception $e) {
$this->pdo->rollBack();
throw $e;
}
}
private function executeBatch(PDOStatement $stmt, array $batch, &$success, &$failed) {
foreach ($batch as $params) {
try {
$stmt->execute($params);
$success++;
} catch (PDOException $e) {
$failed++;
error_log("插入失敗: " . json_encode($params) . " - " . $e->getMessage());
}
}
}
}
MySQL 原生 LOAD DATA INFILE 比 PHP 循環插入快 10-100 倍：
<?php
function loadDataInfile(PDO $pdo, $filePath, $table, $columns = []) {
// 確保文件路徑安全（防止路徑遍歷）
$realPath = realpath($filePath);
if (!$realPath || !is_readable($realPath)) {
throw new Exception("文件不可讀或不存在");
}
$columnList = $columns ? '(' . implode(',', $columns) . ')' : '';
// LOCAL 關鍵字允許客戶端本地文件
$sql = "LOAD DATA LOCAL INFILE '{$realPath}'
INTO TABLE `{$table}`
{$columnList}
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' -- 根據實際分隔符調整
LINES TERMINATED BY '\n'
IGNORE 1 LINES"; -- 如有標題行
// 需啟用 PDO::MYSQL_ATTR_LOCAL_INFILE
$pdo->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
$affected = $pdo->exec($sql);
return $affected;
}
// 使用示例
$affected = loadDataInfile($pdo, '/data/number.txt', 'wk_card', ['card_id']);
echo "導入 {$affected} 行數據";
?>| 方案 | 耗時 | 內存峰值 | 適用場景 |
|---|---|---|---|
| file() + 單條插入 | ~45秒 | ~40MB | 小數據量、簡單邏輯 |
| file() + 事務批量(1000) | ~8秒 | ~40MB | 中等數據量、需業務邏輯 |
| 生成器 + 事務批量 | ~7秒 | ~2MB | 大文件、內存敏感環境 |
| LOAD DATA INFILE | ~0.3秒 | ~1MB | 純數據導入、最高性能 |
fgets() 逐行讀取FILE 權限，PHP 進程需文件讀取權限INSERT IGNORE 或 ON DUPLICATE KEY UPDATEset_time_limit(0)、mysql.connect_timeout、Nginx/Apache 代理超時根據實際業務複雜度選擇方案：純導入用 LOAD DATA INFILE，需業務校驗用生成器+批量事務，小數據量可用簡單 file() 方案。
