MySQL中含REGEXP的CASE语句无法运行，求解决方法

我来帮你排查这个问题，你的SQL语句执行报错，大概率是CASE分支返回的字段类型不匹配导致的，咱们一步步拆解原因和解决方案：

核心问题分析 #

你的CASE语句里：

• WHEN分支通过STR_TO_DATE返回的是DATE/DATETIME类型
• ELSE分支直接返回原字段date_of_sale，如果原字段是字符串类型（比如VARCHAR），两个分支的返回类型就会不一致；如果原字段是DATE类型，直接用REGEXP匹配DATE值会触发隐式转换，容易出现匹配异常或类型错误。

另外，你写的正则表达式[0-9]*-[0-9]*-[0-9]*太宽松了，会匹配空字符串、1--2024这类无效格式，也可能导致后续转换失败。

分场景解决方案 #

场景1：date_of_sale是字符串类型（VARCHAR/TEXT） #

需要保证CASE的两个分支返回相同数据类型，同时优化正则表达式的精准度：

SELECT 
  `date_of_sale`,
  CASE 
    -- 精准匹配MM-DD-YYYY格式的字符串，^和$确保整个字符串符合格式
    WHEN `date_of_sale` REGEXP '^[0-9]{1,2}-[0-9]{1,2}-[0-9]{4}$' 
    THEN STR_TO_DATE(`date_of_sale`, '%m-%d-%Y')
    -- 处理不匹配的情况：如果不匹配的字符串是标准Y-m-d格式，直接转换；否则返回NULL或默认日期
    ELSE STR_TO_DATE(`date_of_sale`, '%Y-%m-%d') -- 替换成你需要的默认转换规则
    -- 或者无法确定格式时返回NULL：ELSE NULL
  END AS formatted_date
FROM <TABLE_NAME>;

场景2：date_of_sale是DATE/DATETIME类型 #

DATE类型本身是标准格式，不需要用REGEXP匹配，直接用DATE_FORMAT转换格式即可：

SELECT 
  `date_of_sale`,
  -- 转换为你需要的格式，比如YYYY-MM-DD
  DATE_FORMAT(`date_of_sale`, '%Y-%m-%d') AS formatted_date
FROM <TABLE_NAME>;

如果字段中存在脏数据（比如DATE类型存储了无效值），可以先转成字符串再处理：

SELECT 
  `date_of_sale`,
  CASE 
    WHEN CAST(`date_of_sale` AS CHAR) REGEXP '^[0-9]{1,2}-[0-9]{1,2}-[0-9]{4}$'
    THEN STR_TO_DATE(CAST(`date_of_sale` AS CHAR), '%m-%d-%Y')
    ELSE `date_of_sale`
  END AS formatted_date
FROM <TABLE_NAME>;

额外注意事项 #

• 确认STR_TO_DATE的格式符和你的字符串格式一致：比如如果原字符串是DD-MM-YYYY，格式符要改成%d-%m-%Y，别搞混月份和日期。
• 如果你的MySQL版本支持TRY_STR_TO_DATE（MySQL 8.0.17+），可以用它替代STR_TO_DATE，避免转换无效字符串时抛出错误：

  TRY_STR_TO_DATE(`date_of_sale`, '%m-%d-%Y')
  

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