Windows 環境 LOAD DATA 實戰範例
本地客戶端 (Windows) 透過 LOAD DATA LOCAL INFILE 將文字檔導入遠端/本地 MySQL，需注意路徑格式、行終止符、標題行處理。
環境與資料
- 目標表
pet：欄位 name, owner, species, sex, birth, death。
- 資料檔
D:/data.txt (UTF-8 編碼)，欄位以 Tab 分隔，行尾 \r\n，第一行標題，\N 代表 NULL。
導入命令
LOAD DATA LOCAL INFILE 'D:/data.txt'
INTO TABLE pet
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
參數解析
| 參數 | 值 | 說明 |
|---|
| LOCAL | 是 | 客戶端讀檔，需 mysql --local-infile=1 啟動 |
| FIELDS TERMINATED BY | '\t' | 欄位分隔符 (Tab)，CSV 用 ',' |
| LINES TERMINATED BY | '\r\n' | Windows 行尾 (CRLF)，Linux 用 '\n' |
| IGNORE 1 LINES | 1 | 跳過第一行標題 |
| NULL 處理 | \N | 檔案中用 \N (兩字元) 表示 NULL，MySQL 自動識別 |
客戶端啟動參數
# 必須加 --local-infile=1
mysql --local-infile=1 -uroot -p -h 127.0.0.1 database_name
# 或在 my.cnf [mysql] 區段永久啟用
[mysql]
local-infile=1
常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
ERROR 1148 (42000): The used command is not allowed with this MySQL version | 客戶端未啟用 --local-infile=1 | 加啟動參數或配置文件啟用 |
ERROR 13 (HY000): Can't get stat of 'file.txt' (Errcode: 2) | 非 LOCAL 模式下伺服器找不到檔案 | 改用 LOCAL 或將檔案放至伺服器 secure_file_priv 目錄 |
| 資料亂碼 | 檔案編碼與表/連線字元集不一致 | 檔案存 UTF-8，指定 CHARACTER SET utf8mb4，連線 SET NAMES utf8mb4 |
| 行數對不上 | 行尾符錯誤 (如 Linux 檔用 \r\n) | 用 file / hexdump -C 檢查行尾，調整 LINES TERMINATED BY |
進階：使用者變數轉換 (SET 子句)
LOAD DATA LOCAL INFILE 'D:/data.txt'
INTO TABLE pet
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(name, owner, species, sex, @birth, @death)
SET birth = STR_TO_DATE(@birth, '%Y-%m-%d'),
death = CASE WHEN @death = '\N' THEN NULL ELSE STR_TO_DATE(@death, '%Y-%m-%d') END;
- 將檔案欄位讀入使用者變數
@var，再用 SET 運算轉型後寫入目標欄位。
- 支援複雜邏輯：日期格式轉換、條件賦值、衍生欄位計算。
