BEGIN
DECLARE id17 VARCHAR (17);
DECLARE id18 VARCHAR (18);
DECLARE retIc VARCHAR (18) ;
DECLARE v_sum INT ;
DECLARE v_y INT ;
DECLARE v_i INT ;
DECLARE is_num VARCHAR(1);
SET @wf = "7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2" ;
-- 定义数组存放校验码（check code）
SET @cc = "1,0,X,9,8,7,6,5,4,3,2" ;
SET v_sum = 0 ;
SET v_y = 0 ;
SET v_i = 1 ;
select (identityCard REGEXP '[^0-9.]') into @is_num;
-- 判断传入id长度
IF CHAR_LENGTH(identityCard) <>15 THEN
SET retIc = identityCard ;
ELSE
IF @is_num = 0 THEN -- 传入15位证件号码均为数字
SET id17 = CONCAT(
CONCAT(SUBSTRING(identityCard, 1, 6), '19'),
SUBSTRING(identityCard, 7)
) ;
-- 十七位数字本体码加权求和
REPEAT
SET v_sum = v_sum + SUBSTRING(id17, v_i, 1) * SUBSTRING_INDEX(
SUBSTRING_INDEX(@wf, ',', v_i),
",",
- 1
) ;
SET v_i = v_i + 1 ;
UNTIL v_i > 17
END REPEAT;
-- 计算模
SET v_y = MOD(v_sum, 11) ;
-- 17位数字 + 通过模得到对应的校验码
SET id18 = CONCAT(
id17,
SUBSTRING_INDEX(
SUBSTRING_INDEX(@cc, ",", v_y + 1),
",",
- 1
)
) ;
SET retIc = id18 ;
END IF;
END IF ;
RETURN retIc;
END