萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

MySQL 教程:將 CSV/文字檔資料高效匯入資料表

方法一:LOAD DATA INFILE (最快、原生)

LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE `target_table`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 跳過標題列

優點:速度極快 (直接由引擎讀取)、支援平行匯入 (MySQL 8.0+)、記憶體效率高。
注意:需 FILE 權限、檔案必須在伺服器本地、secure_file_priv 限制目錄。

方法二:PHP 腳本逐行匯入 (適用無 FILE 權限/虛擬主機)

<?php
// import.php
$host = 'localhost'; $user = 'user'; $pass = 'pass'; $db = 'mydb';
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$file = fopen('data.txt', 'r');
if (!$file) die("無法開啟檔案\n");

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO target_table (col1, col2, col3, col4, col5) VALUES (?, ?, ?, ?, ?)');
$batch = 0;
while (($row = fgetcsv($file, 0, ',')) !== false) {
    // 資料清洗/驗證
    $stmt->execute($row);
    if (++$batch % 1000 === 0) {
        $pdo->commit();
        $pdo->beginTransaction();
        echo "已匯入 $batch 筆...\n";
    }
}
$pdo->commit();
fclose($file);
echo "匯入完成\n";
?>

關鍵優化:批次交易 (Batch Transaction)、PDO::ATTR_EMULATE_PREPARES = false 使用真實預處理、fgetcsv() 處理引號/逗號跳脫。

方法三:mysqlimport 指令列工具

mysqlimport --local -u user -p --fields-terminated-by=',' --lines-terminated-by='\n' \
  --ignore-lines=1 mydb /path/to/data.txt

--local 允許客戶端讀取本地檔案傳送給伺服器,適合遠端連線匯入。

方法四:大數據量進階策略

  • 分區表:按日期/範圍分區,匯入時僅鎖定目標分區。
  • 暫停索引:ALTER TABLE t DISABLE KEYS (MyISAM) / 匯入後 ALTER TABLE t ENABLE KEYS;InnoDB 不支援,但可先刪除二級索引匯入完再建立。
  • 並行匯入:將檔案切分多份,多線程/多進程並行 LOAD DATA (MySQL 8.0 支援並行)。
  • 中間表:先匯入無索引暫存表,驗證清洗後 INSERT INTO target SELECT * FROM staging。

資料格式準備檢查清單

  • 編碼統一為 UTF-8 (含 BOM 會導致第一欄位異常)。
  • 分隔字元一致 (逗號、Tab、分號),欄位含分隔字元時需雙引號包裹。
  • 日期時間格式符合 MySQL 標準 (YYYY-MM-DD HH:MM:SS)。
  • NULL 值用 \N (LOAD DATA 預設) 或空字串 (視定義)。
  • 移除不可見控制字元 (如 \r 在 Unix 環境)。

常見錯誤排查

錯誤原因解決
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option檔案不在允許目錄移動檔案至 SHOW VARIABLES LIKE 'secure_file_priv' 顯示目錄,或使用 --local / PHP 腳本
ERROR 13 (HY000): Can't get stat of '/path/file.txt'權限不足chmod 644 file.txt、確保 mysql 進程用戶可讀
Incorrect integer value: '' for column 'id'空字串寫入整數欄位CSV 空值用 \N 或預設值,或 PHP 腳本中轉換
Duplicate entry for key 'PRIMARY'主鍵重複匯入前 TRUNCATE、或使用 REPLACE/ON DUPLICATE KEY UPDATE
copyright © 萬盛學電腦網 all rights reserved