mysql怎么解析身份證

select??case?left(id_number,2)? when?'11'?then?'北京市' when?'12'?then?'天津市' when?'13'?then?'河北省' when?'14'?then?'山西省' when?'15'?then?'內(nèi)蒙古自治區(qū)' when?'21'?then?'遼寧省' when?'22'?then?'吉林省' when?'23'?then?'黑龍江省' when?'31'?then?'上海市' when?'32'?then?'江蘇省' when?'33'?then?'浙江省' when?'34'?then?'安徽省' when?'35'?then?'福建省' when?'36'?then?'江西省' when?'37'?then?'山東省' when?'41'?then?'河南省' when?'42'?then?'湖北省' when?'43'?then?'湖南省' when?'44'?then?'廣東省' when?'45'?then?'廣西壯族自治區(qū)' when?'46'?then?'海南省' when?'50'?then?'重慶市' when?'51'?then?'四川省' when?'52'?then?'貴州省' when?'53'?then?'云南省' when?'54'?then?'西藏自治區(qū)' when?'61'?then?'陜西省' when?'62'?then?'甘肅省' when?'63'?then?'青海省' when?'64'?then?'寧夏回族自治區(qū)' when?'65'?then?'新疆維吾爾自治區(qū)' when?'71'?then?'臺(tái)灣省' when?'81'?then?'香港特別行政區(qū)' when?'82'?then?'澳門特別行政區(qū)' else?'未知'????? end???as?省份?, year(curdate())-if(length(id_number)=18,substring(id_number,7,4),if(length(id_number)=15,concat('19',substring(id_number,7,2)),null))?as?年齡,? case?if(length(id_number)=18,?cast(substring(id_number,17,1)?as?UNSIGNED)%2,?if(length(id_number)=15,cast(substring(id_number,15,1)?as?UNSIGNED)%2,3))? when?1?then?'男' when?0?then?'女' else?'未知' end?as?性別 from?t_users?limit?10

+--------------------------+--------+--------+ |?省份?????????????????????|?年齡???|?性別???| +--------------------------+--------+--------+ |?廣東省???????????????????|?????40?|?男?????| |?新疆維吾爾自治區(qū)?????????|?????25?|?男?????| |?廣東省???????????????????|?????22?|?男?????| |?浙江省???????????????????|?????24?|?女?????| |?廣東省???????????????????|?????38?|?男?????| |?未知?????????????????????|???NULL?|?未知???| |?河南省???????????????????|?????25?|?男?????| |?廣東省???????????????????|?????25?|?女?????| |?江西省???????????????????|?????37?|?女?????| |?廣東省???????????????????|?????28?|?女?????| +--------------------------+--------+--------+ 10?rows?in?set?(0.00?sec)