Wu Yudong's Blog

本文将学习如何在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）

7、匹配多个实例

例如你可能需要寻找所有的数，不管数中包含多少数字，或者你可能想寻找一个单词并且还能够适应一个尾随的s（如果存在），等等。这可以用下表列出的正则表达式重复元字符来完成：

重复元字符

接下来举几个例子

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;