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
// 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 --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。\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 |