如何将CSV中'mm/dd/yyyy'格式日期直接导入MySQL DATE类型

核心问题分析 #

你之前的STR_TO_DATE调用失败，大概率是格式符与CSV日期格式不匹配：

• 你用了%m（要求两位月份，如05），但CSV里是单数字月份（如5）；
• 若CSV中日期存在单数字日（如5/9/2023），%d（要求两位日）也会导致转换失败，最终返回NULL或0000-00-00。

修正后的LOAD DATA语句 #

针对CSV中mm/dd/yyyy（含单双位月/日）的格式，使用兼容单双位的格式符%c（月份1-12，无需前导零）和%e（日1-31，无需前导零）：

LOAD DATA LOCAL INFILE 'C:/Users/LK/Desktop/Date Import Test.csv'
INTO TABLE Test_Table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(date_col, blah_col1, blah_col2)
SET date_col = STR_TO_DATE(date_col, '%c/%e/%Y');

关键注意事项 #

1. 验证格式符有效性
   先在MySQL中单独测试转换逻辑，确认能得到正确结果：

   SELECT STR_TO_DATE('5/10/2023', '%c/%e/%Y'); -- 应返回2023-05-10
   SELECT STR_TO_DATE('05/09/2023', '%c/%e/%Y'); -- 同样兼容带前导零的格式
   

2. 无需DATE_FORMAT转换
   STR_TO_DATE返回的本身就是MySQL标准DATE类型，直接赋值给DATE列即可，不需要再用DATE_FORMAT转成字符串，否则会丢失DATE类型的查询优势。

3. 处理现有VARCHAR日期列
   如果表中已有VARCHAR类型的日期数据，先转换为DATE类型再修改列定义：

   -- 先转换现有数据
   UPDATE Test_Table 
   SET date_col = STR_TO_DATE(date_col, '%c/%e/%Y') 
   WHERE date_col IS NOT NULL AND date_col != '';
   
   -- 修改列类型为DATE
   ALTER TABLE Test_Table MODIFY COLUMN date_col DATE;
   

4. 处理无效日期
   若CSV中存在非法日期（如13/32/2023），STR_TO_DATE会返回NULL。可以临时调整SQL_MODE允许无效日期（不推荐），或提前清洗CSV数据：

   -- 临时关闭严格模式（会话级）
   SET SESSION sql_mode = 'NO_ZERO_DATE,NO_ZERO_IN_DATE';
   

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