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

MySQL LOAD DATA INFILE 導入文本文件實戰指南

編輯：MySQL 綜合教學

MySQL LOAD DATA INFILE 導入文本文件實戰指南

LOAD DATA INFILE 是 MySQL 批量導入數據的最高效方式，速度遠超 INSERT 語句（通常快 20 倍以上），適合從 CSV、TSV、固定寬度文本等格式文件快速載入數據。本文通過完整實例演示語法、參數詳解、常見坑點與優化技巧。

實戰演示：導入商品數據

1. 準備目標表

CREATE TABLE `shop` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `supplier_name` VARCHAR(30) NOT NULL,
  `sort` CHAR(1) NOT NULL,
  `count` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 準備文本文件 `D:/import.txt`

字段間以 Tab 鍵（\t） 分隔，行以換行符結束，無表頭：

6	bag	180	jd	A	500
7	ipad air2	3500	tmall	B	500
8	Sony Camera	1800	jd	B	200
9	knife	6	tmall	A	500
10	shoes	300	jd	B	80

3. 執行導入命令

mysql> LOAD DATA INFILE 'D:/import.txt' INTO TABLE shop;
Query OK, 5 rows affected (0.08 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

4. 驗證數據

mysql> SELECT * FROM shop;
+----+-------------+---------+---------------+------+-------+
| id | name        | price   | supplier_name | sort | count |
+----+-------------+---------+---------------+------+-------+
| 1  | apple       | 2.50    | dangdang      | A    | 500   |
| 2  | book        | 8.50    | tmall         | B    | 500   |
| 3  | book        | 1.20    | jd            | B    | 200   |
| 4  | apple       | 1.50    | tmall         | A    | 500   |
| 5  | pen         | 8.50    | jd            | B    | 80    |
| 6  | bag         | 180.00  | jd            | A    | 500   |
| 7  | ipad air2   | 3500.00 | tmall         | B    | 500   |
| 8  | Sony Camera | 1800.00 | jd            | B    | 200   |
| 9  | knife       | 6.00    | tmall         | A    | 500   |
| 10 | shoes       | 300.00  | jd            | B    | 80    |
+----+-------------+---------+---------------+------+-------+
10 rows in set (0.00 sec)

原有 5 行數據 + 新導入 5 行 = 10 行，自增 ID 正常遞增。

核心語法詳解

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '"']
        [ESCAPED BY '\\']
    ]
    [LINES
        [STARTING BY '']
        [TERMINATED BY '\n']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var [, col_name_or_user_var] ...)]
    [SET col_name = expr [, col_name = expr] ...]

關鍵參數說明

參數	作用	典型值/示例
`LOCAL`	從客戶端本地讀取文件（需客戶端與服務端均啟用 `local_infile=1`），不需服務端 FILE 權限	`LOAD DATA LOCAL INFILE 'local.csv' ...`
`REPLACE / IGNORE`	主鍵/唯一鍵衝突時：`REPLACE` 覆蓋舊行，`IGNORE` 跳過新行	按業務選擇
`FIELDS TERMINATED BY`	字段分隔符	`\t` (Tab), `,` (CSV), `\|`
`FIELDS ENCLOSED BY`	字段包圍符（可選）	`'"'"'` (雙引號)
`FIELDS ESCAPED BY`	轉義字符	`'\\'` (反斜杠)
`LINES TERMINATED BY`	行結束符	`\n` (Linux), `\r\n` (Windows)
`IGNORE n LINES`	跳過前 n 行（如跳過表頭）	`IGNORE 1 LINES`
`(col1, col2, ...)`	指定文件列映射到表列（順序對應），可跳過/轉換列	`(id, name, @price) SET price=REPLACE(@price, '$', '')`
`SET col = expr`	導入時衍生計算列（用用戶變量 `@var` 承接文件列）	`SET created_at=NOW(), full_name=CONCAT(@first, ' ', @last)`

進階實例：CSV 帶表頭、字段映射、數據清洗

文件 products.csv（UTF-8，含表頭）：

"sku","product_name","price_usd","category"
"P001","Wireless Mouse","29.99","Electronics"
"P002","Mechanical Keyboard","89.50","Electronics"

LOAD DATA LOCAL INFILE 'products.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku, product_name, @price_usd, category)
SET price_cny = ROUND(@price_usd * 7.2, 2), created_at = NOW();

IGNORE 1 LINES 跳過表頭。
@price_usd 用戶變量接收原始價格，SET 計算人民幣價格並寫入 price_cny。
LOCAL 允許從應用服務器本地讀取，無需文件放在數據庫服務器。

⚠️ 常見錯誤與解決

錯誤現象	原因	解決方案
`ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement`	服務端啟用 `secure_file_priv` 限制導入目錄	1. 將文件放入 `SHOW VARIABLES LIKE 'secure_file_priv';` 顯示的目錄 2. 或配置文件設 `secure_file_priv=''` 解除限制（需重啟） 3. 或使用 `LOCAL` 從客戶端讀取（需雙端啟用 `local_infile=1`）
`ERROR 13 (HY000): Can't get stat of 'file.txt' (Errcode: 13)`	文件權限不足/路徑錯誤/SELinux 阻擋	確保 MySQL 進程用戶（通常 mysql）可讀文件；Linux 檢查 SELinux 上下文 `chcon -t mysqld_db_t file.txt`
導入數據亂碼	文件編碼與 `CHARACTER SET` 不匹配	文件編碼為何則指定何編碼（如 `CHARACTER SET utf8mb4` / `gbk` / `latin1`）
主鍵衝突報錯	文件含重複主鍵值	使用 `IGNORE` 跳過或 `REPLACE` 覆蓋；或預處理文件去重
數據截斷警告	字段長度超過表定義	調整表結構或預處理文件截斷；檢查 `Warnings` 詳情

效能優化建議

禁用索引/外鍵/唯一檢查（導入大量數據時）：

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
-- 導入命令
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;

調大 bulk_insert_buffer_size（MyISAM）或 innodb_buffer_pool_size（InnoDB）。
分批導入： 單文件超過 100MB 建議拆分，避免事務日志過大、內存壓力過高。
使用 LOAD DATA LOCAL INFILE 從應用端流式傳輸，無需在數據庫服務器落地大文件。

總結

LOAD DATA INFILE 以流式讀取、批量寫入、最小化日志特性成為 MySQL 批量導入首選。掌握 FIELDS/LINES 格式控制、IGNORE n LINES 跳過表頭、SET 衍生列、LOCAL 客戶端讀取等核心技巧，配合 secure_file_priv 權限管理與索引檢查暫停策略，即可高效、穩健完成 TB 級數據遷移與 ETL 載入任務。