如何在MySQL中标准化存储为字符串的脏日期字段

你当前用的单格式STR_TO_DATE逻辑只能覆盖单一固定格式的日期值，面对异构脏数据、缺少年份、参考列混无关文本的场景，按以下分层逻辑处理即可，转换准确率能覆盖95%以上的常规脏数据场景：

1. 前置清洗：剔除所有和日期无关的冗余内容 #

不管是date列还是参考用的OpeningDate、Deadline列，先统一做字符串清洗，去掉干扰格式匹配的内容：

• 截掉第一个逗号之后的所有内容，过滤掉", 6 pm ABOUT:xxx"这类后缀
• 正则删除所有带AM/PM的时分片段，比如" 2:45 AM"、" 6 pm"这类和日期无关的时间内容
• 把连续多个空格替换成单个空格，去掉首尾多余空格
  MySQL 8.0+可以直接用临时函数封装清洗逻辑，低版本可以把逻辑内联到查询里：

-- 日期字符串清洗逻辑
CREATE TEMPORARY FUNCTION clean_date_str(raw_str VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
RETURN TRIM(
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      SUBSTRING_INDEX(IFNULL(raw_str,''), ',', 1),
      ' [0-9]{1,2}(:[0-9]{2})? ?[APap][Mm]',
      ''
    ),
    ' +',
    ' '
  )
);

2. 多格式兜底匹配，补全缺失年份 #

不要用单一格式字符串硬套所有日期，按优先级依次尝试所有已知的日期格式，匹配成功就返回结果；针对date列缺少年份的记录，优先从OpeningDate提取年份补全，OpeningDate为空就从Deadline提取年份补全：

SELECT
  DATE_FORMAT(
    COALESCE(
      -- 匹配date列「月 日, 年」格式，对应Jan 5, 2004这类值
      STR_TO_DATE(clean_date_str(`date`), '%M %d, %Y'),
      -- 匹配date列「日 月 年」格式
      STR_TO_DATE(clean_date_str(`date`), '%d %M %Y'),
      -- 匹配date列缺少年份的「月 日」格式，拼接提取到的参考年份后转换
      STR_TO_DATE(CONCAT(clean_date_str(`date`), ' ', ref_year), '%M %d %Y')
    ),
    '%Y-%m-%d'
  ) AS standard_date
FROM (
  SELECT
    `date`,
    COALESCE(
      -- 优先从OpeningDate提取年份
      YEAR(STR_TO_DATE(clean_date_str(OpeningDate), '%d %M %Y')),
      -- OpeningDate提取失败就从Deadline提取年份
      YEAR(STR_TO_DATE(clean_date_str(Deadline), '%d %M %Y'))
    ) AS ref_year
  FROM `data job posts`
) t;

说明：MySQL原生STR_TO_DATE同时支持月份缩写（Jan、Jun）和月份全拼（January、June）识别，不需要额外做映射表。

3. 异常值兜底 #

所有转换结果为NULL的记录，直接筛选出来单独人工核验即可，这类极端不规则的脏数据占比通常极低，不需要为了覆盖极小部分数据把SQL逻辑写得过度冗余。

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