您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 綜合知識

PHP 將 CSV/文字檔資料匯入 MySQL 實作指南

編輯：PHP 綜合知識

資料格式準備

文字檔每行一筆記錄，欄位以逗號分隔（CSV 格式）：

aaa,bbb,ccc,ddd,eee
fff,ggg,hhh,iii,jjj,kkk

假設目標資料表 ip 有 5 個欄位，對應上述 5 個值。

基本匯入腳本 `loaddate.php`

<?php
$hostname = 'localhost';
$username = 'yourname';
$password = 'yourpwd';
$dbname   = 'yourdb';

$conn = mysqli_connect($hostname, $username, $password, $dbname);
if (!$conn) die('連線失敗: ' . mysqli_connect_error());

$lines = file('yourdate.txt', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
foreach ($lines as $line) {
    $fields = str_getcsv($line);  // 正確處理欄位內含逗號/引號
    if (count($fields) !== 5) continue; // 欄位數不符略過

    // 參數化查詢防 SQL 注入
    $stmt = $conn->prepare('INSERT INTO ip (col1, col2, col3, col4, col5) VALUES (?, ?, ?, ?, ?)');
    $stmt->bind_param('sssss', ...$fields); // 全視為字串，或依實際型別調整
    $stmt->execute();
    $stmt->close();
}
$conn->close();
echo 'ok!';
?>

關鍵改進點（對比原始碼）

原始寫法	問題	改進版
`mysql_*` 函數	PHP 7 移除	`mysqli_*` / PDO
`explode(',', $line)`	無法處理欄位值含逗號/換行/引號	`str_getcsv()` 或 `fgetcsv()`
直接拼接 SQL 字串	SQL 注入、欄位含引號語法錯誤	Prepared Statement + `bind_param`
無交易控制	中途失敗資料不一致	大量匯入建議啟用交易 `BEGIN/COMMIT`
無錯誤處理/記錄	靜默失敗難除錯	try-catch + error_log

大量資料優化版（PDO + 交易 + 批次執行）

<?php
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4', 'user', 'pwd', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO ip (col1, col2, col3, col4, col5) VALUES (?, ?, ?, ?, ?)');

$fp = fopen('yourdate.txt', 'r');
$count = 0;
while (($fields = fgetcsv($fp)) !== false) {
    if (count($fields) !== 5) continue;
    $stmt->execute($fields);
    if (++$count % 1000 === 0) {
        $pdo->commit();
        $pdo->beginTransaction(); // 分批提交避免長交易鎖表
    }
}
fclose($fp);
$pdo->commit();
echo "成功匯入 $count 筆";
?>

MySQL 原生 `LOAD DATA INFILE`（最高效能）

LOAD DATA LOCAL INFILE 'yourdate.txt'
INTO TABLE ip
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 若第一行為標題

需啟用 local_infile=1 (server & client)
PHP 呼叫：mysqli_query($conn, "LOAD DATA LOCAL INFILE ...") 或 PDO::MYSQL_ATTR_LOCAL_INFILE => true
速度比逐行 INSERT 快 10-100 倍

常見編碼與格式坑

BOM：UTF-8 with BOM 會導致第一欄位名稱帶入 \uFEFF → 用 fgetcsv 自動處理或手動 ltrim($line, "\xEF\xBB\xBF")
換行符：Windows CRLF / Mac CR / Linux LF → fgetcsv 自動處理
欄位數不一致：建議先用 csvlint 或 Python csv 模組預檢
日期/布林/NULL：匯入前在 PHP 端轉換為 MySQL 可接受格式（如 1970-01-01、0/1、NULL）