mysql函数应用实例：截取身份证中的生日

截取身份证中的生日：

update tb_basicinfo inner join (

    select CONCAT(right(left(basicinfo_card,10),4),'-',right(left(basicinfo_card,12),2),'-',right(left(basicinfo_card,14),2)) as birth,basicinfo_id
    from tb_basicinfo
    where (basicinfo_birth is null or basicinfo_birth='')
        and basicinfo_card is not null
        and  length(basicinfo_card)>14
        and right(left(basicinfo_card,8),2) in ('19','20')
    ) as birthTable
    on tb_basicinfo.basicinfo_id=birthTable.basicinfo_id

    set tb_basicinfo.basicinfo_birth=birthTable.birth;

通过生日计算年龄：

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(basicinfo_birth)), '%Y')+0 AS age from tb_basicinfo where basicinfo_birth is not null;