您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL LOAD DATA 導入 Windows 文本文件換行符陷阱：\r\n vs \n 完美解決

編輯：MySQL 資料庫相關知識

Windows 文本文件以 \r\n (CRLF) 換行，MySQL LOAD DATA 默認以 \n (LF) 作為行分隔符，導致導入數據末尾殘留 \r (CR)，引發 DISTINCT 去重失效、字符串比較異常等詭異問題。本文還原現場、解析根因、給出標準修復參數與預防規範。

現場還原

-- 表結構
CREATE TABLE persons (id INT, name VARCHAR(50));

-- persons.txt (Windows 保存，CRLF 換行)
1[TAB]Bush[CRLF]
2[TAB]Carter[CRLF]
3[TAB]Bush[CRLF]

-- 導入
LOAD DATA LOCAL INFILE 'persons.txt' INTO TABLE persons;

-- 查詢去重
SELECT DISTINCT name FROM persons;
-- 結果：Bush 出現兩次！

根因剖析

層面	說明
文件實際行尾	`Bush\r\n`（兩字符）
MySQL 默認行分隔符	`LINES TERMINATED BY '\n'`（單字符 LF）
導入行解析	按 `\n` 切分 → 行內容為 `1\tBush\r`、`2\tCarter\r`、`3\tBush`
字段分割	默認 `FIELDS TERMINATED BY '\t'` → `name` 列值為 `Bush\r`、`Carter\r`、`Bush`
DISTINCT 判斷	`'Bush\r' != 'Bush'` → 視為兩個不同值
Linux 環境	文件原生 `\n`，無此問題

標準修復：顯式指定行分隔符

LOAD DATA LOCAL INFILE 'persons.txt' 
INTO TABLE persons 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\r\n';  -- 關鍵：聲明 Windows 換行符

執行後 SELECT DISTINCT name 正確返回 Bush、Carter 各一行。

進階：跨平台兼容導入腳本

<?php
function loadDataInfile($pdo, $table, $filePath, $fieldsTerminated = "\t", $linesTerminated = null) {
    // 自動檢測行尾符
    if ($linesTerminated === null) {
        $sample = file_get_contents($filePath, false, null, 0, 1024);
        $linesTerminated = (strpos($sample, "\r\n") !== false) ? "\r\n" : "\n";
    }
    
    $sql = "LOAD DATA LOCAL INFILE ? INTO TABLE `$table` 
            FIELDS TERMINATED BY ? 
            LINES TERMINATED BY ?";
    $stmt = $pdo->prepare($sql);
    // PDO::MYSQL_ATTR_LOCAL_INFILE 需在連接屬性開啟
    $stmt->execute([$filePath, $fieldsTerminated, $linesTerminated]);
    return $stmt->rowCount();
}
?>

預防規範：源頭統一行尾符

生成端統一 LF：數據導出腳本/ETL 強制輸出 \n（fwrite($fp, $line . "\n")），無論運行平台。
Git 規範：.gitattributes 設置 * text=auto eol=lf，倉庫內統一 LF，checkout 時按平台轉換。
編輯器設置：VS Code / IDE 默認換行符設為 LF。
導入腳本強制參數：所有 LOAD DATA 腳本必須顯式寫出 LINES TERMINATED BY，禁用默認值。

參數	默認值	Windows 文件建議值	Linux 文件建議值
`FIELDS TERMINATED BY`	`\t`	`\t` (或 `,` CSV)	`\t`
`FIELDS ENCLOSED BY`	`''`	`'"'"` (CSV)	`'"'"`
`FIELDS ESCAPED BY`	`\\`	`\\`	`\\`
`LINES TERMINATED BY`	`\n`	\r\n	`\n`
`LINES STARTING BY`	`''`	`''`	`''`

總結

LOAD DATA 導入 Windows 文件必須顯式指定 LINES TERMINATED BY '\r\n'。根因是默認 \n 無法匹配 \r\n，導致 \r 殘留進字段值，破壞去重、比較、Join 等語義。源頭統一 LF、腳本強制參數、自動檢測兼容，三層防線徹底消除此類隱性數據質量缺陷。