mysql 自定义函数验证证件号码

证件号码有着自己的验证规则，如果只正则验证位数和字符范围系统中很有可能会出现错误的证件号码，系统中有很大多数据可能是导入并未经过系统验证。查询出这些有问题的证件号码

此函数只适用于18位证件号码，因为我们的需求就是15位证件号码算错误的

CREATE FUNCTION `getErrorIdNumer`(idNumbe VARCHAR(20)) RETURNS bigint(1)
BEGIN
  DECLARE result  BIGINT(1) DEFAULT 0;
	
	SET @i = 1; -- 循环变量
	SET @sum = 0; -- 累计变量
	SET @res = 0;-- 累加变量对11取模的赋值变量
	SET @sign = 0;-- 证件号码每位赋值变量
	SET @idEnd = '0';
	
	
	IF idNumbe is NULL THEN
	  set result = 1;
	ELSEIF CHAR_LENGTH(idNumbe) != 18 THEN
	  set result = 1;
	-- 判断时间格式是否正确
	ELSEIF (DATE_FORMAT(SUBSTR(idNumbe,7,8),'%Y%m%d')  is NOT NULL) = 0 THEN
	  set result = 1;
	-- 判断出生日期是不是超出了当前时间
	ELSEIF (DATE_FORMAT(SUBSTR(idNumbe,7,8),'%Y%m%d')) > DATE_FORMAT(NOW(),'%Y%m%d') THEN
	  set result = 1;
  ELSE
	  -- 证件号码校验
		-- 校验规则各位加权值
		-- 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2
		WHILE @i <= 17 DO
			SET @sign = SUBSTR(idNumbe ,@i ,1);
			IF @i = 1 THEN
				SET @sum = @sum + (@sign * 7);
			ELSEIF @i = 2 THEN
				SET @sum = @sum + (@sign * 9);
			ELSEIF @i = 3 THEN
				SET @sum = @sum + (@sign * 10);
			ELSEIF @i = 4 THEN
				SET @sum = @sum + (@sign * 5);
			ELSEIF @i = 5 THEN
				SET @sum = @sum + (@sign * 8);
			ELSEIF @i = 6 THEN
				SET @sum = @sum + (@sign * 4);
			ELSEIF @i = 7 THEN
				SET @sum = @sum + (@sign * 2);
			ELSEIF @i = 8 THEN
				SET @sum = @sum + (@sign * 1);
			ELSEIF @i = 9 THEN
				SET @sum = @sum + (@sign * 6);
			ELSEIF @i = 10 THEN
				SET @sum = @sum + (@sign * 3);
			ELSEIF @i = 11 THEN
				SET @sum = @sum + (@sign * 7);
			ELSEIF @i = 12 THEN
				SET @sum = @sum + (@sign * 9);
			ELSEIF @i = 13 THEN
				SET @sum = @sum + (@sign * 10);
			ELSEIF @i = 14 THEN
				SET @sum = @sum + (@sign * 5);
			ELSEIF @i = 15 THEN
				SET @sum = @sum + (@sign * 8);
			ELSEIF @i = 16 THEN
				SET @sum = @sum + (@sign * 4);
			ELSE
				SET @sum = @sum + (@sign * 2);
			END IF;
			SET @i = @i +1;
		END WHILE;
  END IF;
	-- 对每位加权因子乘集 % 11 用来判断 第18位校检码
	SET @res = @sum % 11;
	-- 最后一位证件号码 '1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'
	SET @idEnd = SUBSTR(idNumbe ,-1);
	
	IF @res = 1 THEN
		IF @idEnd != '0' THEN
			SET result = 1;
		END IF;
	ELSEIF @res = 1 THEN
		IF @idEnd != '0' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 2 THEN
		IF @idEnd != 'X' OR @idEnd != 'x' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 3 THEN
		IF @idEnd != '9' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 4 THEN
		IF @idEnd != '8' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 5 THEN
		IF @idEnd != '7' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 6 THEN
		IF @idEnd != '6' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 7 THEN
		IF @idEnd != '5' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 8 THEN
		IF @idEnd != '4' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 9 THEN
		IF @idEnd != '3' THEN
			set result = 1;
		END IF;
	ELSEIF @res = 10 THEN
		IF @idEnd != '2' THEN
			set result = 1;
		END IF;
	END IF;
	
	
  RETURN  result;
END

如果需要校验15位的证件号码，可以查一下升位规则，将十五位证件号码升位成18位

函数返回值 0代表证件号码正确，1代表证件号码错误