MySQL 8.0中STR_TO_DATE封装为UDF后行为异常问题

MySQL中，直接调用STR_TO_DATE处理非法格式的字符串时，系统只会抛出警告并返回NULL；但在自定义函数（UDF）中，如果会话的sql_mode包含STRICT_TRANS_TABLES或STRICT_ALL_TABLES（默认配置），STR_TO_DATE触发的警告会被升级为错误，导致函数直接终止执行，进而整个查询无结果集返回。

方法1：临时修改UDF内的sql_mode #

在函数内部临时关闭严格模式，执行日期转换后再恢复原模式，避免错误终止：

DELIMITER //
CREATE FUNCTION fnProcessDateEntry(date_str VARCHAR(20)) 
RETURNS DATE
DETERMINISTIC
BEGIN
    DECLARE original_sql_mode VARCHAR(255);
    -- 保存原sql_mode
    SELECT @@sql_mode INTO original_sql_mode;
    -- 临时关闭严格模式
    SET @@sql_mode = REPLACE(REPLACE(original_sql_mode, 'STRICT_TRANS_TABLES', ''), 'STRICT_ALL_TABLES', '');
    -- 执行日期转换
    RETURN STR_TO_DATE(date_str, '%m/%d/%Y');
    -- 恢复原sql_mode
    SET @@sql_mode = original_sql_mode;
END //
DELIMITER ;

方法2：使用TRY_CAST（MySQL 8.0.19+） #

如果你的MySQL版本是8.0.19及以上，TRY_CAST可以安全转换格式合法的字符串为日期，非法格式直接返回NULL，不会触发错误：

DELIMITER //
CREATE FUNCTION fnProcessDateEntry(date_str VARCHAR(20)) 
RETURNS DATE
DETERMINISTIC
BEGIN
    RETURN TRY_CAST(date_str AS DATE FORMAT '%m/%d/%Y');
END //
DELIMITER ;

方法3：提前过滤非法输入 #

在调用STR_TO_DATE前，先判断输入是否为空或格式不匹配，提前返回NULL，避免触发警告：

DELIMITER //
CREATE FUNCTION fnProcessDateEntry(date_str VARCHAR(20)) 
RETURNS DATE
DETERMINISTIC
BEGIN
    -- 匹配MM/DD/YYYY格式，允许前导零省略
    IF date_str IS NULL OR date_str = '' OR NOT date_str REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$' THEN
        RETURN NULL;
    END IF;
    RETURN STR_TO_DATE(date_str, '%m/%d/%Y');
END //
DELIMITER ;

方法4：结合错误抑制符号 #

使用@符号抑制STR_TO_DATE的警告，确保即使转换失败也返回NULL：

DELIMITER //
CREATE FUNCTION fnProcessDateEntry(date_str VARCHAR(20)) 
RETURNS DATE
DETERMINISTIC
BEGIN
    RETURN @STR_TO_DATE(date_str, '%m/%d/%Y');
END //
DELIMITER ;

注：@符号会抑制所有警告，不建议在需要监控转换错误的场景使用。

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