mysql身份证号校验函数数据库tmmm666的博客-

CREATE DEFINER=`tm` FUNCTION `check_id_card`(v_card varchar(32)) RETURNS varchar(32) CHARSET utf8 BEGIN DECLARE v_flag varchar(32) DEFAULT ''; -- 是否有效标志 DECLARE v_sum varchar(32) DEFAULT ''; -- 校验第一步求和 DECLARE v_mod varchar(32) DEFAULT ''; -- 校验第二步取余 DECLARE i_flag varchar(32) DEFAULT ''; -- 校验第三步计算校验位 -- 去空格 SET v_card = TRIM(v_card); -- 长度不等于18为空 IF LENGTH(v_card) <> 18 THEN RETURN 0; END IF; -- 判断区划代码前两位 IF SUBSTRING(v_card,1,2) NOT IN (11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,53,61,62,63,64,65,71,81,83) THEN RETURN 0; END IF; -- 判断年份前两位 IF SUBSTRING(v_card,7,2) NOT BETWEEN 19 AND 21 THEN RETURN 0; END IF; -- 判断月份 IF SUBSTRING(v_card,11,2) NOT BETWEEN 01 AND 12 THEN RETURN 0; END IF; -- 判断31天日期 IF SUBSTRING(v_card,11,2) IN (01,03,05,07,08,10,12) THEN IF SUBSTRING(v_card,13,2) NOT BETWEEN 01 AND 31 THEN RETURN 0; END IF; END IF; -- 判断30天日期 IF SUBSTRING(v_card,11,2) IN (04,06,09,11) THEN IF SUBSTRING(v_card,13,2) NOT BETWEEN 01 AND 30 THEN RETURN 0; END IF; END IF; -- 判断2月日期 IF SUBSTRING(v_card,11,2)=02 THEN IF (SUBSTRING(v_card,7,4)%4 =0 AND SUBSTRING(v_card,7,4)%100 <>0) OR (SUBSTRING(v_card,7,4)%400 =0) THEN IF SUBSTRING(v_card,13,2) NOT BETWEEN 01 AND 29 THEN RETURN 0; END IF; ELSEIF SUBSTRING(v_card,13,2) NOT BETWEEN 01 AND 28 THEN RETURN 0; END IF; END IF; -- 判断校验位范围 IF SUBSTRING(v_card,18,1) NOT IN ('1','2','3','4','5','6','7','8','9','x','X') THEN RETURN 0; END IF; -- 判断校验位 SET v_sum=(SUBSTRING(v_card,1,1)*7)+(SUBSTRING(v_card,2,1)*9)+(SUBSTRING(v_card,3,1)*10)+ (SUBSTRING(v_card,4,1)*5)+(SUBSTRING(v_card,5,1)*8)+(SUBSTRING(v_card,6,1)*4)+ (SUBSTRING(v_card,7,1)*2)+(SUBSTRING(v_card,8,1)*1)+(SUBSTRING(v_card,9,1)*6)+ (SUBSTRING(v_card,10,1)*3)+(SUBSTRING(v_card,11,1)*7)+(SUBSTRING(v_card,12,1)*9)+ (SUBSTRING(v_card,13,1)*10)+(SUBSTRING(v_card,14,1)*5)+(SUBSTRING(v_card,15,1)*8)+ (SUBSTRING(v_card,16,1)*4)+(SUBSTRING(v_card,17,1)*2); SET v_mod=v_sum%11; IF v_mod = 0 THEN SET i_flag = '1'; END IF; IF v_mod = 1 THEN SET i_flag = '0'; END IF; IF v_mod = 2 THEN SET i_flag = 'X'; END IF; IF v_mod = 3 THEN SET i_flag = '9'; END IF; IF v_mod = 4 THEN SET i_flag = '8'; END IF; IF v_mod = 5 THEN SET i_flag = '7'; END IF; IF v_mod = 6 THEN SET i_flag = '6'; END IF; IF v_mod = 7 THEN SET i_flag = '5'; END IF; IF v_mod = 8 THEN SET i_flag = '4'; END IF; IF v_mod = 9 THEN SET i_flag = '3'; END IF; IF v_mod = 10 THEN SET i_flag = '2'; END IF; IF i_flag=SUBSTRING(v_card,18,1) THEN SET v_flag ='1'; RETURN v_flag; ELSE SET v_flag ='0'; RETURN v_flag; END IF; END