MySQL知识（十一）——使用正则表达式查询

使用正则表达式查询

　　MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。

选项	说明	例子	匹配值示例
＾	匹配文本开始字符	‘^b’匹配以字母ｂ开头的字符串	book,big
$	匹配文本结束字符	‘st$’匹配以st结尾的字符串	test,persist
.	匹配任何单个字符	‘b.t’匹配任何b和t之间有一个字符	bit,bat,but
*	匹配零个或多个在它前面的字符	‘f*n’匹配字符n前面有任意个字符	fn,fan,faan
+	匹配前面的字符1次或多次	‘ba+’匹配以b开头后面紧跟至少一个a	ba,bay,bare
<字符串>	匹配包含指定的字符串文本	‘fa’	fan,afa,faad
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’匹配x或者z	dizzy,zebra,x-ray
[^]	匹配不在括号中的任何字符	‘[^abc]’匹配任何不包含a,b,c的字符串	desk,fox,f8ke
字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或更多的b	bbb,bbb,bbbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数	b{2,4}匹配最少2个，最多4个b	bb,bbb,bbbb

1 查询以特定字符或字符串开头的记录

　　‘^’匹配以特定字符或字符串开头的文本。

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^x';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| b5   |  107 | xxxx    | 3.6     |
| m2   |  105 | xbabay  | 2.6     |
| m3   |  105 | xxtt    | 11.6    |
| t4   |  107 | xbababa | 3.6     |
+------+------+---------+---------+
4 rows in set

2 查询以特定字符或字符串结尾的记录

　　‘$’匹配以特定字符或字符串结尾的文本。

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry | 10.2    |
| b2   |  104 | berry      | 7.6     |
| c0   |  101 | cherry     | 3.2     |
+------+------+------------+---------+
3 rows in set

3 用符号”.”来替代字符串中的任意一个字符

　　‘.’匹配任意一个字符

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1  |  102 | orange | 11.2    |
| m1   |  106 | mango  | 15.6    |
+------+------+--------+---------+
2 rows in set

4 使用”*”和”+”来匹配多个字符

　　星号’*’匹配前面的字符任意多次，包括0次；加号’+’匹配前面的字符至少一次。

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry | 10.2    |
| b2   |  104 | berry      | 7.6     |
| t1   |  102 | banana     | 10.3    |
+------+------+------------+---------+
3 rows in set

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| t1   |  102 | banana | 10.3    |
+------+------+--------+---------+
1 row in set

5 匹配指定字符串

　　正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，如果要匹配多个字符串，多个字符串之间使用分隔符’|’隔开：

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| bs2  |  105 | melon   | 8.2     |
| l2   |  104 | lemon   | 6.4     |
| o2   |  103 | coconut | 9.2     |
+------+------+---------+---------+
3 rows in set

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a1   |  101 | apple   | 5.2     |
| a2   |  103 | apricot | 2.2     |
| bs2  |  105 | melon   | 8.2     |
| l2   |  104 | lemon   | 6.4     |
| o2   |  103 | coconut | 9.2     |
| t2   |  102 | grape   | 5.3     |
+------+------+---------+---------+
6 rows in set

　　与REGEXP不同，LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行业不会返回。

mysql> SELECT * FROM fruits WHERE f_name LIKE 'on';
Empty set

6 匹配指定字符中的任意一个

　　方括号”[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。还可以指定数值集合。”[a-z]”表示集合区间为从a~z的字母，”[0-9]”表示集合区间为所有数字。

mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a2   |  103 | apricot | 2.2     |
| bs1  |  102 | orange  | 11.2    |
| bs2  |  105 | melon   | 8.2     |
| l2   |  104 | lemon   | 6.4     |
| m1   |  106 | mango   | 15.6    |
| m3   |  105 | xxtt    | 11.6    |
| o2   |  103 | coconut | 9.2     |
+------+------+---------+---------+
7 rows in set

7 匹配指定字符以外的字符

　　“[^字符集合]”匹配不在指定集合中的任何字符。
　　例，查询f_id字段包含字母a~e和数字1~2以外的字符的记录：

mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| b5   |  107 | xxxx    | 3.6     |
| bs1  |  102 | orange  | 11.2    |
| bs2  |  105 | melon   | 8.2     |
| c0   |  101 | cherry  | 3.2     |
| l2   |  104 | lemon   | 6.4     |
| m1   |  106 | mango   | 15.6    |
| m2   |  105 | xbabay  | 2.6     |
| m3   |  105 | xxtt    | 11.6    |
| o2   |  103 | coconut | 9.2     |
| t1   |  102 | banana  | 10.3    |
| t2   |  102 | grape   | 5.3     |
| t4   |  107 | xbababa | 3.6     |
+------+------+---------+---------+
12 rows in set

8 使用{n,}或者{n,m}来指定字符串连续出现的次数

　　字符串{n,}：匹配前面的字符串至少n次；
　　字符串{n,m}：匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数。

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b5   |  107 | xxxx   | 3.6     |
| m3   |  105 | xxtt   | 11.6    |
+------+------+--------+---------+
2 rows in set

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| m2   |  105 | xbabay  | 2.6     |
| t1   |  102 | banana  | 10.3    |
| t4   |  107 | xbababa | 3.6     |
+------+------+---------+---------+
3 rows in set

说明

　　阅读《MySQL5.5 从零开始学》笔记记录。