Pattern Matching

MySQL提供了标准的SQL模式匹配,和以Unix中正则表达式为基础的模式匹配.

SQL模式匹配允许你使用_来匹配任意单个字符,使用%来匹配任意数量的字符(包括零个字符).在MySQL中,SQL模式匹配默认情况下对大小写不敏感.下面是一些例子.在SQL模匹配式中,不是使用=,<>,而是使用LIKE,NOT LIKE.

为了找到以b开头的name值:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

找到以fy结尾的name值:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

找到包含wname值:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

找到name值为五个字符长度,使用五个_:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

MySQL中其他类型的模式匹配是扩展的正则表达式,当你使用这种类型的模式匹配时,使用REGEXPNOT REGEXP,或者RLIKENOT RLIKE,两者是等价的.

下面描述了扩展的正则表达式的特征:

  • .匹配任意单个字符;
  • [...]匹配方括号内的任意字符,例如'[abc]'匹配'a','b','ab''c'.'[a-z]'匹配任意字母,'[0-9]'匹配任意数字;
  • *匹配零个或多个它前面的实例,例如'x*'匹配任意多个'x','[0-9]*'匹配任意多个数字,'.*'匹配任意多个字符;
  • 值中的任意部分匹配成功,REGEXP匹配成功,整个值匹配成功,LIKE才匹配成功;
  • 在匹配开头使用^,来保证一定要匹配开头,在匹配末尾使用$,来保证一定要匹配末尾.

为了演示扩展的正则表达式是如何工作的,使用REGEXP代替LIKE重写前面的例子:

为了找到以b开头的name值,使用^匹配name开头:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

如果你想强制REGEXP匹配对大小写敏感,使用关键字BINARY,这个查询只匹配以小写的b开头的name值:

1
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

找到以fy结尾的name值,使用$来匹配name末尾:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+

找到包含wname值:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+

因为正则表达式匹配在值的任意位置出现的w,所以不需要像使用SQL匹配那样,在其两侧放置通配符.

找到name值为五个字符长度,使用^匹配name开头,使用$来匹配name末尾:

1
2
3
4
5
6
7
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+

也可以使用{n}操作符这样写查询:

1
mysql> SELECT * FROM pet WHERE name REGEXP ‘^.{5}$’;

注意模式匹配是:使用关键字后面的字符匹配关键字前面的字符:

上面的查询若写为:

1
mysql> SELECT * FROM pet WHERE ‘^…..$’ REGEXP name;

得不到任何查询结果.

英文原文:

Pattern Matching