MySQL 8插入非标准日期字符串报错，如何不修改全局配置解决？

现有两张表：

• 原表（original_table）的date_str字段为varchar类型，存储了多种格式数据，包括'2016-02'、'2016-02-02'这类日期格式，以及123、abc这类非日期格式内容；
• 新表（new_table）在原表结构基础上新增is_date字段。

需求是将原表date_str数据插入新表，同时校验date_str是否为合法日期格式，若不合法则在is_date字段填充'DATE_FORMAT'。

执行以下SQL时出现报错：

INSERT INTO new_table ( date_str, is_date ) 
SELECT ot.date_str AS date_str, 
       CASE WHEN DATE_FORMAT( ot.date_str, '%Y-%m-%d' ) IS NULL THEN 'DATE_FORMAT' END AS is_date 
FROM original_table ot

报错信息：Incorrect DateTime value: '2016-02'

不想通过修改my.cnf移除sql_mode=STRICT_TRANS_TABLES（避免影响其他表），寻求解决方案。

解决方案 #

方案1：临时修改当前会话的sql_mode #

无需修改全局配置，仅在当前会话中临时关闭严格模式，操作完成后可按需恢复：

-- 先查看当前会话的sql_mode配置
SELECT @@SESSION.sql_mode;

-- 临时移除STRICT_TRANS_TABLES，保留其他原有模式
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');

-- 执行插入操作，替换DATE_FORMAT为STR_TO_DATE以兼容多种合法日期格式
INSERT INTO new_table ( date_str, is_date ) 
SELECT ot.date_str AS date_str, 
       CASE WHEN STR_TO_DATE(ot.date_str, '%Y-%m-%d') IS NULL 
            AND STR_TO_DATE(ot.date_str, '%Y-%m') IS NULL 
            THEN 'DATE_FORMAT' 
       END AS is_date 
FROM original_table ot;

-- 可选：若需要，将sql_mode恢复为原配置
SET SESSION sql_mode = '你之前查询到的原sql_mode值';

替换原因：DATE_FORMAT要求输入必须是标准合法日期，否则直接触发报错；而STR_TO_DATE会尝试按指定格式解析字符串，解析失败返回NULL，更适配校验需求。

方案2：使用安全日期校验（不修改sql_mode） #

如果不想调整sql_mode，可根据MySQL版本选择对应方法：

方法A：使用TRY_CAST（MySQL 8.0及以上版本支持）

TRY_CAST转换失败时不会报错，直接返回NULL；对于YYYY-MM格式的字符串，拼接-01后可转换为合法日期：

INSERT INTO new_table ( date_str, is_date ) 
SELECT ot.date_str AS date_str, 
       CASE WHEN TRY_CAST(ot.date_str AS DATE) IS NULL 
            AND TRY_CAST(CONCAT(ot.date_str, '-01') AS DATE) IS NULL 
            THEN 'DATE_FORMAT' 
       END AS is_date 
FROM original_table ot;

方法B：自定义日期校验函数（兼容低版本MySQL）

先创建一个自定义函数，专门判断字符串是否为合法日期（支持YYYY-MM和YYYY-MM-DD格式）：

DELIMITER //
CREATE FUNCTION is_valid_date(date_str VARCHAR(255)) 
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE valid BOOLEAN DEFAULT FALSE;
    -- 校验YYYY-MM-DD格式
    IF date_str REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' THEN
        SET valid = STR_TO_DATE(date_str, '%Y-%m-%d') IS NOT NULL;
    -- 校验YYYY-MM格式
    ELSEIF date_str REGEXP '^[0-9]{4}-[0-9]{2}$' THEN
        SET valid = STR_TO_DATE(CONCAT(date_str, '-01'), '%Y-%m-%d') IS NOT NULL;
    END IF;
    RETURN valid;
END //
DELIMITER ;

然后使用该函数执行插入操作：

INSERT INTO new_table ( date_str, is_date ) 
SELECT ot.date_str AS date_str, 
       CASE WHEN is_valid_date(ot.date_str) = FALSE THEN 'DATE_FORMAT' END AS is_date 
FROM original_table ot;

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