使用LOCAL INFILE导入MySQL时Excel转换后日期显示异常问题

核心问题原因 #

• MySQL DATE类型默认识别的输入格式为yyyy-mm-dd，你导入的CSV中日期为yyyy/mm/dd格式，格式不匹配导致直接写入时触发非法日期默认值0000-00-00
• 此前使用date_format()逻辑错误：该函数作用是将合法日期值转为指定格式字符串，输入的@dates为字符串类型的非标准日期，无法被识别因此返回null

具体操作 #

1. 无需修改表结构，dates字段保留DATE类型即可
2. 调整LOAD DATA语句，通过STR_TO_DATE()函数将非标准日期字符串转换为MySQL支持的日期格式，完整语句如下：

LOAD DATA LOCAL INFILE 'file_location/data.csv' 
INTO TABLE df
FIELDS TERMINATED BY ',' 
ENCLOSED BY "'" 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@dates, store_num, loyalty_card_num, txin_id, prod_num, product_name, product_qty, total_sales)
SET dates = STR_TO_DATE(@dates, '%Y/%m/%d');

异常排查 #

如果执行后仍有问题，优先检查两项：

• 打开CSV确认日期实际格式，若为月/日/年格式，将STR_TO_DATE的第二个参数调整为%m/%d/%Y
• 若CSV为Linux/Mac系统导出，将LINES TERMINATED BY '\r\n'修改为LINES TERMINATED BY '\n'

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