如何设置SQL日期格式为12th Jun 2021样式并解决CSV导入日期异常

一、导入时修复日期格式（核心解决方案） #

你的CSV日期格式是1st Jan 2021这种带序数词后缀（st/nd/rd/th）的格式，MySQL默认无法直接识别，导致导入后变成0000-00-00。需要在导入过程中先清理后缀，再转换为DATE类型：

1. 先设置会话时区语言为英文（确保能识别Jan/Feb等英文月份缩写）：

SET lc_time_names = 'en_US';

2. 使用LOAD DATA时，通过用户变量接收原始日期字符串，清理后缀后用STR_TO_DATE转换：
   假设你的CSV结构是name;数字;日期1;日期2;...(空列)，对应的LOAD DATA语句如下：

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE table1
FIELDS TERMINATED BY ';'  -- 匹配CSV的分号分隔符
LINES TERMINATED BY '\n' -- 根据你的文件行分隔符调整，可能是'\r\n'
-- 用@变量接收原始字段，空列用@dummy占位
(name, num_col, @raw_date1, @raw_date2, @dummy1, @dummy2, @dummy3, @dummy4, @dummy5, @dummy6, @dummy7, @dummy8, @dummy9, @dummy10, @dummy11, @dummy12)
SET
  -- 清理日期字符串末尾的st/nd/rd/th后缀，再转换为DATE类型
  my_date = STR_TO_DATE(TRIM(TRAILING 'stndrdth' FROM @raw_date1), '%d %b %Y'),
  other_date = STR_TO_DATE(TRIM(TRAILING 'stndrdth' FROM @raw_date2), '%d %b %Y');

• TRIM(TRAILING 'stndrdth' FROM @raw_date1)：自动去掉字符串末尾的st/nd/rd/th，比如1st转为1，2nd转为2
• %d %b %Y：对应日 月份缩写 年份的格式，比如1 Jan 2021可以被正确解析为DATE类型

二、将DATE字段格式化为12th Jun 2021样式 #

MySQL没有内置函数直接生成序数词后缀，需要通过CASE逻辑自定义：

SELECT
  CONCAT(
    DAY(my_date),
    -- 根据日期的日部分判断后缀
    CASE
      WHEN DAY(my_date) IN (11, 12, 13) THEN 'th'  -- 11/12/13特殊处理，用th
      WHEN RIGHT(DAY(my_date), 1) = '1' THEN 'st'
      WHEN RIGHT(DAY(my_date), 1) = '2' THEN 'nd'
      WHEN RIGHT(DAY(my_date), 1) = '3' THEN 'rd'
      ELSE 'th'
    END,
    ' ',
    DATE_FORMAT(my_date, '%b %Y')  -- 拼接月份缩写和年份
  ) AS formatted_date
FROM table1;

三、你之前尝试的代码为什么无效？ #

你用的DATE_FORMAT(my_date, 'DD MMM yyyy')是将已有的DATE类型转为字符串格式，但此时my_date已经是导入失败的0000-00-00，自然无法得到正确结果。正确的做法是在导入阶段就用STR_TO_DATE处理原始的日期字符串，而不是导入后再格式化。

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