mysql 根据身份证号查询性别、年龄以及出生日期的方法

查询语句

SELECT id,xm as name,

-- 根据身份证号求年龄
(YEAR(NOW())- SUBSTRING(sfzh,7,4)) as age,

-- 根据身份证号求出生日期
cast(substring(sfzh,7,8) as date) as birdthday,

-- 根据身份证号求性别
case if(length(sfzh)=18, cast(substring(sfzh,17,1) as UNSIGNED)%2, if(length(sfzh)=15,cast(substring(sfzh,15,1) as UNSIGNED)%2,3)) 
when 1 then '男' when 0 then '女' else '未知' end as sex

FROM t_xsxx ORDER BY age DESC LIMIT 10

查询结果