本文将学习如何在MySQL WHERE子句内使用正则表达式来更好地控制数据过滤。
正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持,允许你指定正则表达式,过滤SELECT检索出的数据
1、基本字符匹配
下面看一个简单的例子:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘1000’;
除关键字LIKE被REGEXP替代外,这条语句看上去非常像使用LIKE的语句,好像也没看到有啥优势。
再看下面的例子:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘.000′
-> order by prod_name;
. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符
mysql> select prod_name
-> from products
-> where prod_name like ‘1000’
-> order by prod_name;
Empty set (0.00 sec)
mysql> select prod_name
-> from products
-> where prod_name regexp ‘1000’
-> order by prod_name;
+————–+
| prod_name |
+————–+
| JetPack 1000 |
+————–+
1 row in set (0.00 sec)
LIKE匹配整个列。如果被匹配的文本在列值中出现, LIKE将不会找到它,相应的行也不被返回(除非使用通配符)。而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP将会找到它,相应的行将被返回。这是一个非常重要的差别。
2、进行OR匹配
为搜索两个串之一(或者为这个串,或者为另一个串),使用|,如下所示:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘1000|2000′
-> order by prod_name;
3、匹配几个字符之一
如果你只想匹配特定的字符,可通过指定一组用[和]括起来的字符来完成,例如:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘[123] Ton’
-> order by prod_name;
[123]定义一组字符,它的意思是匹配1或2或3,因此, 1 ton和2 ton都匹配且返回(没有3 ton)
4、 匹配范围
集合可用来定义要匹配的一个或多个字符。例如,下面的集合将匹配数字0到9,为简化这种类型的集合,可使用–来定义:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘[1-5] Ton’
-> order by prod_name;
5、 匹配特殊字符
正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、[]、|和–等,还有其他一些字符。请问,如果你需要匹配这些字符,应该怎么办呢?例如,如果要找出包含.字符的值,怎样搜索?
为了匹配特殊字符,必须用\\为前导。\\-表示查找–, \\.表示查找.。 请看下面的例子:
mysql> select vend_name
-> from vendors
-> where vend_name regexp ‘\\.’
-> order by vend_name;
6、匹配字符类
为更方便工作,可以使用预定义的字符集,称为字符类( character class)
类 | 说明 |
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表(同[\\t]) |
[:cntrl:] | ASCII控制字符( ASCII 0到31和127) |
[: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、匹配多个实例
例如你可能需要寻找所有的数,不管数中包含多少数字,或者你可能想寻找一个单词并且还能够适应一个尾随的s(如果存在),等等。这可以用下表列出的正则表达式重复元字符来完成:
重复元字符
元字符 | 说明 |
* | 0个或多个匹配 |
+ | 1个或多个匹配(等于{1,}) |
? | 0个或1个匹配(等于{0,1}) |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围( m不超过255) |
接下来举几个例子
mysql> select prod_name
-> from products
-> where prod_name regexp ‘\\([0-9] sticks?\\)’
-> order by prod_name;
[0-9]匹配任意数字(这个例子中为1和5), sticks?匹配stick和sticks( s后的?使s可选,因为?匹配它前面的任何字符的0次或1次出现), \\)匹配)。没有?,匹配stick和sticks会非常困难。
mysql> select prod_name
-> from products
-> where prod_name regexp ‘[[:digit:]]{4}’
-> order by prod_name;
[:digit:]匹配任意数字,因而它为数字的一个集合。 {4}确切地要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4}匹配连在一起的任意4位数字。
8、定位符
为了匹配特殊位置的文本,需要使用下表中列出的定位符:
元字符 | 说明 |
^ | 文本的开始 |
$ | 文本的结尾 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
如,如果你想找出以一个数(包括以小数点开始的数)开始的所有产品,怎么办?简单搜索[0-9\\.](或[[:digit:]\\.])不行,因为它将在文本内任意位置查找匹配。解决办法是使用^定位符,如下所示:
mysql> select prod_name
-> from products
-> where prod_name regexp ‘^[0-9\\.]’
-> order by prod_name;
Comments