MySql整理篇之身份證校驗

規(guī)則描述

中國身份證分為一代15位和二代18位，排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位數(shù)字校驗碼。順序碼的奇數(shù)分給男性，偶數(shù)分給女性。校驗碼是根據(jù)前面十七位數(shù)字碼，按照ISO 7064:1983.MOD 11-2校驗碼計算出來的檢驗碼。詳細(xì)規(guī)則參考百科身份證規(guī)則。

場景

各種場景下會用到證件有效性校驗，證件與性別、生日校驗，現(xiàn)特整理編輯出MySql數(shù)據(jù)庫環(huán)境下的證件校驗，使用到自定義的兩個函數(shù)：FUN_SPLIT（根據(jù)分隔符取字符串中的第幾位）、FUN_VERIFY_CERT（身份證校驗）。

詳細(xì)設(shè)計

FUN_SPLIT函數(shù)

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函數(shù)

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;-- 證件系數(shù)求和
    DECLARE V_ID_SEX VARCHAR(10) DEFAULT '';-- 證件性別
    DECLARE V_ID_BIRTH VARCHAR(10) DEFAULT '';-- 證件出生日期
    DECLARE V_ID_TEMP BIGINT; -- 證件號臨時
    DECLARE V_IN_TEMP BIGINT; -- 系數(shù)臨時
    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';-- 身份證系數(shù)
    DECLARE V_M VARCHAR(25) DEFAULT '1,0,X,9,8,7,6,5,4,3,2';-- 求MOD得的余數(shù)
    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));
            -- 循環(huán)計算身份證前17位和權(quán)加因子的相乘得到的總合
            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得到一個余數(shù)，余數(shù)對應(yīng)相應(yīng)值
            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;
            -- 傳入性別默認(rèn)：0-女 1-男 ，中文則轉(zhuǎn)碼轉(zhuǎn)碼
            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 := '';-- 恢復(fù)臨時變量
                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('生日參數(shù):',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;
            -- 循環(huán)計算身份證前17位和權(quán)加因子的相乘得到的總合
            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得到一個余數(shù)，余數(shù)對應(yīng)相應(yīng)值
            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;
            -- 傳入性別默認(rèn)：0-女 1-男 ，中文則轉(zhuǎn)碼轉(zhuǎn)碼
            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 := '';-- 恢復(fù)臨時變量
                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('生日參數(shù):',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 ;

使用

以上兩個函數(shù)直接Copy到數(shù)據(jù)庫執(zhí)行，在查詢邏輯或者需要使用的地方直接使用函數(shù)FUN_VERIFY_CERT即可。例如：

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

備注：性別和生日若無需校驗可傳遞NULL即可，生日參數(shù)格式為yyyymmdd或yyyy-mm-dd。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容