mysql查询身份证和出生日期是否匹配

描述

数据库中人员的信息可能由于人工录入出错、数据源不一致等问题导致身份证与出生日期字段不一致。
本文提供一个简单方法，查询数据库人员表中人员的身份证号与出生日期是否匹配，并提供基于身份证进行出生日期更新的方法。

数据准备

新建数据表、写入测试数据：

CREATE TABLE test_member (
	id BIGINT auto_increment NOT NULL COMMENT '主键',
	name varchar(32) NULL COMMENT '姓名',
	id_number varchar(32) NULL COMMENT '身份证号',
	birthday DATETIME NULL COMMENT '出生日期',
	CONSTRAINT test_member_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci
COMMENT='（测试）人员表';

insert into 
test_member(name,id_number,birthday)
values
('张建军','440101199008018888','1977-08-01'),
('李国庆','440101199510018888','1995-10-01'),
('王冬至','440101200012228888','2000-06-21'),
('赵建军','440101200508018888','2000-06-21'),
('孙国庆',null,'2010-10-01')

数据表正确创建、数据正确插入

查询方法

身份证号码的第7~14位是个人的出生日期，可以基于这段信息与表中出生日期字段做匹配。

select 
tm.*,
if(subString(id_number,7,8) = replace(subString(birthday,1,10),'-',''),'匹配','不匹配') birthday_right 
from test_member tm

查询结果：

更正方法

可以基于有效的身份证号取出出生日期位，对数据表中的出生日期进行更正：

update 
test_member 
set birthday = if(id_number is not null,
	concat(
		subString(id_number,7,4),
		'-',
		subString(id_number,11,2),
		'-',
		subString(id_number,13,2)
	),
	birthday) 

执行更正脚本后，再次运行执行身份证与出生日期的匹配脚本，可以看到有有效身份证号的数据都能正确匹配了。