[大数据]mysql根据15位或者18位身份证号计算年龄的自定义函数

mysql中，根据提供的15位或者18位身份证号和日期计算年龄

输入的参数：身份证和日期

1、判断输入的参数是否有效；

2、做去空处理；

3、分别根据15位和18位进行计算年龄（个人感觉其中某些步骤可以合并）；

最后对函数进行验证，如有错误烦请指正，谢谢哦～

CREATE DEFINER=`root`@`localhost` FUNCTION `cal_age`(sfzh varchar(32),  date varchar(32)) RETURNS varchar(10) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
    COMMENT '根据身份证号计算年龄'
BEGIN
	DECLARE AGE VARCHAR(10); -- 计算得到的年龄
	# CHECK INPUT 
	IF SFZH = '' OR DATE = '' THEN RETURN NULL;
	END IF;	
	SET SFZH = REPLACE(SFZH, ' ', '');
	SET DATE = REPLACE(DATE, ' ', '');
	# CHECK LENGTH
	IF (LENGTH(SFZH)<>18 AND LENGTH(SFZH)<>15) OR LENGTH(DATE)<>8 THEN RETURN NULL;
	END IF;
	# CALULATE LENGTH=18 SFZH
	IF LENGTH(SFZH) = 18 THEN
		IF SUBSTRING(SFZH,11,2) > SUBSTRING(DATE,5,2) THEN
			SET AGE = SUBSTRING(DATE,1,4) - SUBSTRING(SFZH,7,4) - 1;
			RETURN AGE;
		END IF;
		IF SUBSTRING(SFZH,11,2) = SUBSTRING(DATE,5,2) THEN
			IF SUBSTRING(SFZH,13,2) > SUBSTRING(DATE,7,2) THEN
				SET AGE = SUBSTRING(DATE,1,4) - SUBSTRING(SFZH,7,4) - 1;
				RETURN AGE;
			END IF;
			IF SUBSTRING(SFZH,13,2) <= SUBSTRING(DATE,7,2) THEN
				SET AGE = SUBSTRING(DATE,1,4) - SUBSTRING(SFZH,7,4);
				RETURN AGE;
			END IF;
		END IF;
		IF SUBSTRING(SFZH,11,2) < SUBSTRING(DATE,5,2) THEN
			SET AGE = SUBSTRING(DATE,1,4) - SUBSTRING(SFZH,7,4);
			RETURN AGE;
		END IF;
	END IF;
	# CALCUTE LENGTH=15 SFZH
	IF LENGTH(SFZH) = 15 THEN
		IF SUBSTRING(SFZH,9,2) > SUBSTRING(DATE,5,2) THEN
			SET AGE = SUBSTRING(DATE,1,4) - (SUBSTRING(SFZH,7,2)+1900) - 1;
			RETURN AGE;
		END IF;
		IF SUBSTRING(SFZH,9,2) = SUBSTRING(DATE,5,2) THEN
			IF SUBSTRING(SFZH,11,2) > SUBSTRING(DATE,7,2) THEN
				SET AGE = SUBSTRING(DATE,1,4) - (SUBSTRING(SFZH,7,2)+1900) - 1;
				RETURN AGE;
			END IF;
			IF SUBSTRING(SFZH,11,2) <= SUBSTRING(DATE,7,2) THEN
				SET AGE = SUBSTRING(DATE,1,4) - (SUBSTRING(SFZH,7,2)+1900);
				RETURN AGE;
			END IF;
		END IF;
		IF SUBSTRING(SFZH,9,2) < SUBSTRING(DATE,5,2) THEN
			SET AGE = SUBSTRING(DATE,1,4) - (SUBSTRING(SFZH,7,2)+1900);
			RETURN AGE;
		END IF;
	END IF;
END