SQL怎样从身份证号中提取出生日期_利用SUBSTRING与CAST转换

身份证号第7至14位表示出生日期，格式为YYYYMMDD，需确保字段为字符串类型后截取并显式转换为DATE类型，否则易因类型错误、脏数据或索引失效导致查询失败。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

身份证号第7到第14位就是出生日期，但得先确认格式

都知道中国大陆18位身份证号的第7到第14位是YYYYMMDD格式，比如19950823。但这里有个关键前提：你得确保数据库里存的这个字段是字符串类型。如果它被存成了数值型（比如BIGINT），那么后续的SUBSTRING操作要么直接报错，要么截取出莫名其妙的结果。常见的坑包括：SUBSTRING返回空值、截取位置发生偏移，或者后续的CAST转换直接提示“invalid input syntax”。

动手之前，建议先做这几步检查：

• 用SELECT LENGTH(id_card) FROM table_name LIMIT 5快速验证一下，看看是不是全是18位。如果有15位的老号码，需要单独处理（本文聚焦18位新号）。
• 如果发现id_card字段是数值类型，必须先把它转成字符串。不同数据库语法略有不同：CAST(id_card AS TEXT) 或者 id_card::TEXT（PostgreSQL），在SQL Server里则是CONVERT(VARCHAR, id_card)。
• MySQL里可以直接用CAST(id_card AS CHAR)。更稳妥一点的写法是加上LPAD(id_card, 18, '0')来防止前置零丢失——不过话说回来，18位身份证的首位不可能是0，这一步通常可以省略。

SUBSTRING提取后必须显式CAST为DATE类型

用SUBSTRING(id_card, 7, 8)拿到的，本质上还是一个字符串'19950823'。它可不是日期类型，没法直接参与日期计算、排序，或者跟其他DATE字段进行比较。如果直接写WHERE birth_date > '1990-01-01'，数据库会尝试隐式转换，而不同数据库的“脾气”可不一样：MySQL可能容忍，PostgreSQL多半会直接报错，SQL Server则可能按字符串的字典序去比较，结果可想而知。

所以，正确的姿势是显式转换。具体怎么转，还得看数据库：

• PostgreSQL：CAST(SUBSTRING(id_card, 7, 8) AS DATE) 或者简写 SUBSTRING(id_card, 7, 8)::DATE。
• SQL Server：CONVERT(DATE, SUBSTRING(CAST(id_card AS VARCHAR(18)), 7, 8))。这里顺序很重要，得先CAST成字符串再SUBSTRING，因为SUBSTRING函数对纯数字类型可能不买账。
• MySQL：这里有点特殊，得用STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d')。直接用CAST(... AS DATE)是识别不了'19950823'这种格式的。

遇到NULL或非法值时，SUBSTRING+CAST会中断查询

这才是最让人头疼的地方。只要表里有一条记录的id_card为空、长度不够18位，或者第7到14位里混进了字母之类的非数字字符，整个查询就可能直接报错退出。尤其是在做聚合分析或者创建索引的时候，这种“一颗老鼠屎坏了一锅粥”的情况绝非危言耸听。

怎么办？得加防护判断。比如在PostgreSQL里，可以先用正则清除非数字字符：NULLIF(REGEXP_REPLACE(SUBSTRING(id_card, 7, 8), '\D', '', 'g'), '')，然后再转换。但更治本的建议是，在生产环境里，强烈建议在数据ETL阶段就做好校验和清洗，把异常身份证号单独拎出来处理，而不是等到查询的时候才手忙脚乱地兜底。

如果非要在查询时处理，MySQL里可以写一个相对复杂的判断：IF(LENGTH(id_card)=18 AND id_card REGEXP '^[0-9]{17}[0-9Xx]$', STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d'), NULL)。当然，这会影响性能。

性能影响：SUBSTRING+CAST无法利用索引

另一个容易被忽视的问题是性能。即便你在id_card字段上建了B-Tree索引，像SUBSTRING(id_card, 7, 8)这样的表达式计算是没法走索引的。如果你想查询1990年到1999年出生的人，数据库只能老老实实地做全表扫描，把每条记录都拿出来截取、转换一遍再比较。

对于高频的按出生日期查询的场景，有更优的解决方案：

• 增加冗余字段：最实在的办法，就是在表里直接新增一个birth_date DATE列。通过触发器或者在应用层写入数据时，就同步解析好并存储进去。一劳永逸。
• 使用函数索引：像PostgreSQL就支持创建函数索引，例如CREATE INDEX idx_birth ON users ((SUBSTRING(id_card, 7, 8)::DATE))。但要注意，这个索引只对查询条件能完全匹配这个函数表达式的情况有效。
• 避免错误写法：别在WHERE子句里写CAST(SUBSTRING(id_card, 7, 4) AS INT) BETWEEN 1990 AND 1999。这相当于只截了年份部分来比较，不仅比用完整8位更慢，而且让优化器更加无从下手。

说到底，技术语法本身并不复杂，真正的挑战往往来自于数据本身的不规范。哪怕表中成千上万条记录里，只有那么一两条是15位旧号或者带了空格，就足以让整个批量解析的脚本崩溃。所以，在动手之前，不妨先运行一下这个查询：SELECT id_card FROM t WHERE LENGTH(TRIM(id_card)) NOT IN (15, 18)。看看你的数据到底干不干净，别指望数据库函数能替你搞定所有脏数据。