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

来源：互联网发布：毛笔字知乎编辑：程序博客网时间：2024/05/29 18:03

使用正则表达式查询

　　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 setmysql> 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 setmysql> 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 setmysql> 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 从零开始学》笔记记录。

1 0