MySQL中任意日期格式转换及无指定格式验证有效日期方法

嘿，针对你遇到的这两个MySQL日期处理问题，我来分享一些实用的解决方案：

首先明确下，MySQL兼容的标准日期格式是YYYY-MM-DD。由于用户输入的格式不固定，我们可以通过枚举常见格式+COALESCE函数来实现自动匹配转换，它会返回第一个能成功转换的结果：

SELECT COALESCE(
  STR_TO_DATE(user_input, '%d-%m-%Y'),   -- 处理21-08-2013这类格式
  STR_TO_DATE(user_input, '%d/%m/%Y'),   -- 处理21/08/2013这类格式
  STR_TO_DATE(user_input, '%Y/%m/%d'),   -- 处理2013/08/21这类格式
  STR_TO_DATE(user_input, '%d%b%Y'),     -- 处理21jan2018这类格式（注意月份是英文缩写，大小写不敏感）
  -- 可以继续添加更多你遇到的常见日期格式，比如'%m/%d/%Y'等
  NULL  -- 如果所有格式都匹配失败，返回NULL
) AS mysql_compatible_date
FROM your_table;

MySQL本身没有内置的"自动识别任意格式验证日期"的函数，但我们可以通过以下两种方案实现：

方案一：利用MySQL 8.0+的TRY_系列函数（推荐） #

TRY_STR_TO_DATE和TRY_CAST在转换失败时不会抛出错误，而是返回NULL，我们可以通过判断结果是否非空来验证：

-- 方法1：结合TRY_STR_TO_DATE枚举常见格式
SELECT IF(
  TRY_STR_TO_DATE(user_input, '%d-%m-%Y') IS NOT NULL
  OR TRY_STR_TO_DATE(user_input, '%d/%m/%Y') IS NOT NULL
  OR TRY_STR_TO_DATE(user_input, '%Y/%m/%d') IS NOT NULL
  OR TRY_STR_TO_DATE(user_input, '%d%b%Y') IS NOT NULL,
  '有效日期',
  '无效日期'
) AS is_valid_date
FROM your_table;

-- 方法2：尝试直接用TRY_CAST（部分格式可自动识别，但覆盖范围不如枚举广）
SELECT IF(TRY_CAST(user_input AS DATE) IS NOT NULL, '有效日期', '无效日期') AS is_valid_date
FROM your_table;

方案二：针对MySQL 5.x版本（无TRY_函数） #

可以自定义一个存储函数，通过捕获转换错误来验证：

DELIMITER //
CREATE FUNCTION is_valid_date(input_str VARCHAR(50)) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  DECLARE valid BOOLEAN DEFAULT FALSE;
  -- 捕获转换时的警告/异常，避免报错中断
  DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION SET valid = FALSE;
  
  -- 依次尝试各种常见格式
  SET @temp_date = STR_TO_DATE(input_str, '%d-%m-%Y');
  IF @temp_date IS NOT NULL THEN SET valid = TRUE; END IF;
  
  IF NOT valid THEN
    SET @temp_date = STR_TO_DATE(input_str, '%d/%m/%Y');
    IF @temp_date IS NOT NULL THEN SET valid = TRUE; END IF;
  END IF;
  
  IF NOT valid THEN
    SET @temp_date = STR_TO_DATE(input_str, '%Y/%m/%d');
    IF @temp_date IS NOT NULL THEN SET valid = TRUE; END IF;
  END IF;
  
  IF NOT valid THEN
    SET @temp_date = STR_TO_DATE(input_str, '%d%b%Y');
    IF @temp_date IS NOT NULL THEN SET valid = TRUE; END IF;
  END IF;
  
  RETURN valid;
END //
DELIMITER ;

使用这个函数验证日期：

SELECT is_valid_date('21jan2018') AS is_valid; -- 返回1（TRUE，有效日期）
SELECT is_valid_date('2023/13/01') AS is_valid; -- 返回0（FALSE，无效日期）

注意事项 #

这两种方案的核心都是覆盖常见的日期格式，如果用户输入了非常罕见的格式（比如YYYY-DD-MM的非常规写法），可能无法识别，你可以根据实际遇到的格式继续扩充枚举列表。

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