【Mysql学习笔记】正则表达式进行搜索
本节学习在Mysql的WHERE子句中使用正则表达式进行数据匹配过滤
一.什么是正则表达式?
-
正则表达式是用来匹配文本的特殊的串。比如像从文本中提取电话号码,进行邮箱匹配是否满足条件都可以使用正则表达式。
-
所有种类的程序设计语言、文本编辑器、操作系统都支持正则表达式的功能
二.Mysql中使用正则表达式
Mysql中在SELECT的时候允许WHERE子句运行正则表达式进行数据过滤
1.基本字符匹配
mysql> SELECT id,land_id,content FROM comment WHERE content REGEXP '好好好'; +----+---------+---------+ | id | land_id | content | +----+---------+---------+ | 11 | 21 | 好好好 | | 12 | 21 | 好好好 | | 13 | 21 | 好好好 | +----+---------+---------+ 3 rows in set (0.02 sec)
匹配所有comment中content包含"好好好"
mysql> SELECT id,land_id,content FROM comment WHERE content REGEXP '.1'; +----+---------+----------------------------+ | id | land_id | content | +----+---------+----------------------------+ | 4 | 21 | 11111111 | | 5 | 21 | 1111122222211 | | 6 | 21 | 1111133333322222211 | | 7 | 21 | 11111333444444433322222211 | +----+---------+----------------------------+ 4 rows in set (0.00 sec)
匹配所有content以1结尾的comment
这边两个例子都可以用LIKE模糊匹配筛选出来.
LIKE与REGEXP的中间重要的一个区别:如果LIKE和REGEXP都不存在通配符的时候,LIKE是相等的关系,REGEXP是包含的一个关系。LIKE是表中查询某一列某一字段相等的记录,REGEXP是表中查询某一列某一字段包含表达式的记录
Mysql中的正则表达式是不区分大小写的,当然Mysql提供了BINARY关键字进行大小写的区分,举个例子:WHERE prod_name REGEXP BINARY 'JetPack' .000
2.进行OR匹配
mysql> SELECT id,land_id FROM comment WHERE land_id REGEXP '19|20'; +----+---------+ | id | land_id | +----+---------+ | 2 | 20 | | 3 | 19 | +----+---------+ 2 rows in set (0.00 sec)
可以看到在|为正则表达式中的OR,当然可以多个OR连起来使用 '19|20|21'
3.匹配几个字符之一
# users表长这样 mysql> SELECT id,username FROM users; +----+----------+ | id | username | +----+----------+ | 8 | 1-Titans | | 6 | 2-Titans | | 7 | 3-Titan | | 9 | b-Titans | | 5 | mysql... | +----+----------+ # 查询username包含1-Titan或者2-Titan或者3-Titan的记录 mysql> SELECT id,username FROM users WHERE username REGEXP '[123]-Titan'; +----+----------+ | id | username | +----+----------+ | 8 | 1-Titans | | 6 | 2-Titans | | 7 | 3-Titan | +----+----------+ 3 rows in set (0.01 sec)
这边的正则表达式使用到了'[123]',[123]定义了一组字符'1','2','3'它的意思是匹配1或2或3,因此就会像上面返回结果.同时[123]-Titan是[1|2|3]-Titan的缩写
字符集合也可以使用否定运算符,正则表达式使用^表否定,[^123]表示匹配除了这些字符外的任意东西
4.匹配范围
之前[123]定义了匹配字符集,但是比如我们的字符集合为0-9,a-z那么[0123456789abcdefghijklmnopqrstuvwxyz]这样写是不是显得非常非常麻烦,为了简化这样情况,正则表达式提供了'-'来表示范围。上面等价于[0-9a-z]
mysql> SELECT id,username FROM users WHERE username REGEXP '[1-9a-z]-Titan'; +----+----------+ | id | username | +----+----------+ | 8 | 1-Titans | | 6 | 2-Titans | | 7 | 3-Titan | | 5 | b-Titans | +----+----------+ 4 rows in set (0.00 sec)
5.匹配特殊字符
正则表达式存在一些具有特殊意义的字符,比如'-' '|' '.'还有一些字符,那么我们应该如何匹配这些字符?所以我们需要转义符.
mysql> SELECT id,username FROM users WHERE username REGEXP '.'; +----+----------+ | id | username | +----+----------+ | 8 | 1-Titans | | 6 | 2-Titans | | 7 | 3-Titan | | 5 | b-Titans | | 9 | mysql... | +----+----------+ 5 rows in set (0.00 sec)
上面这个例子我们是想匹配出mysql...这个记录,但是由于正则表达式中'.'具有特殊意义:匹配任意字符,所以它会将表中所有的数据都匹配出来,这时候我们就需要进行转义
mysql> SELECT id,username FROM users WHERE username REGEXP '\\.'; +----+----------+ | id | username | +----+----------+ | 9 | mysql... | +----+----------+ 1 row in set (0.04 sec)
在正则表达式中使用\\+需要匹配的字符来进行匹配特殊字符,比如上面\\.就是去查找'.'包含这个字符的记录
元字符 | 说明 |
---|---|
\\f | 换页 |
\\n | 换行 |
\\r | 回车 |
\\t | 制表 |
\\v | 纵向值表 |
\\\用来匹配''反斜杠自身
6.匹配字符类
正则表达式为我们封装了常用的字符类
类 | 说明 |
---|---|
[:alnum:] | [a-zA-Z0-9] |
[:alpha:] | [a-zA-Z] |
[:blank:] | 空格和制表同[\\t] |
[:cntrl:] | ASCII控制字符 |
[:digit:] | [0-9] |
[:graph:] | 同[:print:],但不包括空格 |
[:lower:] | [a-z] |
[:print:] | 任意可打印字符 |
[:punct:] | 不在[:alnum:]和[:cntrl:]中的任意字符 |
[:space:] | [\\f\\n\\r\\t\\v] |
[:upper:] | [A-Z] |
[:xdigit:] | 任意十六进制数字[a-fA-F0-9] |
7.匹配多个实例
为实现匹配多个实例可以使用重复元字符
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配<=>{1,} |
? | 0个或1个匹配<=>{0,1} |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围(m<256) |
# users长这样 mysql> SELECT id,username FROM users; +----+-----------+ | id | username | +----+-----------+ | 8 | 1(Titans) | | 6 | 2(Titans) | | 7 | 3(Titan) | | 5 | b(Titans) | | 9 | mysql... | +----+-----------+ 5 rows in set (0.00 sec) mysql> SELECT id,username FROM users WHERE username REGEXP '[0-9]\\(Titans?\\)'; +----+-----------+ | id | username | +----+-----------+ | 8 | 1(Titans) | | 6 | 2(Titans) | | 7 | 3(Titan) | +----+-----------+ 3 rows in set (0.00 sec)
解释下'[0-9]\\(Titans?\\)' 首先[0-9]匹配一个任意数字,然后\\(转义匹配'(' ,\\)转义匹配')' Titans?匹配Titans和Titan,这边?表示s匹配0次或者1次即s可出现可不出现
mysql> SELECT id,username FROM users WHERE username REGEXP '[[:digit:]]{4}'; +----+--------------+ | id | username | +----+--------------+ | 5 | JetPack 1000 | | 6 | JetPack 2000 | | 7 | JetPack 3000 | +----+--------------+ 3 rows in set (0.00 sec) mysql> SELECT id,username FROM users WHERE username REGEXP '[0-9][0-9][0-9][0-9]'; +----+--------------+ | id | username | +----+--------------+ | 5 | JetPack 1000 | | 6 | JetPack 2000 | | 7 | JetPack 3000 | +----+--------------+ 3 rows in set (0.00 sec)
上面两个方式等价 都是匹配连载一起的任意4位数字
8.定位符
为了匹配特定位置的文本,需要使用到定位符
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
mysql> SELECT id,username FROM users WHERE username REGEXP '^[0-9\\.]'; +----+-----------+ | id | username | +----+-----------+ | 5 | .5 ton | | 6 | 1 ton | | 8 | 1(Titans) | | 7 | 2 ton | +----+-----------+ 4 rows in set (0.00 sec)
'^[0-9\.]'表示在词的开头匹配一个任意数字或者'.'
^在[]内表否定,否则表示字段的开始处
小结
-
本文介绍了如何在Mysql中使用正则表达式配合WHERE子句进行数据的匹配