用正则表达式进行搜索（REGEXP）

2019-08-04

1.基本字符匹配

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.15 sec)

除关键字LIKE被REGEXP替代外，这条语句看上去非常像使用LIKE的语句。它告诉MySQL：REGEXP后所跟的东西作为正则表达式处理。

LIKE与REGEXP

在LIKE和REGEXP之间有一个重要的差别。看一下语句：

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name LIKE '1000'
    -> ORDER BY prod_name;
Empty set (0.03 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将会找到它，相应的行将被返回。这是一个非常重要的差别。
那么，REGEXP能不能用来匹配整个列值（从而起与LIKE相同的作用）？答案是肯定的，使用^和$定位符（anchor）即可。

匹配不区分大小写

MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

进行OR匹配

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '1000|2000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[123] Ton'
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.06 sec)

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) |
+---------------+
5 rows in set (0.00 sec)

这并不是期望的输出。两个要求的行被检索出来，但还检索出来了另外3行。之所以这样是由于MySQL假定你的意思是’1’或‘2’或‘3 ton’。除非你把字符 | 括在一个集合中，否则它将应用于整个串。
字符集合也可以被否定，如：[123]匹配字符1、2或3，但[^123]却匹配除这些字符外的任何东西。

匹配特殊字符

mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '\\.'
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.05 sec)

• 为了匹配特殊字符，必须用\\为前导。如，\\-表示查找-，\\.表示查找.，为了匹配反斜杠（\）字符本身，需要使用\\\。
• 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释一个）。
  ###匹配字符类
  |类|说 明|
  |:—|:—|
  |[:alnum:]|任意字母和数字（同[a-zA-Z0-9]）|
  |[:alpha:]|任意字符（同[a-zA-Z]）|
  |[:black:]|空格和制表（同[\\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]）|

匹配多个实例

重复元字符

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

定位符

定位元字符

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