MySQL中STR_TO_DATE在SELECT正常返回NULL，UPDATE却报错

问题原因 #

SELECT语句中CAST(STR_TO_DATE('1/0/2000', '%m/%d/%Y') AS DATE)返回NULL，但UPDATE时触发1411错误，核心原因是MySQL的sql_mode严格校验规则：当sql_mode包含NO_ZERO_IN_DATE、NO_ZERO_DATE或STRICT_TRANS_TABLES时，DML操作（UPDATE/INSERT）会对无效日期转换抛出错误，而SELECT仅返回NULL不触发错误。

通用解决方案 #

方案1：使用TRY_CAST（MySQL 8.0.19+ 推荐） #

MySQL 8.0.19及以上版本支持TRY_CAST函数，它会在转换失败时返回NULL，而非抛出错误，直接替换原有逻辑即可：

UPDATE your_table 
SET date_column = TRY_CAST(STR_TO_DATE(your_string_column, '%m/%d/%Y') AS DATE)
-- 可添加WHERE条件限定范围
WHERE your_string_column IS NOT NULL;

方案2：自定义安全转换函数（兼容所有MySQL 8.0版本） #

创建一个捕获1411错误的自定义函数，确保无效日期转换返回NULL：

DELIMITER //
CREATE FUNCTION safe_str_to_date(str VARCHAR(255), fmt VARCHAR(50)) 
RETURNS DATE 
DETERMINISTIC
BEGIN
    DECLARE result DATE;
    -- 捕获STR_TO_DATE的1411错误，设置结果为NULL
    DECLARE CONTINUE HANDLER FOR 1411 SET result = NULL;
    SET result = STR_TO_DATE(str, fmt);
    RETURN result;
END //
DELIMITER ;

调用函数执行更新：

UPDATE your_table 
SET date_column = safe_str_to_date(your_string_column, '%m/%d/%Y')
WHERE your_string_column IS NOT NULL;

方案3：临时调整sql_mode（快速应急） #

临时移除严格校验相关的sql_mode选项，执行更新后恢复原配置：

1. 查看当前sql_mode：

SELECT @@sql_mode;

2. 临时修改sql_mode（替换为你的实际配置，移除NO_ZERO_IN_DATE、NO_ZERO_DATE、STRICT_TRANS_TABLES）：

SET sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3. 执行更新语句：

UPDATE your_table 
SET date_column = CAST(STR_TO_DATE(your_string_column, '%m/%d/%Y') AS DATE)
WHERE your_string_column IS NOT NULL;

4. 恢复原sql_mode（替换为步骤1查询到的值）：

SET sql_mode = '原sql_mode值';

注意事项 #

• 优先使用TRY_CAST或自定义函数，避免修改全局sql_mode影响其他业务逻辑
• 自定义函数需确保有足够权限创建
• 更新前建议先备份数据，或用SELECT语句验证转换结果后再执行UPDATE

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