-- -----------------------------------------------------
-- 功能描述 : 15位身份证号转18位
-- 调用示例：SELECT Get18Id('513127831108061');
-- -----------------------------------------------------
DROP FUNCTION IF EXISTS Get18Id;
DELIMITER //
CREATE FUNCTION Get18Id ( id15 VARCHAR ( 15 ) ) RETURNS VARCHAR ( 18 )
BEGIN
-- 【1】定义返回结果的变量：retIDNoNew
DECLARE retIDNoNew VARCHAR ( 18 );
-- 【2】定义其他变量，赋初值
DECLARE id17 VARCHAR ( 17 );
DECLARE id18 VARCHAR ( 18 );
DECLARE v_sum INT;
DECLARE v_y INT;
DECLARE v_i INT;
-- 赋初值
SET retIDNoNew = id15;
-- 定义数组存放加权因子（weight factor）
SET @weight_factor = "7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2";
-- 定义数组存放校验码（check code）
SET @check_code = "1,0,X,9,8,7,6,5,4,3,2";
SET v_sum = 0;
SET v_y = 0;
SET v_i = 1;
-- 【3】主要计算逻辑：只处理15位的，如果传入参数不是15位，原样返回。
-- 判断传入id长度
IF CHAR_LENGTH(id15) <> 15 THEN
SET retIDNoNew = id15;
ELSE -- 加上两位年'19'
SET id17 = CONCAT(CONCAT(SUBSTRING(id15,1,6),'19'), SUBSTRING(id15,7)); -- 十七位数字本体码加权求和
WHILE v_i <= 17 DO
SET v_sum = v_sum + CAST(SUBSTRING(id17,v_i,1) AS SIGNED) * CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@weight_factor,',',v_i),",",-1) AS SIGNED);
SET v_i = v_i + 1;
END WHILE;
-- 计算模
SET v_y = MOD(v_sum,11); -- 17位数字 + 通过模得到对应的校验码
SET id18 = CONCAT(id17,SUBSTRING_INDEX(SUBSTRING_INDEX(@check_code,",",v_y + 1),",",-1));
SET retIDNoNew = id18;
END IF;
-- 【4】返回值
RETURN retIDNoNew;
END //
DELIMITER;