Mysql根据身份证更新出生日期及年龄sql语句

最近一个客户的系统要升级,并要求在数据库基本信息中新增年龄和出生日期字段信息. 我心想这么"难" 报价一定要高一点了 于是我废了九牛二虎之力写了一条sql语句,将mysql数据库新增的出生日期和年龄根据身份证号给生成了

数据库中字段信息如下

整理查询sql语句

### 先整理出查询语句  
select DATE_FORMAT(SUBSTR(ident_card,7,8),'%Y-%m-%d') as peop_birth,ident_card ,2021-SUBSTR(ident_card,7,4) as age from 
hrp_comm_people 
where LENGTH(ident_card)=18 and (substr(ident_card,7,2) = '19' OR substr(ident_card,7,1)='2') 

批量更新操作

update 
hrp_comm_people  set peop_birth = DATE_FORMAT(SUBSTR(ident_card,7,8),'%Y-%m-%d') ,age=2021-SUBSTR(ident_card,7,4)
where LENGTH(ident_card)=18 and (substr(ident_card,7,2) = '19' OR substr(ident_card,7,1)='2') 

### 如果你的身份证号错误的太多了要必要校验一下身份证号 可以使用如下语句 
### 该mysql正则校验身份证合法性方法取自 网友https://blog.csdn.net/jacklin929/article/details/52515810?utm_medium=distribute.pc_relevant_t0.none-task-blog-searchFromBaidu-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-searchFromBaidu-1.control

update 
hrp_comm_people  set peop_birth = DATE_FORMAT(SUBSTR(ident_card,7,8),'%Y-%m-%d') ,age=2021-SUBSTR(ident_card,7,4)
where LENGTH(ident_card)=18 
and ident_card regexp '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)$'

更新后效果