用MySQL正则表达式进行搜索

《MySQL必知必会》

正则表达式是用来匹配文本的特殊的串(字符集合)。
所用到的三个表:customers、products、vendors

1. 基本字符匹配: 关键字REGEXP后所跟的为正则表达式

#检索列prod_name包含文本'1000'的所有行
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)

#用一个特殊字符'.'表示匹配任意一个字符
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '.000'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

关键字LIKE和REGEXP的区别：
LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它；REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回。

mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name LIKE '1000'
    -> ORDER BY prod_name;
Empty set (0.00 sec)#SQL查询没有返回任何结果

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 |
+--------------+
2 rows in set (0.00 sec)
#也可给出两个以上的OR条件
'1000|2000|3000'

3. 匹配几个字符之一: 使用一组括号[ ]，[ ]是另一种形式的OR语句，但也有所区别。

#[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回。
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[123] ton'#等价于'[1|2|3] ton'
    -> ORDER BY prod_name;
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

#若去掉[], MySQL会认为是'1'或'2'或'3 ton'。
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)

4. 匹配范围: 集合可用来定义要匹配的一个或多个字符，在集合的开始出放置一个^可以否定一个字符集。可以用-来定义一个范围，如[0-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  |
+--------------+
3 rows in set (0.00 sec)

5. 匹配特殊字符: 使用\ \为前导，如\ \ .(或-、|、[ ])
   \ \ 也用来引用元字符：

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

#不使用前导\\
mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '.'
    -> ORDER BY vend_name;
+----------------+
| vend_name      |
+----------------+
| ACME           |
| Anvils R Us    |
| Furball Inc.   |
| Jet Set        |
| Jouets Et Ours |
| LT Supplies    |
+----------------+
6 rows in set (0.00 sec)

#使用前导\\
mysql> SELECT vend_name
    -> FROM vendors
    -> WHERE vend_name REGEXP '\\.'
    -> ORDER BY vend_name;
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

6.匹配字符类

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\ \t]）
[:cntrl:]	ASCLL控制字符（ASCLL0到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）

#\\(匹配(, [0-9]匹配任意数字, sticks?匹配stick和sticks, \\)匹配)
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
    -> ORDER BY prod_name;
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

#{4}确切要求它前面的字符出现4次
#等价于'[0-9][0-9][0-9][0-9]'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '[[:digit:]]{4}'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

8. 定位符: 以上所有例子都是匹配一个串任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

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

#找出以一个数字或小数点开始的所有产品, 也可用'[0-9]\\.]'
mysql> SELECT prod_name
    -> FROM products
    -> WHERE prod_name REGEXP '^[[:digit:]\\.]'
    -> ORDER BY prod_name;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

通过用^开始每个表达式，用$结束每个表达式，可使REGEXP的作用与LIKE一样。