mysql正则表达式验证身份证，并获取年龄、生日、性别
发布时间：-05-17 16:28,
浏览次数：4844
, 标签：
mysql正则表达式，mysql验证身份证，REGEXP
mysql是支持正则表达式的(REGEXP)。
创建身份信息表：
DROP TABLE IF EXISTS `card`;
CREATE TABLE `card` (
`id` int(14) NOT NULL AUTO_INCREMENT,
`card_id` varchar(18) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
插入一条数据：
insert into `card`(`id`,`card_id`,`name`) values (1,'52040219770325X064','李四');
下面mysql语句即可查询出card为身份证号的记录
(18位纯数字，17位纯数字+X,15位纯数字)
select * from table where card REGEXP '[0-9]{18}|[0-9]{17}X|[0-9]{15}'
更精确的正则表达式写法是：
select * from table where cardregexp '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)$'
获得年龄、出生日期、性别：
SELECT card_id,CAST(SUBSTRING(card_id,7,8) AS DATETIME) AS bir,FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SUBSTRING(card_id,7,8) AS DATETIME)),'%m-%d') AS TIME,IF(LEFT(SUBSTRING(card_id,17),1)%2=1,"男","女") AS sex FROM card
