美文网首页
MySql整理篇之身份证校验

MySql整理篇之身份证校验

作者: 山水墨阳 | 来源:发表于2019-05-28 21:00 被阅读0次

规则描述

中国身份证分为一代15位和二代18位,排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码。顺序码的奇数分给男性,偶数分给女性。校验码是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码。详细规则参考百科身份证规则

场景

各种场景下会用到证件有效性校验,证件与性别、生日校验,现特整理编辑出MySql数据库环境下的证件校验,使用到自定义的两个函数:FUN_SPLIT(根据分隔符取字符串中的第几位)、FUN_VERIFY_CERT(身份证校验)。

详细设计

FUN_SPLIT函数

DELIMITER $$
DROP FUNCTION IF EXISTS FUN_SPLIT$$
CREATE FUNCTION FUN_SPLIT(P_INPUT VARCHAR(500),P_SPLIT VARCHAR(20),P_INDEX INT)
    RETURNS VARCHAR(50) CHARSET utf8
    BEGIN
    DECLARE RTV_VALUE VARCHAR(50) DEFAULT '';
    IF P_INPUT IS NULL THEN 
        RETURN NULL;
    END IF;
    IF (P_SPLIT IS NULL OR P_SPLIT = '') THEN 
        RETURN P_INPUT;
    END IF;
    SET RTV_VALUE := SUBSTRING_INDEX(SUBSTRING_INDEX(P_INPUT,P_SPLIT,P_INDEX),P_SPLIT,-1);
    RETURN RTV_VALUE;
    END$$
DELIMITER ;

FUN_VERIFY_CERT函数

DELIMITER $$
DROP FUNCTION IF EXISTS FUN_VERIFY_CERT$$
CREATE
    /*
    @Param ID_NUMBER 证件号
    @Param P_SEX 性别 0-女 1-男 可传入中文或码值
    @Param P_BIRTHDAY 生日
    备注:性别生日不校验可以传入NULL,生日格式为 yyyymmdd 或 yyyy-mm-dd
    */
    FUNCTION FUN_VERIFY_CERT(ID_NUMBER VARCHAR(20), P_SEX VARCHAR(1),P_BIRTHDAY VARCHAR(10))
    RETURNS VARCHAR(200) CHARSET utf8
    
    BEGIN
    DECLARE R_RETURN VARCHAR(50) DEFAULT '';-- 返回值
    DECLARE V_TEMP VARCHAR(50) DEFAULT '';-- 临时变量
    DECLARE V_HANDLER VARCHAR(50) DEFAULT 'OK';-- 临时变量
    DECLARE V_TMP_15_18 VARCHAR(50);-- 18 15位证件号
    DECLARE V_ID_SUM  BIGINT DEFAULT 0;-- 证件系数求和
    DECLARE V_ID_SEX VARCHAR(10) DEFAULT '';-- 证件性别
    DECLARE V_ID_BIRTH VARCHAR(10) DEFAULT '';-- 证件出生日期
    DECLARE V_ID_TEMP BIGINT; -- 证件号临时
    DECLARE V_IN_TEMP BIGINT; -- 系数临时
    DECLARE V_IM_TEMP BIGINT; -- mod 临时
    DECLARE V_INDEX BIGINT DEFAULT 1;-- 遍历索引
    DECLARE V_N VARCHAR(40) DEFAULT '7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2';-- 身份证系数
    DECLARE V_M VARCHAR(25) DEFAULT '1,0,X,9,8,7,6,5,4,3,2';-- 求MOD得的余数
    DECLARE CONTINUE HANDLER FOR 1292 SET V_HANDLER='日期格式不对';
    DECLARE CONTINUE HANDLER FOR 1690 SET V_HANDLER='校验字段过长';
    -- 判断非空
    IF (ID_NUMBER IS NULL OR ID_NUMBER = '') THEN 
        SET R_RETURN := '';
        RETURN R_RETURN;
    END IF;
    -- 判断长度
    IF (LENGTH(ID_NUMBER) <> 15 AND LENGTH(ID_NUMBER) <> 18) THEN
        SET R_RETURN := CONCAT(ID_NUMBER,'长度非[15,18]');
        RETURN R_RETURN;
    END IF;
    
    -- 15位证件号校验
    IF (LENGTH(ID_NUMBER) = 15) THEN
        BEGIN
            SET V_TMP_15_18 := CONCAT(SUBSTR(ID_NUMBER, 0, 6),'19',SUBSTR(ID_NUMBER, 7));
            -- 循环计算身份证前17位和权加因子的相乘得到的总合
            myloop:LOOP
                SET V_ID_TEMP := CAST(SUBSTR(V_TMP_15_18,V_INDEX,1) AS SIGNED);
                SET V_IN_TEMP := CAST(FUN_SPLIT(V_N,',',V_INDEX) AS SIGNED);
                
                SET V_ID_SUM := V_ID_SUM + V_ID_TEMP * V_IN_TEMP;
                
                SET V_INDEX := V_INDEX+1;
                
                IF V_INDEX > 17 THEN  LEAVE myloop;END IF;
            END LOOP myloop;
            -- 将得到的总合除以11得到一个余数,余数对应相应值
            SET V_IM_TEMP := FUN_SPLIT(V_M,',',MOD(V_ID_SUM,11) + 1);
            -- 校验位比较 15位不存在
            
            -- 性别取值:0-女 1-男
            SELECT CASE MOD((CAST(SUBSTR(ID_NUMBER, 15, 1) AS SIGNED)), 2) 
                   WHEN 0 THEN '0' ELSE '1' END INTO V_ID_SEX;
            -- 性别赋值
            IF V_ID_SEX = '0' THEN SET V_TEMP := '0-女'; END IF; 
            IF V_ID_SEX = '1' THEN SET V_TEMP := '1-男'; END IF;
            -- 传入性别默认:0-女 1-男 ,中文则转码转码
            IF (P_SEX IS NOT NULL AND P_SEX <> '') THEN 
                IF P_SEX = '女' THEN SET P_SEX:= '0'; END IF;
                IF P_SEX = '男' THEN SET P_SEX:= '1'; END IF;
                -- 比较性别
                IF (V_ID_SEX <> P_SEX) THEN
                    RETURN CONCAT(ID_NUMBER,'性别不匹配,证件号性别为:',V_TEMP);
                END IF;
            END IF;
            
            -- 出生时间取值
                SET @year  := SUBSTR(V_TMP_15_18, 1, 4);-- 年
                SET @month := SUBSTR(V_TMP_15_18, 5, 2);-- 月
                SET @day   := SUBSTR(V_TMP_15_18, 7, 2);-- 日
                SET V_ID_BIRTH := CONCAT(@year,'-',@month,'-',@day);
                SET V_TEMP := '';-- 恢复临时变量
                IF (P_BIRTHDAY IS NOT NULL AND P_BIRTHDAY <> '') THEN 
                BEGIN
                  SELECT DATE_FORMAT(P_BIRTHDAY,'%Y-%m-%d') INTO V_TEMP;
                  IF (V_TEMP IS NOT NULL AND V_TEMP <> '') THEN 
                    IF V_ID_BIRTH <> V_TEMP THEN
                        RETURN CONCAT(ID_NUMBER,'生日不匹配,证件生日为:',V_ID_BIRTH);
                    END IF;
                    IF V_ID_BIRTH = V_TEMP THEN 
                        -- return CONCAT('V_ID_BIRTH:',V_ID_BIRTH,',P_BIRTHDAY:',V_TEMP);
                        -- 身份其他信息校验
                        SET V_TEMP := '';
                    END IF;
                  ELSE 
                      RETURN CONCAT('生日参数:',P_BIRTHDAY,'格式(yyyy-mm-dd)不对');
                  END IF;
                END;
                END IF;
            -- 返回
            RETURN CONCAT(ID_NUMBER);
        END;
    END IF;
    
    -- 18位证件号校验
    IF (LENGTH(ID_NUMBER) = 18) THEN
        BEGIN   
            SET V_INDEX = 1;
            SET V_TEMP := '';           
            SET V_ID_SUM := 0;
            SET V_TMP_15_18 := ID_NUMBER;
            -- 循环计算身份证前17位和权加因子的相乘得到的总合
            myloop:LOOP
                SET V_ID_TEMP := CAST(SUBSTR(V_TMP_15_18,V_INDEX,1) AS SIGNED);
                SET V_IN_TEMP := CAST(FUN_SPLIT(V_N,',',V_INDEX) AS SIGNED);
                
                SET V_ID_SUM := V_ID_SUM + V_ID_TEMP * V_IN_TEMP;
                
                SET V_INDEX := V_INDEX+1;
                
                IF V_INDEX > 17 THEN  LEAVE myloop;END IF;
            END LOOP myloop;
            
            -- 将得到的总合除以11得到一个余数,余数对应相应值
            SET V_IM_TEMP := FUN_SPLIT(V_M,',',MOD(V_ID_SUM,11) + 1);
            SET V_TEMP := UPPER(SUBSTR(V_TMP_15_18, 18, 1));
            
            -- -- 校验位比较 第18位为校验位
            IF V_IM_TEMP <> V_TEMP THEN
                RETURN CONCAT(ID_NUMBER,'校验位不正确,',V_IM_TEMP,'!=',V_TEMP);
            END IF;
            
            -- 性别取值:0-女 1-男
            SELECT CASE MOD((CAST(SUBSTR(ID_NUMBER, 17, 1) AS SIGNED)), 2) 
                   WHEN 0 THEN '0' ELSE '1' END INTO V_ID_SEX;
            -- 性别赋值
            IF V_ID_SEX = '0' THEN SET V_TEMP := '0-女'; END IF; 
            IF V_ID_SEX = '1' THEN SET V_TEMP := '1-男'; END IF;
            -- 传入性别默认:0-女 1-男 ,中文则转码转码
            IF (P_SEX IS NOT NULL AND P_SEX <> '') THEN 
                IF P_SEX = '女' THEN SET P_SEX:= '0'; END IF;
                IF P_SEX = '男' THEN SET P_SEX:= '1'; END IF;
                -- 比较性别
                IF (V_ID_SEX <> P_SEX) THEN
                    RETURN CONCAT(ID_NUMBER,'性别不匹配,证件号性别为:',V_TEMP);
                END IF;
            END IF;
            
            -- 出生时间取值
                SET @year  := SUBSTR(V_TMP_15_18, 7, 4);-- 年
                SET @month := SUBSTR(V_TMP_15_18, 11,2);-- 月
                SET @day   := SUBSTR(V_TMP_15_18, 13,2);-- 日
                SET V_ID_BIRTH := CONCAT(@year,'-',@month,'-',@day);
                SET V_TEMP := '';-- 恢复临时变量
                IF (P_BIRTHDAY IS NOT NULL AND P_BIRTHDAY <> '') THEN 
                BEGIN
                  SELECT DATE_FORMAT(P_BIRTHDAY,'%Y-%m-%d') INTO V_TEMP;
                  IF (V_TEMP IS NOT NULL AND V_TEMP <> '') THEN 
                    IF V_ID_BIRTH <> V_TEMP THEN
                        RETURN CONCAT(ID_NUMBER,'生日不匹配,证件生日为:',V_ID_BIRTH);
                    END IF;
                    IF V_ID_BIRTH = V_TEMP THEN 
                        -- return CONCAT('V_ID_BIRTH:',V_ID_BIRTH,',P_BIRTHDAY:',V_TEMP);
                        -- 身份其他信息校验
                        SET V_TEMP := '';
                    END IF;
                  ELSE 
                      RETURN CONCAT('生日参数:',P_BIRTHDAY,'格式(yyyy-mm-dd)不对');
                  END IF;
                END;
                END IF;
            -- 返回
            RETURN CONCAT(ID_NUMBER);
        END;
    END IF;
    -- 最后一步无问题,返回本身证件号
    IF V_HANDLER <> 'OK' THEN 
        SET R_RETURN = V_HANDLER;
        RETURN R_RETURN;
    END IF;
    SET R_RETURN := ID_NUMBER;
    RETURN R_RETURN;
    END$$
    
DELIMITER ;

使用

以上两个函数直接Copy到数据库执行,在查询逻辑或者需要使用的地方直接使用函数FUN_VERIFY_CERT即可。例如:

SELECT FUN_VERIFY_CERT('142223198310300212',NULL,NULL) AS CheckResul;

备注:性别和生日若无需校验可传递NULL即可,生日参数格式为yyyymmdd或yyyy-mm-dd。

相关文章

网友评论

      本文标题:MySql整理篇之身份证校验

      本文链接:https://www.haomeiwen.com/subject/otmjtctx.html