逻辑
传入身份证号,正确返回该身份证号,错误返回NULL
使用方法
select is_valid_idnumber('111111111111111111')
代码
非强校验可以参考代码中的正则
delimiter //
CREATE FUNCTION is_valid_idnumber(idnumber VARCHAR(30)) RETURNS VARCHAR(100)
BEGIN
DECLARE check_sum INT;
DECLARE coefficient_arr varchar(100) ;
DECLARE idnumber_arr CHAR(17);
DECLARE i INT;
DECLARE check_code varchar(10);
-- 长度判断
IF LENGTH(idnumber) != 18 THEN
RETURN NULL;
END if;
-- 正则判断
IF idnumber not REGEXP '^[1-9][0-9]{5}(19|20)[0-9]{2}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])[0-9]{3}([0-9]|x|X)$' THEN
RETURN NULL;
END if;
-- 校验码判断
-- 系数数组
SET coefficient_arr = '7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2';
-- 前17位
SET idnumber_arr = SUBSTR(idnumber, 1, 17);
-- 每位乘以系数并累加
SET check_sum = 0;
SET i = 1;
WHILE i <= 17 DO
SET check_sum = check_sum + SUBSTR(idnumber_arr, i, 1) * substring_index(substring_index(coefficient_arr,',',i),',',-1) ;
SET i = i + 1;
END WHILE;
-- 结果模11
SET check_sum = check_sum % 11;
SET check_code = case SUBSTR(idnumber, 18, 1)
when '1' then '0'
when '0' then '1'
when 'x' then '2'
when 'X' then '2'
when '9' then '3'
when '8' then '4'
when '7' then '5'
when '6' then '6'
when '5' then '7'
when '4' then '8'
when '3' then '9'
when '2' then '10'
end;
-- 验证验证码是否相等
IF(check_sum <> check_code) THEN
RETURN NULL;
END IF;
RETURN idnumber;
end;
//
参考
https://blog.51cto.com/u_15069450/2577926
https://blog.csdn.net/weixin_45017098/article/details/130449417
https://blog.csdn.net/weixin_44241240/article/details/124290999