MySql整理篇之身份证提取生日性别

应用场景

某些场景中，需要从国人身份证号中提取出生日期及性别，现整理出MySql版本的提取方法，了解其规则后，其他数据库异曲同工。
目前国人身份证分为一代和二代，一代15位，二代18为。基本区分如下：

18位证件号

1～6位为地区省市县编码，7～14位出生年月日，15～16所在地派出所，17位性别（奇男、偶女），18位为校验码

15位证件号

1～6位为地区省市县编码，7～12位出生年月日（年为两位），13～15为顺序码，末位区分男女（奇男、偶女）。

解决方案

解决方案为根据证件号规则，创建两个函数：fun_idno_birthday、fun_idno_sex提取。

出生日期提取

通过创建函数fun_idno_birthday获取，具体代码如下：

DELIMITER $$
DROP FUNCTION IF EXISTS fun_idno_birthday$$
CREATE FUNCTION fun_idno_birthday(idno  VARCHAR(25)) RETURNS VARCHAR(10) CHARSET utf8
BEGIN 
    DECLARE v_birth VARCHAR(10) DEFAULT NULL;
    DECLARE v_birth_1 VARCHAR(10) DEFAULT NULL;
    DECLARE v_birth_2 VARCHAR(10) DEFAULT NULL;
    IF idno IS NULL OR '' = idno THEN RETURN NULL; END IF;
    IF LENGTH(idno) = 18 THEN 
        SET v_birth = SUBSTRING(idno,7,8);
        SET v_birth_1 = LEFT(v_birth,4);
        SET v_birth_2 = RIGHT(v_birth,4);
        SET v_birth = CONCAT(v_birth_1,'-',LEFT(v_birth_2,2),'-',RIGHT(v_birth_2,2));
        RETURN v_birth;
    END IF;
    IF LENGTH(idno) = 15 THEN
        SET v_birth = CONCAT('19',SUBSTRING(idno,7,6));
        SET v_birth_1 = LEFT(v_birth,4);
        SET v_birth_2 = RIGHT(v_birth,4);
        SET v_birth = CONCAT(v_birth_1,'-',LEFT(v_birth_2,2),'-',RIGHT(v_birth_2,2));
        RETURN v_birth;
    END IF;
    RETURN NULL;
END$$
DELIMITER ;

性别提取

通过创建函数fun_idno_sex获取，具体代码如下：

DELIMITER $$
DROP FUNCTION IF EXISTS fun_idno_sex$$
CREATE FUNCTION fun_idno_sex(idno  VARCHAR(25)) RETURNS VARCHAR(10) CHARSET utf8
BEGIN 
    DECLARE v_num VARCHAR(10) DEFAULT NULL;
    IF idno IS NULL OR '' = idno THEN RETURN NULL; END IF;
    -- 倒数第二位为性别
    IF LENGTH(idno) = 18 THEN 
        SET v_num = LEFT(RIGHT(idno,2),1);
        IF v_num % 2 = 0 THEN RETURN '0';-- 偶女
        ELSE RETURN '1';-- 奇男
        END IF;
    END IF;
    -- 倒数第一位为性别
    IF LENGTH(idno) = 15 THEN
        SET v_num = RIGHT(idno,1);
        IF v_num % 2 = 0 THEN RETURN '0';-- 偶女
        ELSE RETURN '1';-- 奇男
        END IF;
    END IF;
    RETURN NULL;
END$$
DELIMITER ;

使用方法

直接在逻辑中使用，传递证件号作为参数。

SELECT fun_idno_birthday('52022119890408407X');
SELECT fun_idno_birthday('370986890623212');
SELECT fun_idno_sex('52022119890408407X');
SELECT fun_idno_sex('370986890623212');

注意: fun_idno_sex返回值：0-女 1-男，NULL为非有效证件号

最后编辑于：2020.06.24 11:47:34