【mysql】从身份证号中提取出生日期和性别

-- 身份证号中提取出生日期
SELECT id,name,id_card,birth_date,gender,length(id_card), str_to_date(substring(id_card, 7, 8), '%Y%m%d') as birth_date2
FROM `wk_staff`
where id_card<>'';

update wk_staff set birth_date = str_to_date(substring(id_card, 7, 8), '%Y%m%d')
where id_card<>'';


-- 身份证号中提取性别: 0-男, 1-女
SELECT id,name,id_card,birth_date,gender,length(id_card), if(substring(id_card, length(id_card) - 1, 1) % 2 = 0, 1, 0) as gender2
FROM `wk_staff`
where id_card<>'';

update wk_staff set gender=if(substring(id_card, length(id_card)-1, 1) % 2 = 0, 1, 0)
where id_card<>'';