MySQL 8.0+中STR_TO_DATE处理1970年前日期报错问题

问题根源 #

MySQL 8.0默认启用了更严格的SQL模式（如STRICT_TRANS_TABLES、NO_ZERO_DATE、NO_ZERO_IN_DATE），同时STR_TO_DATE函数对格式不匹配的字符串解析行为更严格——即使后续分支能正确解析，前面分支中用错误格式解析的尝试也会触发报错，而5.7版本会忽略这类无效解析的错误。

解决方案 #

1. 调整SQL模式 #

先临时修改会话级SQL模式，测试有效性：

SET sql_mode = 'ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

若要永久生效，修改MySQL配置文件（Linux为my.cnf，Windows为my.ini），添加/修改以下配置后重启服务：

[mysqld]
sql_mode = ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2. 优化UPDATE语句 #

原语句的问题是重复调用STR_TO_DATE，且会用错误格式尝试解析所有值，触发报错。优化思路是先用正则匹配确定日期格式，再针对性解析，避免无效解析尝试：

UPDATE users
SET birthday = DATE_FORMAT(
    CASE
        -- 匹配YYYY-MM-DD格式
        WHEN birthday REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN
            CASE WHEN STR_TO_DATE(birthday, '%Y-%m-%d') > NOW() THEN
                DATE_SUB(STR_TO_DATE(birthday, '%Y-%m-%d'), INTERVAL 100 YEAR)
            ELSE
                STR_TO_DATE(birthday, '%Y-%m-%d')
            END
        -- 匹配MM-DD-YYYY格式
        WHEN birthday REGEXP '^[0-9]{2}-[0-9]{2}-[0-9]{4}$' THEN
            CASE WHEN STR_TO_DATE(birthday, '%m-%d-%Y') > NOW() THEN
                DATE_SUB(STR_TO_DATE(birthday, '%m-%d-%Y'), INTERVAL 100 YEAR)
            ELSE
                STR_TO_DATE(birthday, '%m-%d-%Y')
            END
        -- 匹配MM/DD/YYYY格式
        WHEN birthday REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$' THEN
            CASE WHEN STR_TO_DATE(birthday, '%m/%d/%Y') > NOW() THEN
                DATE_SUB(STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL 100 YEAR)
            ELSE
                STR_TO_DATE(birthday, '%m/%d/%Y')
            END
        -- 无法匹配的格式，可根据需求设为NULL或保留原值
        ELSE birthday
    END, '%Y-%m-%d')
-- 仅更新需要处理的行，提升效率
WHERE birthday REGEXP '^([0-9]{2}([-/][0-9]{2}){2}[0-9]{4})|([0-9]{4}-[0-9]{2}-[0-9]{2})$';

3. 关键说明 #

• 用REGEXP先匹配格式，确保每个日期值只被用正确的格式解析一次，避免无效解析触发报错。
• DATE_FORMAT将解析后的日期转为Y-m-d字符串，符合VARCHAR列的存储需求，后续转DATETIME列也无需额外处理。
• 保留了原逻辑中“日期大于当前则减100年”的规则，同时处理了无法匹配格式的异常值。

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