MySQL加载CSV文件时无法识别\N，使用STR_TO_DATE报错的解决方法

表结构 #

CREATE table LOCATION
(
Place varchar(50) not null,
Date date,
CONSTRAINT location_pk PRIMARY KEY(Place)
);

待导入文件（location.txt）内容 #

Place   Date
School  2023-03-04
Market      \N  

已尝试的LOAD DATA语句及报错 #

1. 语句1：

LOAD DATA INFILE "/xxxx/location.txt" INTO TABLE LOCATION FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES
(Place, date)
SET
Date=IF(date='\\N', NULL, STR_TO_DATE(date, '%Y-%m-%d'));

报错：Error Code: 1292. Incorrect date value: 'N' for column 'date' at row 1

2. 语句2：

LOAD DATA INFILE "/xxxx/location.txt" INTO TABLE LOCATION FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES
(Place, date)
SET
Date=STR_TO_DATE(date,'%Y-%m-%d');

报错：Incorrect date value: 'N ' for column 'Date' at row 24

3. 语句3：

LOAD DATA INFILE "/xxxx/location.txt" INTO TABLE LOCATION FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES
(Place, date)
SET
Date=NULLIF(date, '\\N'),
Date=STR_TO_DATE(date,'%Y-%m-%d');

报错：Error Code: 1292. Incorrect date value: 'N' for column 'date' at row 1

问题 #

无法编辑目标txt文件，请问还有哪些可用的函数或方法解决此导入报错问题？

可行解决方案 #

方案1：转义处理+临时变量+空格清理 #

通过ESCAPED BY让MySQL正确解析\N，同时用临时变量处理字段中的多余空格：

LOAD DATA INFILE "/xxxx/location.txt" 
INTO TABLE LOCATION 
FIELDS TERMINATED BY '\t' 
ESCAPED BY '\\'  -- 识别转义字符，将\N解析为N
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(Place, @temp_date)  -- 用临时变量接收原始日期值
SET 
Date = CASE 
    WHEN TRIM(@temp_date) = 'N' THEN NULL
    ELSE STR_TO_DATE(TRIM(@temp_date), '%Y-%m-%d') 
END;

方案2：NULLIF+TRIM简化处理 #

利用NULLIF将清理后的N转为NULL，STR_TO_DATE处理NULL时会直接返回NULL，不会触发日期格式错误：

LOAD DATA INFILE "/xxxx/location.txt" 
INTO TABLE LOCATION 
FIELDS TERMINATED BY '\t' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(Place, @temp_date)
SET 
Date = STR_TO_DATE(NULLIF(TRIM(@temp_date), 'N'), '%Y-%m-%d');

方案3：临时关闭严格模式（应急用） #

如果上述方法仍不生效，可临时关闭SQL严格模式，让MySQL自动将无效日期值转为NULL：

-- 临时关闭严格模式
SET sql_mode = '';

LOAD DATA INFILE "/xxxx/location.txt" 
INTO TABLE LOCATION 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(Place, Date);

-- 恢复默认严格模式（务必执行）
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

注意：长期关闭严格模式会降低数据校验性，仅作为临时应急方案。

内容的提问来源于stack exchange，提问作者liang