使用TIMESTAMPDIFF计算年龄出现NULL值，请求SQL语句排查

问题场景 #

根据no_id字段解析出生日期并计算年龄，执行SQL后部分行的age字段返回NULL，而非预期数值。

原SQL语句 #

select no_id,
    case when substring(no_id,1,2) < Substring(YEAR(NOW()),-2) 
    then Concat('20',substring(no_id,1,2),'-',substring(no_id,3,2),'-', substring(no_id,5,2))
    else
     Concat('19',substring(no_id,1,2),'-',substring(no_id,3,2),'-', substring(no_id,5,2))
    end as dob, TIMESTAMPDIFF(YEAR, no_id, CURDATE()) AS age
from driver_details;

实际表数据 #

实际执行输出 #

预期输出 #

问题原因 #

原SQL中TIMESTAMPDIFF(YEAR, no_id, CURDATE())存在核心错误：

• no_id是12位字符串（如970101110101），MySQL无法将其隐式转换为有效日期格式，导致TIMESTAMPDIFF无法计算，返回NULL。
• 部分短格式字符串的隐式转换属于侥幸行为，完全不可靠。

修正方案 #

方案1：用子查询复用已解析的dob #

先在子查询中生成标准日期格式的dob，再基于该字段计算年龄：

select no_id, dob, TIMESTAMPDIFF(YEAR, STR_TO_DATE(dob, '%Y-%m-%d'), CURDATE()) AS age
from (
    select no_id,
        case when substring(no_id,1,2) < Substring(YEAR(NOW()),-2) 
            then Concat('20',substring(no_id,1,2),'-',substring(no_id,3,2),'-',substring(no_id,5,2))
            else Concat('19',substring(no_id,1,2),'-',substring(no_id,3,2),'-',substring(no_id,5,2))
        end as dob
    from driver_details
) temp;

方案2：直接在函数内转换日期 #

将no_id解析为标准日期后传入TIMESTAMPDIFF，避免重复逻辑：

select no_id,
    case when substring(no_id,1,2) < Substring(YEAR(NOW()),-2) 
        then Concat('20',substring(no_id,1,2),'-',substring(no_id,3,2),'-',substring(no_id,5,2))
        else Concat('19',substring(no_id,1,2),'-',substring(no_id,3,2),'-',substring(no_id,5,2))
    end as dob, 
    TIMESTAMPDIFF(YEAR, 
        STR_TO_DATE(
            CONCAT(
                case when substring(no_id,1,2) < Substring(YEAR(NOW()),-2) then '20' else '19' end,
                substring(no_id,1,2),'-',substring(no_id,3,2),'-',substring(no_id,5,2)
            ), 
            '%Y-%m-%d'
        ), 
        CURDATE()
    ) AS age
from driver_details;

验证结果 #

执行修正后的SQL，两行数据的age字段均能返回预期数值：第二行970101110101对应的age为25。

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