mysql: 身份证校验等问题

根据〖中华人民共和国国家标准 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) ;-- 日期