MySQL LOAD DATA 導入 Windows 文本換行符 \r\n 導致 DISTINCT 重複的根因與修復

使用 LOAD DATA INFILE 導入 Windows 格式文本（\r\n 換行）時，MySQL 默認以 \n 分隔行，導致 \r 殘留在字段末尾，引發 DISTINCT 去重失效、字符串比較異常。本文深度剖析根因並給出標準修復方案。

問題複現

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

-- persons.txt (Windows CRLF)
1	Bush
2	Carter
3	Bush

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

-- 查詢異常
SELECT DISTINCT name FROM persons;
-- 結果：Bush, Carter, Bush  （Bush 出現兩次！）

根因分析

• Windows 換行符 = \r\n (0x0D 0x0A)
• MySQL LOAD DATA 默認 LINES TERMINATED BY '\n'
• 行分割時僅消費 \n，\r 作為字段內容殘留在最後一列（此處 name）
• Bush\r ≠ Bush，導致去重、比較、JOIN 全部異常

標準修復方案

LOAD DATA LOCAL INFILE 'persons.txt'
INTO TABLE persons
LINES TERMINATED BY '\r\n';

完整參數模板（生產建議）

LOAD DATA LOCAL INFILE '/path/file.txt'
INTO TABLE target_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'        -- 欄位分隔符，默認 \t
ENCLOSED BY '"'                  -- 引用符（可選）
ESCAPED BY '\\'                  -- 轉義符
LINES TERMINATED BY '\r\n'       -- 關鍵：指定行結束符
IGNORE 1 LINES;                  -- 跳過標題行（可選）

驗證技巧

-- 檢查是否殘留 \r
SELECT HEX(name), name FROM persons;
-- 正常：42757368 (Bush)
-- 異常：427573680D (Bush\r)

跨平台最佳實踐

1. 導出端統一輸出 LINES TERMINATED BY '\n'（Linux 風格）
2. 或導入端顯式聲明 LINES TERMINATED BY '\r\n'
3. ETL 流程中加入 dos2unix 預處理步驟
4. 使用 HEX() 例行檢查不可见字符