根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定,公民身份号码是特征组合码,由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。 地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。 出生日期码表示编码对象出生的年、月、日,其中年份用四位数字表示,年、月、日之间不用分隔符。 顺序码表示同一地址码所标识的区域范围内,对同年、月、日出生的人员编定的顺序号。顺序码的奇数分给男性,偶数分给女性。 校验码是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。 出生日期计算方法。 15位的身份证编码首先把出生年扩展为4位,简单的就是增加一个19或18,这样就包含了所有1800-1999年出生的人; 2000年后出生的肯定都是18位的了没有这个烦恼,至于1800年前出生的(\d|X)$/i 15位校验规则 6位地址编码+6位出生日期+3位顺序号 18位校验规则 6位地址编码+8位出生日期+3位顺序号+1位校验位 校验位规则 公式:∑(ai×Wi)(mod 11)……………………………………(1) 公式(1)中: i----表示号码字符从由至左包括校验码在内的位置序号; ai----表示第i位置上的号码字符值; Wi----示第i位置上的加权因子,其数值依据公式Wi=2^(n-1)(mod 11)计算得出。 i 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4
具体函数如下:
CREATE DEFINER=`ecifwrite`@`%` FUNCTION `check_identity_no`(`cardNo` varchar(30)) RETURNS varchar(100) CHARSET utf8
BEGIN
#Routine body goes here...
DECLARE result varchar(100) default '身份证行政编码不对' ;
IF length(cardNo) <> 18 && length(cardNo) <> 15 THEN
set result = '身份证号位数不对';
END IF;
IF length(cardNo) = 18 THEN
( select
case
-- 判断身份证号码前17位是否含除数字外的字符
when (substring(cardNo,1,17) REGEXP '[^0-9]') = 1 then '身份证号码前17位格式不正确'
-- 判断身份证的年份是否在合理范围内
when substring(cardNo,7,4) not between '1900' and YEAR(NOW()) then '身份证年份错误'
-- 判断身份证的月份是否在合理范围内
when substring(cardNo,11,2) not between '01' and '12' then '身份证月份错误'
-- 判断身份证日期是否在合理范围内
when substring(cardNo,13,2) not between 1 and day(DATE_SUB(date_add(str_to_date(CONCAT(substring(cardNo,7,4),'-',substring(cardNo,11,2),'-01'),'%Y-%m-%d'), interval 1 MONTH) ,INTERVAL 1 DAY )) then '身份证日期错误'
-- 判断身份证号码的第18位是否符合验证规则
when
mod((
substring(cardNo,1,1)*7+
substring(cardNo,2,1)*9+
substring(cardNo,3,1)*10+
substring(cardNo,4,1)*5+
substring(cardNo,5,1)*8+
substring(cardNo,6,1)*4+
substring(cardNo,7,1)*2+
substring(cardNo,8,1)*1+
substring(cardNo,9,1)*6+
substring(cardNo,10,1)*3+
substring(cardNo,11,1)*7+
substring(cardNo,12,1)*9+
substring(cardNo,13,1)*10+
substring(cardNo,14,1)*5+
substring(cardNo,15,1)*8+
substring(cardNo,16,1)*4+
substring(cardNo,17,1)*2
),11)
<>
(
case
when substring(cardNo,18,1)='1' then '0'
when substring(cardNo,18,1)='0' then '1'
when substring(cardNo,18,1) in ('X','x') then '2'
when substring(cardNo,18,1)='9' then '3'
when substring(cardNo,18,1)='8' then '4'
when substring(cardNo,18,1)='7' then '5'
when substring(cardNo,18,1)='6' then '6'
when substring(cardNo,18,1)='5' then '7'
when substring(cardNo,18,1)='4' then '8'
when substring(cardNo,18,1)='3' then '9'
when substring(cardNo,18,1)='2' then '10'
end
)
then '身份证验证错误'
else 'ok'
end INTO result
-- 用户信息表,包含所需要查询的身份证号码信息
from code_card_address b
where substring(cardNo,1,6)=b.card_no ) ;
END IF;
IF length(cardNo) = 15 THEN
( select
case
-- 判断身份证号码前17位是否含除数字外的字符
when (cardNo REGEXP '[^0-9]') = 1 then '身份证号码前17位格式不正确'
-- 判断身份证的年份是否在合理范围内
when substring(cardNo,7,2) not between '84' and '99' then '身份证年份错误'
-- 判断身份证的月份是否在合理范围内
when substring(cardNo,9,2) not between '01' and '12' then '身份证月份错误'
-- 判断身份证日期是否在合理范围内
when substring(cardNo,11,2) not between 1 and day(DATE_SUB(date_add(str_to_date(CONCAT('19',substring('110000500113863',7,2),'-',substring('110000500113863',9,2),'-01'),'%Y-%m-%d'), interval 1 MONTH) ,INTERVAL 1 DAY )) then '身份证日期错误'
else 'ok'
end INTO result
-- 用户信息表,包含所需要查询的身份证号码信息
from code_card b --身份证编码表 去网站下载 或公司提供
where substring(cardNo,1,6)=b.card_no ) ;
END IF;
RETURN result;
END
根据上方衍生的通过身份证获取出生日期,如下
CREATE DEFINER=`ecifwrite`@`%` FUNCTION `get_identity_birthday`(`cardNo` varchar(30)) RETURNS date
BEGIN
#Routine body goes here...
DECLARE result date ;
DECLARE flag varchar(100) default '' ;
IF length(cardNo) = 18 THEN
set flag = check_identity_no(cardNo) ;
if flag ='ok' then
set result= str_to_date(CONCAT(substring(cardNo,7,4),'-',substring(cardNo,11,2),'-',substring(cardNo,13,2)),'%Y-%m-%d');
END IF;
END IF;
IF length(cardNo) = 15 THEN
set flag = check_identity_no(cardNo) ;
if flag ='ok' then
set result= str_to_date(CONCAT('19',substring(cardNo,7,2),'-',substring(cardNo,9,2),'-',substring(cardNo,11,2)),'%Y-%m-%d');
END IF;
END IF;
RETURN result;
END
性别:
CREATE DEFINER=`ecifwrite`@`%` FUNCTION `get_identity_sex`(`cardNo` varchar(30)) RETURNS varchar(100) CHARSET utf8
BEGIN
#Routine body goes here...
DECLARE result varchar(100) ;
DECLARE flag varchar(100) default '' ;
IF length(cardNo) = 18 THEN
set flag = check_identity_no(cardNo) ;
if flag ='ok' then
set flag= CAST(substring(cardNo,17,1) as SIGNED)%2 ;
END IF;
END IF;
IF length(cardNo) = 15 THEN
set flag = check_identity_no(cardNo) ;
if flag ='ok' then
set flag= CAST(substring(cardNo,15,1) as SIGNED)%2;
END IF;
END IF;
IF flag = 1 THEN
set result ='男';
END IF;
IF flag = 0 THEN
set result ='女';
END IF;
RETURN result;
END
以及年龄等等
select TIMESTAMPDIFF(YEAR, get_identity_birthday('110000880113863'),DATE_FORMAT(NOW(), '%Y-%m-%d ')) ; --- 计算年龄
select CAST(substring(get_identity_birthday('110000880113863'),6,2) as SIGNED) ;-- 月份
select CAST(substring(get_identity_birthday('110000880113863'),9,2) as SIGNED) ;-- 日期