MySQL中如何仅忽略STR_TO_DATE函数的警告，保留其他警告？

问题说明 #

要把my_table表comment字段里的日期统一成标准格式，写的SELECT查询能正常提取转换多种格式的日期，但执行UPDATE时，STR_TO_DATE遇到不匹配的日期格式会触发警告，导致更新失败。不想用UPDATE IGNORE忽略所有警告，只希望跳过这个函数的警告，其他警告保留。

可行方案 #

方案1：临时修改会话sql_mode，关闭日期严格校验 #

MySQL的严格模式（比如STRICT_TRANS_TABLES）会把STR_TO_DATE的转换警告升级成错误，导致更新中断。可以临时在当前会话里关掉相关严格模式，执行完更新再恢复。

操作步骤：

1. 先查当前会话的sql_mode：

SELECT @@SESSION.sql_mode;

2. 临时修改sql_mode，移除STRICT_TRANS_TABLES、NO_ZERO_DATE、NO_ZERO_IN_DATE这些会触发日期错误的选项（保留其他你需要的模式）：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3. 执行更新语句（优化了原查询里多余的子查询写法）：

UPDATE (
    SELECT
        t.id,
        REGEXP_SUBSTR(t.comment, '(?<=Date: )[0-9/]+') AS date_str,
        COALESCE(STR_TO_DATE(date_str, '%m/%d/%Y'), NULL) AS converted_a,
        COALESCE(STR_TO_DATE(date_str, '%m/%d/%y'), NULL) AS converted_b,
        COALESCE(STR_TO_DATE(date_str, '%d/%m/%Y'), NULL) AS converted_c,
        COALESCE(converted_a, converted_b, converted_c) AS new_date_str
    FROM my_table t
) sub
JOIN my_table t2 USING (id)
SET t2.comment = REPLACE(t2.comment, sub.date_str, sub.new_date_str);

4. 把sql_mode改回原来的配置：

SET SESSION sql_mode = '你之前查到的原sql_mode值';

方案2：封装安全转换函数，捕获STR_TO_DATE警告 #

写一个自定义函数，内部处理STR_TO_DATE的转换错误，遇到无法转换的情况直接返回NULL，不会触发警告。

创建函数：

DELIMITER //
CREATE FUNCTION safe_str_to_date(date_str VARCHAR(50), format_str VARCHAR(50))
RETURNS DATE
DETERMINISTIC
BEGIN
    DECLARE result DATE;
    -- 捕获转换时的警告和未找到匹配的情况，返回NULL
    DECLARE CONTINUE HANDLER FOR SQLWARNING, NOT FOUND SET result = NULL;
    SET result = STR_TO_DATE(date_str, format_str);
    RETURN result;
END //
DELIMITER ;

然后用这个函数替换原查询里的STR_TO_DATE：

UPDATE (
    SELECT
        t.id,
        REGEXP_SUBSTR(t.comment, '(?<=Date: )[0-9/]+') AS date_str,
        COALESCE(safe_str_to_date(date_str, '%m/%d/%Y'), NULL) AS converted_a,
        COALESCE(safe_str_to_date(date_str, '%m/%d/%y'), NULL) AS converted_b,
        COALESCE(safe_str_to_date(date_str, '%d/%m/%Y'), NULL) AS converted_c,
        COALESCE(converted_a, converted_b, converted_c) AS new_date_str
    FROM my_table t
) sub
JOIN my_table t2 USING (id)
SET t2.comment = REPLACE(t2.comment, sub.date_str, sub.new_date_str);

这个函数只会吃掉STR_TO_DATE的警告，其他操作的警告依然会正常触发。

方案3：只更新能正常转换的记录 #

在子查询里先过滤掉new_date_str为NULL的行，只处理那些能成功转换的日期，从根源避免转换错误。

更新语句：

UPDATE (
    SELECT
        t.id,
        REGEXP_SUBSTR(t.comment, '(?<=Date: )[0-9/]+') AS date_str,
        COALESCE(STR_TO_DATE(date_str, '%m/%d/%Y'), 
                 STR_TO_DATE(date_str, '%m/%d/%y'), 
                 STR_TO_DATE(date_str, '%d/%m/%Y')) AS new_date_str
    FROM my_table t
    -- 只保留能成功转换的行
    WHERE COALESCE(STR_TO_DATE(date_str, '%m/%d/%Y'), 
                   STR_TO_DATE(date_str, '%m/%d/%y'), 
                   STR_TO_DATE(date_str, '%d/%m/%Y')) IS NOT NULL
) sub
JOIN my_table t2 USING (id)
SET t2.comment = REPLACE(t2.comment, sub.date_str, sub.new_date_str);

这样只会更新有有效日期的记录，不会触发转换警告，同时其他操作的警告依然会被保留。

内容的提问来源于stack exchange，提问作者You Old Fool