MySQL校验函数
身份证校验函数
校验之前先开启一个安全参数,
log_bin_trust_function_creators 默认为0，是不允许function的同步的，开启这个参数，就可以创建成功了。
查看语句：mysql> show variables like '%fun%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
修改语句：mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%fun%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
如果是在有master上开启了该参数，记得在slave端也要开启这个参数(salve需要stop后再重新start)，否则在master上创建函数会导致replaction中断。
————————————————
创建函数代码如下：
CREATE DEFINER=`root`@`localhost` 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 = REPLACE(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,54,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 ('0','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
最终结果返回0则代表错误 1为正确
