如何在MySQL中提取身份证号中的出生日期-mysql教程-PHP中文网

MySQL中用SUBSTR提取18位身份证出生日期应使用SUBSTR(id_card, 7, 8)，因pos从1开始；组合标准日期用CONCAT，转DATE类型必须用STR_TO_DATE(SUBSTR(...), '%Y%m%d')，避免CAST；兼容15位需CASE判断长度并补'19'；WHERE中慎用函数以防全表扫描。

如何在mysql中提取身份证号中的出生日期

MySQL中用SUBSTR提取18位身份证的出生日期

18位身份证号的第7到第14位是8位数字格式的出生日期（YYYYMMDD），SUBSTR是最直接、兼容性最好的提取方式。注意：它不校验身份证有效性，只做字符串截取。

常见错误是索引搞错——MySQL的SUBSTR(str, pos, len)中pos从1开始计数，不是0。写成SUBSTR(id_card, 6, 8)会少取一位年份，得到“2000010”这种错误结果。

SUBSTR(id_card, 7, 4) 提取年份（如'1992'）
SUBSTR(id_card, 11, 2) 提取月份（如'05'）
SUBSTR(id_card, 13, 2) 提取日期（如'21'）
组合成标准日期：CONCAT(SUBSTR(id_card, 7, 4), '-', SUBSTR(id_card, 11, 2), '-', SUBSTR(id_card, 13, 2))

用STR_TO_DATE把身份证子串转为DATE类型

如果后续要参与日期计算（比如查“年龄＞30”或“生日在本月”），必须把字符串转成DATE类型，否则BETWEEN或DATE_ADD会出错或隐式转换失败。

直接对SUBSTR结果用STR_TO_DATE最稳妥：STR_TO_DATE(SUBSTR(id_card, 7, 8), '%Y%m%d')。别用CAST(... AS DATE)——它在部分MySQL版本（如5.7）对'19920521'这种无分隔符格式支持不稳定，容易返回NULL。

成功返回：1992-05-21（DATE类型）
若id_card为空、长度不足18、或第7–14位含非数字字符，STR_TO_DATE返回NULL，需提前过滤
在WHERE条件中使用时，记得加IS NOT NULL判断，避免意外漏数据

处理15位身份证的兼容逻辑

老一代15位身份证没有年份前缀（只有YYMMDD），且无校验位。如果表里混存了15位和18位，不能统一用SUBSTR(..., 7, 8)——15位的出生日期在第7–12位（6位），直接截8位会越界或拼错。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

安全做法是先判断长度：CASE WHEN LENGTH(id_card) = 18 THEN STR_TO_DATE(SUBSTR(id_card, 7, 8), '%Y%m%d') WHEN LENGTH(id_card) = 15 THEN STR_TO_DATE(CONCAT('19', SUBSTR(id_card, 7, 6)), '%Y%m%d') ELSE NULL END。注意：15位默认是19XX年，极少有1900年前的，但业务上需确认是否要支持2000年后出生的特殊15位（实际不存在）。

15位身份证第7–12位是YYMMDD，所以补'19'构成'19YYMMDD'
不建议用LPAD或REPLACE强行统一长度，易引入脏数据
线上表若有15位存量数据，建议加个生成列（generated column）缓存标准化出生日期，避免每次查询都计算

WHERE条件中提取出生日期的性能提醒

在WHERE里写STR_TO_DATE(SUBSTR(id_card, 7, 8), '%Y%m%d') > '1990-01-01'会导致全表扫描——因为函数作用于字段，无法走id_card索引。

真正要查“出生日期在某区间”的场景，应反向构造身份证前缀范围。例如查1990–1999年出生的人，可写：id_card BETWEEN '1101011990%' AND '1101011999%'（假设前6位地区码固定），或更通用的：SUBSTR(id_card, 7, 4) BETWEEN '1990' AND '1999'——后者能命中函数索引（MySQL 8.0+），但5.7不支持，得建表达式索引或冗余字段。