规则描述
中国身份证分为一代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。
