mysql从身份证号中提取生日、性别

SELECT
    p.ID,
    p.IDCardNo,
IF (
    LENGTH(p.IDCardNo) = 18,
    CAST(
        SUBSTRING(p.IDCardNo, 7, 8) AS DATETIME
    ),
    CAST(
        CONCAT(
            '19',
            SUBSTRING(p.IDCardNo, 7, 6)
        ) AS DATETIME
    )
) AS Birthday,
IF (
    SUBSTR(
        p.IDCardNo,
        LENGTH(p.IDCardNo) - 1,
        1
    ) % 2 = 1,
    '男',
        '女'
) AS sex
FROM
    users p
WHERE
    p.IsDelete = 2
AND p.IDCardNo REGEXP '[1-8][0-8][0-9]{16}|[1-8][0-8][0-9]{15}X|[1-8][0-8][0-9]{13}'
AND (
    length(p.IDCardNo) = 18
    OR LENGTH(p.idcardno) = 15
)

 ps: 此处身份证号正确性未做非常严谨的验证