MySQL总结4-正则表达式

随着过滤难度的增加，通过WHERE就很难完成任务了，我们需要正则表达式来进行匹配。正则表达式是用来匹配文本的特殊的字符集合。

1. 基本字符匹配

检索列prod_name包含文本1000的所有行

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '1000';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|      35.00 | JetPack 1000 |    1005 |
+------------+--------------+---------+
1 row in set (0.30 sec)

这条语句非常像LIKE，不同之处在于REGEXP告诉MySQL：后面的东西作为正则表达式理解。

下面这个例子说明了正则表达式优于LIKE的地方

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '.000';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|      35.00 | JetPack 1000 |    1005 |
|      55.00 | JetPack 2000 |    1005 |
+------------+--------------+---------+
2 rows in set (0.22 sec)

这里使用了正则表达式.000。点号是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000和2000都匹配
且返回。

2. OR匹配

如果要搜索两个串之一，使用 | 进行逻辑安排。

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '1000|2000';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|      35.00 | JetPack 1000 |    1005 |
|      55.00 | JetPack 2000 |    1005 |
+------------+--------------+---------+
2 rows in set (0.23 sec)

3. 匹配其中几个字符

如果想要匹配某几个特定的字符，则使用[ ]括起来

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[123] Ton';
+------------+-------------+---------+
| prod_price | prod_name   | vend_id |
+------------+-------------+---------+
|       9.99 | 1 ton anvil |    1001 |
|      14.99 | 2 ton anvil |    1001 |
+------------+-------------+---------+
2 rows in set (0.23 sec)

注意：正则默认是不区分大小写的。如果要区分，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'Jet'

[ ]可以理解为一种简写的OR语言，[123]等价于1|2|3，但要注意

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '1|2|3 Ton';
+------------+---------------+---------+
| prod_price | prod_name     | vend_id |
+------------+---------------+---------+
|       9.99 | 1 ton anvil   |    1001 |
|      14.99 | 2 ton anvil   |    1001 |
|      35.00 | JetPack 1000  |    1005 |
|      55.00 | JetPack 2000  |    1005 |
|       2.50 | TNT (1 stick) |    1003 |
+------------+---------------+---------+
5 rows in set (0.22 sec)

上面没有输出预期结果的原因是没有用括号括起来。

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[1|2|3] Ton';
+------------+-------------+---------+
| prod_price | prod_name   | vend_id |
+------------+-------------+---------+
|       9.99 | 1 ton anvil |    1001 |
|      14.99 | 2 ton anvil |    1001 |
+------------+-------------+---------+
2 rows in set (0.23 sec)

4. 范围匹配

集合可用来定义要匹配的一个或多个字符，例如匹配数字0到9。可以从[0123456789]简化为[0-9]

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[1-5] Ton';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|       5.99 | .5 ton anvil |    1001 |
|       9.99 | 1 ton anvil  |    1001 |
|      14.99 | 2 ton anvil  |    1001 |
+------------+--------------+---------+
3 rows in set (0.23 sec)

5. 特殊字符匹配

有些字符有特殊含义，比如.代表匹配一个任意字符，如果想要匹配真正的点号，就需要转义。

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '\\.';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|       5.99 | .5 ton anvil |    1001 |
+------------+--------------+---------+
1 row in set (0.22 sec)

要注意斜杠是反斜杠，在Enter上面。

6. 匹配预定义字符集

可以使用预定义的字符集，也称为字符类（character class）。以下例子匹配了所有带有空格的数据，省略了一部分节省空间。

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[:blank:]';
+------------+----------------+---------+
| prod_price | prod_name      | vend_id |
+------------+----------------+---------+
|       5.99 | .5 ton anvil   |    1001 |
|       9.99 | 1 ton anvil    |    1001 |
|       .... | .......        |    .... |
|       2.50 | TNT (1 stick)  |    1003 |
|      10.00 | TNT (5 sticks) |    1003 |
+------------+----------------+---------+
13 rows in set (0.23 sec)

7. 匹配多个实例

有时需要对匹配的数目进行更强的控制，所以需要重复元字符。

第一个实例展示了?的用法。sticks?匹配stick 和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现）

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[0-9] sticks?';
+------------+----------------+---------+
| prod_price | prod_name      | vend_id |
+------------+----------------+---------+
|       2.50 | TNT (1 stick)  |    1003 |
|      10.00 | TNT (5 sticks) |    1003 |
+------------+----------------+---------+
2 rows in set (0.23 sec)

第二个实例展示了{}的用法。[:digit:]匹配任意数字{4}确切地要求它前面的字符（任意数字）出现4次。注意：

一定要用[ ]括起来，否则会出现语义错误。
{ }不能填0

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '[[:digit:]]{4}';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|      35.00 | JetPack 1000 |    1005 |
|      55.00 | JetPack 2000 |    1005 |
+------------+--------------+---------+
2 rows in set (0.23 sec)

8. 定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

假设要找到一个以.开头的产品，则

mysql> SELECT prod_price,prod_name,vend_id FROM products
    -> WHERE prod_name REGEXP '^[\\.]';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|       5.99 | .5 ton anvil |    1001 |
+------------+--------------+---------+
1 row in set (0.23 sec)

此外^还有否定的用法，在集合中[ ]使用^，表示否定该集合。