一、校验规则
-- 入参:身份证号码(varchar)
-- 返回值(varchar):1:通过,0:不通过
-- 校验规则:
-- 1,判断入参长度,为15或者18位,否则返回校验不通过(0);长度为15位,则走15位检测逻辑,长度为18位,则走18为检测逻辑;
-- 2,15位身份证号码检测逻辑:
-- 15 位身份证编码规则为:DDDDDD YYMMDD XXS
-- DDDDDD 6位地区编码
-- YYMMDD 出生年月。年份用 2 位表示
-- 月份,数字,值范围在1-12
-- 日,数字,值范围在1-31(暂未考虑 平闰年 2月29 30 31 以及小月没有31号的情形)
-- XXS 顺序码。其中 S 为性别识别码,奇数为男,偶数为女
-- 3,18位身份证号编码规则为:DDDDDD YYYYMMDD XXX Y
-- DDDDDD 6 位地区编码。其中,前两位为省份编码,必须是11-15 21-23 31-37 41-46 50-54 61-65
-- YYYYMMDD 出生年月。年份用 4 位表示。
-- 年份4位必须19、20开头
-- 月份,数字,值范围在1-12
-- 日,数字,值范围在1-31(暂未考虑 平闰年 2月29 30 31 以及小月没有31号的情形)
-- XXX 顺序码。奇数为男,偶数为女
-- Y 校验位。前 17 位值计算而得。
-- 3-2,18 位身份证,末位校验码-校验规则(这一逻辑在另一篇中实现,地址链接为:MySQL-18位身份证-末位校验码验证-自定义函数实现_人间四月芳菲尽~~~的博客-CSDN博客):
--关于校验码
--18 位身份证最末位是校验码,是按一定的规则产生的,用于校验身份证真伪,按理这规则属于保密的,
--X 代表的是其中一个校验码,是 10。校验码从 0 到 X 共 11 个。
--校验码是通过一系列数学计算得出来的,具体校验的计算方式如下:
--
--公式为:∑(Ai×Wi)(mod 11)
--i——表示号码字符从右至左包括校验码在内的位置序号;
--Ai——表示第 i 位置上的号码字符值;
--Wi——示第 i 位置上的加权因子(其值已定),其数值依据公式 Wi=2^(i-1)(mod 11) 计算得出。
--其各位对应的值依次为:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
--
--例如:某公民身份号码为 34052419800101001V(V为校验码),对前 17 位数字本体码首先按照上面公式加权求和计算:
--i: 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1
--Ai: 3 4 0 5 2 4 1 9 8 0 0 1 0 1 0 0 1 V
--Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 1
-- --
--∑(Ai×Wi) =( 21 + 36 + 0 + 25 + 16 + 16 + 2 + 9 + 48 + 0 + 0 + 9 + 0 + 5 + 0 + 0 + 2 ) = 189
--∑(Ai×Wi)(mod 11) = 189 mod 11 = 2
--
--然后根据计算的结果,从下面的表中查出相应的校验码。
--
--其中 X 表示计算结果为 10。
--
--∑(Ai×Wi)(mod 11): 0 1 2 3 4 5 6 7 8 9 10
--校验码字符值 V: 1 0 X 9 8 7 6 5 4 3 2
--
--根据上表,查出计算结果为 2 的校验码为 X,所以该人员的公民身份号码应该为 34052419800101001X。
--如果校验码不符合这个规则,则校验不通过。
二、身份证校验-代码实现
CREATE DEFINER=`root`@`%` FUNCTION `FUN_CARDNO_CHECK`( p_idcardno VARCHAR(255) )
RETURNS varchar(10) CHARSET utf8
DETERMINISTIC
BEGIN
-- 入参:身份证号码(varchar)
-- 返回值(varchar):1:通过,0:不通过
DECLARE V_RETURN VARCHAR(50);
DECLARE v_cardno_area_flag VARCHAR(50);
DECLARE v_cardno_year_num VARCHAR(50);
DECLARE v_cardno_month_num VARCHAR(50);
DECLARE v_cardno_day_num VARCHAR(50);
DECLARE v_cardno_order_num VARCHAR(50);
DECLARE v_cardno_last_verify_res VARCHAR(50); -- 18位中最后一位校验码验证结果, 1:通过,0:不通过
DECLARE v_len int;
DECLARE v_reg_1 int;
DECLARE v_reg_2 int;
SET V_RETURN = '1'; -- 1:通过
select length(p_idcardno) into v_len from dual ;
-- 校验规则:
-- 1,判断入参长度,为15或者18位,否则返回校验不通过(0);长度为15位,则走15位检测逻辑,长度为18位,则走18为检测逻辑;
-- 1,验证入参字符串长度,长度不合法,则返回0(不通过)
if v_len <> 15 and v_len <> 18 then
set V_RETURN = '0';
-- 2, 判断入参,不为15位数字,也不为18位(前17位位数字,最后一位为数字或者X|x)的组合,返回0(校验不通过)
else
select p_idcardno regexp '^[0-9]{15}$' , p_idcardno regexp '^[0-9]{17}[0-9Xx]{1}$'
into v_reg_1 , v_reg_2
from dual;
if v_reg_1 = 0 and v_reg_2 = 0 then
set V_RETURN = '0'; -- 返回0(校验不通过)
else
-- 3,验证 地区编码 ,不合法,则返回0(不通过)
select case when LEFT(p_idcardno, 2) in (
'11','12','13','14','15',
'21','22','23',
'31','32','33','34','35','36','37',
'41','42','43','44','45','46',
'50','51','52','53','54',
'61','62','63','64','65'
) then '1' else '0' end
into v_cardno_area_flag from dual;
-- DDDDDD 6位地区编码。其中,前两位为省份编码,必须是11-15 21-23 31-37 41-46 50-54 61-65
if v_cardno_area_flag = '0' THEN -- 编码不合法
set V_RETURN = '0'; -- 返回0(校验不通过)
else
-- 4,校验生日信息
select
case when v_len = 15 then right(left(p_idcardno, 10), 2)
when v_len = 18 then right(left(p_idcardno, 12), 2) end
into v_cardno_month_num from dual ;
select
case when v_len = 15 then right(left(p_idcardno, 12), 2)
when v_len = 18 then right(left(p_idcardno, 14), 2) end
into v_cardno_day_num from dual;
-- 4-1,校验月份 month
if v_cardno_month_num < 1 or v_cardno_month_num > 12 then
set V_RETURN = '0'; -- 返回0(校验不通过)
else
-- 4-2,校验日期 day ,数字,值范围在1-31(暂未考虑 平闰年 2月29 30 31 以及小月没有31号的情形)
if v_cardno_day_num < 1 or v_cardno_day_num > 31 then
set V_RETURN = '0'; -- 返回0(校验不通过)
else
--
if v_len = 18 then
set v_cardno_year_num = right(left(p_idcardno, 8), 2);
-- 5-1,年份4位必须19、20开头
if v_cardno_year_num not in('19', '20' ) then
set V_RETURN = '0'; -- 返回0(校验不通过)
else
-- 5-2,末位校验码校验 ,1:通过,0:不通过
select FUN_IDCARD_18_LAST_VERIFY_CODE_CHECK(p_idcardno) into V_RETURN;
-- (FUN_IDCARD_18_LAST_VERIFY_CODE_CHECK,这一函数逻辑在另一篇中实现,地址链接为:/mp_blog/creation/editor/130116610)
end if;
end if;
end if;
end if;
end if;
end if;
end if;
RETURN V_RETURN;
END