文章目录
前文连接:
《MySQL必知必会》——书中表的生成
《MySQL必知必会》——第三章(了解数据库和表) 关键词:USE、SHOW
《MySQL必知必会》——第四章(检索数据) 关键词:SELECT
《MySQL必知必会》——第五章(排序检索数据) 关键词:ORDER BY
《MySQL必知必会》——第六章(过滤数据) 关键词:WHERE
《MySQL必知必会》——第七章(数据过滤) 关键词:WHERE、AND、OR、IN
《MySQL必知必会》——第八章(用通配符过滤)关键词:LIKE % _
第6、7、8章讲述了基本的数据过滤操作,但随着过滤数据复杂性的提升,WHERE子句的复杂性也会越来越大!这时我们就可以使用正则表达式就处理相关的数据!
9.1 正则表达式
正则表达式:
- 是用来匹配文本的特殊字符串。如:本文中电话号码、网址、人名、地点等等的匹配,都可以去使用正则表达式去做
9.2 MySQL中正则表达式的使用
正则表达式可以去匹配文本中的某些片段(网址、电话等等),而MySQL中的 WHERE子句 对正则表达式有一定的支持,我们可以使用具体的正则表达式去过滤 SELECT 中的数据。
9.2.1 基本字符匹配
我们在 products表 中举例:
先看看表中的基本信息:
USE test;
SELECT * FROM products;
- 若我们找 prod_name列 中包含 1000 的行:
# like: % 表示匹配任意字符
SELECT prod_name FROM products where prod_name like '%1000%';
# 正则表达式 RegExp: '1000'表示匹配文本中含有'1000'的数据
SELECT prod_name FROM products where prod_name RegExp '1000';
上述两种的结果是一致的
- 若我们找 prod_name列 中包含 000 的行:
# like: % 表示匹配任意字符
SELECT prod_name FROM products where prod_name like '%000';
# 正则表达式: . 表示匹配任意字符
SELECT prod_name FROM products where prod_name RegExp '.000';
上述两种的结果是一致的
注意:
- MySQL正则表达式匹配是 不区分大小写 的,若要区分大小写可以使用 BINARY关键词
- 如: WHERE prod_name RegExp BINARY ‘要匹配的文本’
9.2.2 进行OR匹配
正则表达式中OR操作符为: | ,我们举例看看:
# 匹配文本中包含 1000 或者 2000 的数据
SELECT prod_name FROM products where prod_name RegExp '1000|2000';
拓展:
- 若是两个以上的OR操作:可以用:1000|2000|3000 等等
9.2.3 匹配几个字符之一
匹配 任意单一字符 : [] , 我们举例看看:
# 匹配文本中包含 1 或 2 或 3 的数据
SELECT prod_name FROM products where prod_name RegExp '[123]';
# 我们也可以使用OR来表示
SELECT prod_name FROM products where prod_name RegExp '1|2|3';
上述两种表达的结果是一致的:
拓展:
- 我们可以使用: ^ ,匹配除了指定字符之外的任意字符
- where 列名 regexp ‘[^2]’ :表示除了2之外的任意字符
9.2.4 匹配范围
使用集合来匹配一和或多个字符,如使用 [0123456789] 来匹配0到9任意的数字,为了简化上述的表达,我们可以使用 - ,如:[0-9]
# 匹配文本中包含 1 到 5的任意数据
SELECT prod_name FROM products where prod_name RegExp '[1-5]';
当然,我们可以表示用: [a-z] 表示任意字符的范围
9.2.5 匹配特殊字符
上面我们学习了很多的特殊字符,如:. [ ] | - 等,要是我们需要匹配这些特殊字符应该怎么办呢?
我们需要在特殊字符前面加上 \ \ ,进行转义,我们举例看看:
# . 是匹配任意字符,vend_name 所有的行都将返回
SELECT vend_name FROM vendors WHERE vend_name RegExp '.';
# 我们加上转义符 \\,这样就可以匹配 '.'
SELECT vend_name FROM vendors WHERE vend_name RegExp '\\.';
注意:
- 正则表达式一般用 \ 来进行转义, 而MySQL使用 \ 来进行转义
9.2.6 匹配字符类
下面列一些经常会用到的匹配模式:
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同 [a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同 [\t]) |
[:cntrl:] | ASCII控制字符 (ASCII 0 到 31和127) |
[:digit:] | 任意数字(同 [0-9]) |
[:print:] | 任意可打印字符 |
[:graph:] | 与[:print:]相同,但是不包括空格 |
[:lower:] | 任意小写字母(同 [a-z]) |
[:upper:] | 任意大写字母(同 [A-Z]) |
[:punct:] | 即不在 [:alnum:] 又不在 [:cntrl:] 中的任意字符 |
[:space:] | 包括空格在内的任意空白字符(同 [\ \f \ \n \ \r \ \t \ \v]) |
[:xdigit:] | 任意十六进制数字(同 [a-zA-Z0-9]) |
9.2.7 匹配多个实例
有时我们不仅仅匹配单一实例,我们需要匹配多个实例,这时我们有以下操作:
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配(等同于 {1,}) |
? | 0个或1个匹配(等同于 {0,1}) |
{n} | 指定数目n的匹配 |
{n, } | 不少于指定数目n的匹配 |
{n, m} | 匹配数目的范围(m不超过255) |
我们举例看看上面的操作如何使用:
SELECT prod_name FROM products where prod_name RegExp '[0-9] sticks?';
解释:’[0-9] sticks?’ :
- [0-9]:匹配0-9之间的任意数字
- sticks?:其中的 '?'是指其前面的字符’s’有0个或1个,这样就是匹配 stick 或者 sticks
SELECT prod_name FROM products where prod_name RegExp '[:digit:]{4}';
解释:’[:digit:]{4}’
- [:digit:]:匹配 0-9 的任意数字
- {4}:要求其前面的字符出现4次
当然上面的操作,我们也可以使用下面这种写法:
SELECT prod_name FROM products where prod_name RegExp '[0-9][0-9][0-9][0-9]';
9.2.8 定位符
上面提到的都是匹配一个串中任意位置的文本,为了匹配特定位置的文本,我们使用下表中的定位符:
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的末尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
我们举例看看:
# 找出以数字开头的数据
SELECT prod_name FROM products where prod_name RegExp '^[0-9].';
解释: ’ ^ [0-9]. ’
- ^:表示文本的开始
- [0-9]:表示匹配数字
- . :表示匹配任意字符
注意:
- ^:在集合[]中,表示否定集合中的内容;在集合外,表示文本的开头
- RegExp匹配文本中的子串,LIKE匹配整个文本,我们在使用RegExp时,通过指定开头^和文本的末尾$,就可以使得RegExp和LIKE用法一致
9.2.9 测试正则表达式
我们想要测试正则表达式是否正确时,可以不用数据库,直接使用SELECT语句,若返回0说明没有匹配上,返回1说明匹配上!
SELECT 'HELLO' RegExp '[a-z]';
SELECT 'HELLO' RegExp '[0-9]';
总结:
- SELECT 列名 FROM 表名 WHERE 列名 RegExp 正则表达式:匹配特定的文本