MySQL处理特殊数据（身份证号，计算年龄，判断性别，处理科学计数法，使用正则表达式）

最新推荐文章于 2024-10-28 18:45:19 发布

叶炎灵

最新推荐文章于 2024-10-28 18:45:19 发布

阅读量1.1k

点赞数

分类专栏： sql 正则表达式文章标签： mysql 正则表达式

本文链接：https://blog.youkuaiyun.com/weixin_43375650/article/details/116092582

版权

sql 同时被 2 个专栏收录

2 篇文章

订阅专栏

正则表达式

1 篇文章

订阅专栏

本文介绍了MySQL中正则表达式的使用，包括开始和结束字符、特殊字符、数量限定等，并提供了多个查询示例。同时，讲解了如何利用正则表达式校验日期格式，如yyyy-MM-dd、yyyy-MM-ddHH:mm:ss等，以及通过身份证号获取性别、年龄和省份的方法。此外，还提及了MySQL自带的LIKE关键词和处理科学计数法的转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL-使用正则表达式

正则表达式功能确实很强大，那天专门抽空学学，这里就暂时在mysql查询中用用。

正则表达式强大而灵活，可以应用于非常复杂的查询。

选项	说明（自动加匹配二字）	例子	匹配值示例
^	文本开始字符	'^b’匹配以字母b开头的字符串	book, big, banana, bike
$	文本结束字符	'st$'匹配以st结尾的字符串	test, resist, persist
.	任何单个字符	'b.t’匹配任何b和t之间有一个字符	bit, bat, but, bite
*	0个或多个在它前面的字符	'f*n’匹配字符n前面有任意n个字符	f fn, fan, faan, abcn
+	前面的字符一次或多次	'ba+'匹配以b开头后面紧跟至少一个a	ba, bay, bare, battle
<字符串>	包含指定字符串的文本	‘fa’	fan, afa, faad
[字符集合]	字符集合中的任一个字符	'[xz]'匹配x或者z	dizzy, zebra, x-ray, extra
[^]	不在括号中的任何字符	'[^abc]'匹配任何不包含a、b或c的字符串	desk, fox, f8ke
字符串{n}	前面的字符串至少n次	b{2}匹配2个或更多的b	bbb, bbbb, bbbbbb
字符串{n,m}	前面的字符串至少n次，至多m次	b{2,4}匹配最少2个，最多4个b	bb, bbb, bbbb

where 字段名 REGEXP 模式;

基本形式

属性名 regexp ‘匹配方式’

正则表达式的模式字符

^ 匹配字符开始的部分

eg1: 从info表name字段中查询以L开头的记录

select * from info where name regexp '^L';

eg2: 从info表name字段中查询以aaa开头的记录

select * from info where name regexp '^aaa';

$ 匹配字符结束的部分

eg1: 从info表name字段中查询以c结尾的记录

select * from info where name regexp 'c$';

eg2: 从info表name字段中查询以aaa结尾的记录

select * from info where name regexp 'aaa$';

. 匹配字符串中的任意一个字符，包括回车和换行

eg1: 从info表name字段中查询以L开头y结尾中间有两个任意字符的记录

select * from info where name regexp '^L..y$';

[字符集合]匹配字符集合中的任意字符

eg1: 从info表name字段中查询包含c、e、o三个字母中任意一个的记录

select * from info where name regexp '[ceo]';

eg2: 从info表name字段中查询包含数字的记录

select * from info where name regexp '[0-9]';

eg3: 从info表name字段中查询包含数字或a、b、c三个字母中任意一个的记录

select * from info where name regexp '[0-9a-c]';

[^字符集合]匹配除了字符集合外的任意字符

eg1: 从info表name字段中查询包含a-w字母和数字以外字符的记录

select * from info where name regexp '[^a-w0-9]';

s1|s2|s3 匹配s1s2s3中的任意一个

eg1: 从info表name字段中查询包含’ic’的记录

select * from info where name regexp 'ic';

eg2: 从info表name字段中查询包含ic、uc、ab三个字符串中任意一个的记录

select * from info where name regexp 'ic|uc|ab';

*** 代表多个该字符前的字符，包括0个或1个**

eg1: 从info表name字段中查询c之前出现过a的记录

select * from info where name regexp 'a*c';

+ 代表多个该字符前的字符，包括1个

eg1: 从info表name字段中查询c之前出现过a的记录

select * from info where name regexp 'a+c';(注意比较结果！)

字符串{N} 字符串出现N次

eg1: 从info表name字段中查询出现过a3次的记录

select * from　info where name regexp 'a{3}';

字符串{M，N}字符串最少出现M次，最多出现N次

eg1: 从info表name字段中查询ab出现最少1次最多3次的记录

select * from info where name regexp 'ab{1,3}';

MYSQL中自带通配符（LIKE关键词）

%可以表示任意长度的字符（包括0）

-可以表示单个字符

MySQL-根据身份证号识别性别、年龄、所在省份

性别：

IF(mod(SUBSTR(c.reciver_idcard_num,17,1),2),'man','woman') 性别

年龄：

SELECT TIMESTAMPDIFF(YEAR, SUBSTRING('身份证号', 7, 8), NOW()) AS age;

所属省份：

CASE WHEN d.area_name IS NULL THEN 
                (CASE LEFT(c.reciver_idcard_num,2) WHEN 11 THEN '北京' WHEN 12 THEN '天津' WHEN 13 THEN '河北省' WHEN 14 THEN '山西省' WHEN 15 
                THEN '内蒙古自治区' WHEN 21 THEN '辽宁省'WHEN 22 THEN '吉林省' WHEN 23 THEN '黑龙江省' WHEN 31 THEN '上海' WHEN 32 THEN '江苏省' 
                WHEN 33 THEN '浙江省' WHEN 34 THEN '安徽省' WHEN 35 THEN '福建省' WHEN 36 THEN '江西省'WHEN 37 THEN '山东省' WHEN 41 THEN '河南省' 
                WHEN 42 THEN '湖北省' WHEN 43 THEN '湖南省' WHEN 44 THEN '广东省' WHEN 45 THEN '广西壮族自治区' WHEN 46 THEN '海南省' WHEN 50 THEN '重庆' 
                WHEN 51 THEN '四川省'WHEN 52 THEN '贵州省' WHEN 53 THEN '云南省' WHEN 54 THEN '西藏' WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃省' 
                WHEN 63 THEN '青海' WHEN 64 THEN '宁夏回族自治区' WHEN 65 THEN '新疆维吾尔自治区'end )
ELSE d.area_name END  as'省级名称'

MySQL-处理科学计数法

select convert('0.12e+006',decimal(20,0));

MySQL-用正则表达式校验时间格式的正确性

1、yyyy-MM-dd格式时间校验：

(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))-02-29)$

2、yyyy-MM-dd HH:mm:ss格式时间校验：

((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))-02-29))

\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$

3、yyyy/MM/dd格式时间校验：

(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\/(((0[13578]|1[02])\/(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)\/(0[1-9]|[12][0-9]|30))|(02\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))\/02\/29)$

4、yyyy/MM/dd HH:mm:ss格式时间校验：

((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\/(((0[13578]|1[02])\/(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)\/(0[1-9]|[12][0-9]|30))|(02\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))\/02\/29))

\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$

5、yyyyMMdd格式时间校验：

(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))0229)$

6、yyyyMMddHHmmss格式时间校验：

((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))0229))

([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])$

7、yyyyMMddHHmmssSSS格式时间校验：

((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|

((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|

((0[48]|[2468][048]|[3579][26])00))0229))

([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])([0-9]{3})$