MySQL必知必会九：用正则表达式进行搜索

1. 基本字符匹配

我们从一个非常简单的例子开始。下面的语句检索列prod_name包含文本1000的所有行：

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

• 分析：除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL： REGEXP后所跟的东西作为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

为什么要费力地使用正则表达式？在刚才的例子中，正则表达式确实没有带来太多好处（可能还会降低性能），不过，请考虑下面的例子：

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

• 分析：这里使用了正则表达式.000。 .是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此， 1000和2000都匹配且返回。
• LIKE与REGEXP的区别：LIKE匹配整个列。如果被匹配的文本在列值中出现， LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现， REGEXP将会找到它，相应的行将被返回。这是一个非常重要的差别。
• 匹配不区分大小写 ：MySQL中的正则表达式匹配不区分大小写。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

2. 进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用|，如下所示：

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

• 分析：语句中使用了正则表达式1000|2000。 |为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。
• 使用|从功能上类似于在SELECT语句中使用OR语句， 多个OR条件可并入单个正则表达式。两个以上的OR条件可以给出两个以上的OR条件。例如，'1000 | 2000 | 3000'将匹配1000或2000或3000。

3. 匹配几个字符之一

匹配任何单一字符。但是，如果你只想匹配特定的字符，怎么办？可通过指定一组用[和]括起来的字符来完成，如下所示：

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

• 分析：这里，使用了正则表达式[123] Ton。 [123]定义一组字符，它的意思是匹配1或2或3，因此， 1 ton和2 ton都匹配且返回（没有3 ton）。

正如所见， []是另一种形式的OR语句。 事实上，正则表达式[123]Ton为[1|2|3]Ton的缩写，也可以使用后者。但是，需要用[]来定义OR语句查找什么。为更好地理解这一点，请看下面的例子：

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name;
+---------------+
| prod_name     |
+---------------+
| 1 ton anvil   |
| 2 ton anvil   |
| JetPack 1000  |
| JetPack 2000  |
| TNT (1 stick) |
+---------------+

• 分析：MySQL假定你的意思是'1'或'2'或'3 ton'。除非把字符|括在一个集合中，否则它将应用于整个字符串。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、 2或3，但[^123]却匹配除这些字符外的任何东西。

4. 匹配范围

范围不限于完整的集合， [1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的， [a-z]匹配任意字母字符。

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

• 分析：这里使用正则表达式[1-5] Ton。 [1-5]定义了一个范围，这个表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配，所以返回.5 ton。

5.匹配特殊字符

为了匹配特殊字符，必须用\\为前导。 \\-表示查找-， \\.表示查找.。

mysql> SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+

\也用来引用元字符（具有特殊含义的字符）。

元 字 符	说 明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配\：为了匹配反斜杠\字符本身，需要使用\\\。
\或\：多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（ MySQL自己解释一个，正则表达式库解释另一个）。

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. 匹配多个实例

元 字 符	说 明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（ m不超过255）

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

• 分析：如前所述， [:digit:]匹配任意数字，因而它为数字的一个集合。 {4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

8. 定位符

元 字 符	说 明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

• 分析：^匹配串的开始。因此， ^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。

^的双重用途：^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。
使REGEXP起类似LIKE的作用： LIKE和REGEXP的不同在于， LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。
简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0（没有匹配）或1（匹配）。可以用带文字串的REGEXP来测试表达式，并试验它们。相应的语法如下：SELECT 'hello' REGEXP '[0-9]';这个例子显然将返回0（因为文本hello中没有数字）。