您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

CSV 匯入 MySQL 完整實戰：從檔案分析、建表腳本生成到 LOAD DATA INFILE 避坑

編輯：MySQL 綜合教學

CSV 匯入 MySQL 完整實戰：從檔案分析、建表腳本生成到 LOAD DATA INFILE 避坑

面對 2000 萬列的 CSV 檔案，如何快速建表並高效匯入？本文記錄完整流程：欄位分析 → PHP 生成建表 SQL → LOAD DATA INFILE 匯入 → BOM 頭處理 → 驗證查詢。

1. 分析 CSV 結構

用編輯器開啟 CSV 首行 (Header)，統計欄位數與名稱。範例欄位：

Name CardNo Descriot CtfTp CtfId Gender Birthday Address Zip Dirty District1 District2 District3 District4 District5 District6 FirstNm LastNm Duty Mobile Tel Fax EMail Nation Taste Education Company CTel CAddress CZip Family Version id

CSV 首行預覽

2. PHP 快速生成建表 SQL (偷懶版：全部 VARCHAR(20))

<?php
$columns_line = "Name CardNo Descriot CtfTp CtfId Gender Birthday Address Zip Dirty District1 District2 District3 District4 District5 District6 FirstNm LastNm Duty Mobile Tel Fax EMail Nation Taste Education Company CTel CAddress CZip Family Version id";
$columns = preg_split("/\s+/", trim($columns_line));
$sql = "CREATE TABLE kf_info (\n";
foreach ($columns as $i => $col) {
    $sql .= "  `$col` VARCHAR(20) NOT NULL DEFAULT ''";
    if ($i < count($columns)-1) $sql .= ",";
    $sql .= "\n";
}
$sql .= ") ENGINE=MyISAM DEFAULT CHARSET=utf8;";
echo $sql;
?>

輸出 SQL 直接在 MySQL 執行建表。
建表成功

3. LOAD DATA INFILE 高效匯入

LOAD DATA INFILE 'D:/2000W/200W-400W.csv'
INTO TABLE kf_info
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

關鍵參數：

FIELDS TERMINATED BY ','：欄位分隔符
OPTIONALLY ENCLOSED BY '"'：欄位可被雙引號包裹
ESCAPED BY '"'：跳脫字元 (雙引號內的雙引號用兩個雙引號表示)
LINES TERMINATED BY '\r\n'：Windows 換行符

4. 避坑：UTF-8 BOM 頭導致匯入失敗

執行報錯：Incorrect string value: '\xEF\xBB\xBF...'。原因：CSV 以 **UTF-8 with BOM** 儲存，前三個位元組 EF BB BF 被當作第一欄資料一部分。
BOM 報錯

解決： 用編輯器 (Notepad++, VS Code, Sublime) 另存為 **UTF-8 without BOM**，或用指令移除：

# Linux/macOS
sed -i '1s/^\xEF\xBB\xBF//' file.csv
# 或 Python
python -c "open('out.csv','wb').write(open('in.csv','rb').read()[3:])"

移除 BOM 後重新匯入，**成功導入 200 萬列**。
匯入成功

5. 驗證查詢

SELECT Name, CtfId, Birthday, Address, Mobile 
FROM kf_info 
WHERE Address LIKE '北京市%' AND Gender='F';

查詢結果

效能提示

匯入前 ALTER TABLE kf_info DISABLE KEYS; (MyISAM) 關閉索引更新，匯入後 ENABLE KEYS; 重建，大幅加速。
InnoDB 表建議調大 innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2 (匯入期間) 提升寫入效能。
生產環境**嚴禁**直接匯入敏感個資 (身分證、電話、地址)，本文純技術演示，請遵守資安法規。