MySQL解析字符串日期时如何校验格式并清理多余分隔符修复错误

针对字符串存储日期存在多余分隔符、非法字符导致解析失败的问题，直接在解析前增加一层清洗逻辑即可，不需要调整原有判断分支。

MySQL 8.0+ 最优方案（支持正则替换） #

利用REGEXP_REPLACE一次性完成三类清洗：

• 统一分隔符：先把所有-替换为/，和原有解析逻辑保持一致
• 移除非法字符：剔除所有非数字、非/的无关字符
• 合并连续分隔符：把连续2个及以上的/替换为单个/，解决6/24//2022这类输入的解析问题

单个字段的清洗逻辑代码如下：

REGEXP_REPLACE(
  REGEXP_REPLACE(
    REPLACE(so.customfields->>'$."59".value', "-", "/"),
    '[^0-9/]',
    ''
  ),
  '/{2,}',
  '/'
)

该逻辑可以覆盖绝大多数用户输入错误场景：6/24//2022会被清洗为6/24/2022，6--24/2022abc会被清洗为6/24/2022，清洗后的值完全适配原有STR_TO_DATE的格式要求。

MySQL 5.7 兼容方案（无正则函数） #

5.7版本没有内置正则替换函数，可以通过多层嵌套REPLACE，循环把连续的//替换为单个/，嵌套4层即可覆盖最多16个连续斜杠的极端输入场景：

REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(so.customfields->>'$."59".value', "-", "/"),
        '//', '/'
      ),
      '//', '/'
    ),
    '//', '/'
  ),
  '//', '/'
)

该方案缺点是无法自动移除数字、斜杠之外的其他非法字符，如果业务中存在其他类型的乱码输入，需要额外增加REPLACE逻辑逐个处理。

整合后完整SQL片段 #

把原有SQL中直接读取JSON字段值的部分替换为上述清洗逻辑即可，原有IFNULL判断、日期格式化、错误提示逻辑完全不需要改动，8.0版本整合后的代码如下：

IFNULL(
  IFNULL(
    NULLIF(
      DATE_FORMAT(
        STR_TO_DATE(
          REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(so.customfields->>'$."59".value',"-","/"),'[^0-9/]',''),'/{2,}','/'),
          '%m/%d/%Y'
        ),
        '%m-%d-%Y'
      ),
      "00-00-0000"
    ),
    NULLIF(
      DATE_FORMAT(
        STR_TO_DATE(
          REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(so.customfields->>'$."61".value',"-","/"),'[^0-9/]',''),'/{2,}','/'),
          '%m/%d/%Y'
        ),
        '%m-%d-%Y'
      ),
      "00-00-0000"
    )
  ),
  "Missing Requested Ship and Earliest Ship Date"
) as "Earliest Ship Date",

补充说明：原有逻辑使用的%m/%d/%Y格式符天然支持个位数月/日输入（比如6/2/2022可以正常解析），不需要调整格式参数；如果遇到完全不符合日历逻辑的输入（比如13/40/2022这类不存在的日期），STR_TO_DATE依然会返回Null，正常触发原有错误提示逻辑，不会返回错误日期值。

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