mysql身份证校验码_MySQL通过自定义函数验证身份证号合法性

创建自定义函数脚本如下

DELIMITER $$

DROP FUNCTION IF EXISTS `is_id_card`$$

CREATE FUNCTION `is_id_card` (number VARCHAR (20) CHARSET utf8) RETURNS TINYINT (1)

BEGIN

DECLARE flag BOOL DEFAULT FALSE ;

IF (

LENGTH(number) = 18

and number REGEXP '[1-9]{1}[0-9]{17}|[1-9]{1}[0-9]{16}X'

AND substr(number,7,4) between '1900' and year(now())

AND SUBSTR(number,11,2) BETWEEN '01' AND '12'

AND SUBSTR(number,13,2) BETWEEN '01' AND DAY(LAST_DAY(CONCAT(SUBSTR(number,7,4),'-',SUBSTR(number,11,2),'-01')))

OR (

LENGTH(number) = 15

AND number REGEXP '[1-9]{1}[0-9]{14}'

AND substr(number,7,2) between '00' and '99'

AND SUBSTR(number,9,2) BETWEEN '01' AND '12'

AND SUBSTR(number,11,2) BETWEEN '01' AND DAY(LAS