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

 1 SELECT
 2     p.ID,
 3     p.IDCardNo,
 4 IF (
 5     LENGTH(p.IDCardNo) = 18,
 6     CAST(
 7         SUBSTRING(p.IDCardNo, 7, 8) AS DATETIME
 8     ),
 9     CAST(
10         CONCAT(
11             '19',
12             SUBSTRING(p.IDCardNo, 7, 6)
13         ) AS DATETIME
14     )
15 ) AS Birthday,
16 IF (
17     SUBSTR(
18         p.IDCardNo,
19         LENGTH(p.IDCardNo) - 1,
20         1
21     ) % 2 = 1,
22     '男',
23         '女'
24 ) AS sex
25 FROM
26     users p
27 WHERE
28     p.IsDelete = 2
29 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}'
30 AND (
31     length(p.IDCardNo) = 18
32     OR LENGTH(p.idcardno) = 15
33 )

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