1.3.4.7 匹配模式

MySQL 提供了一种标准 SQL 匹配模式，使用 _ 匹配任何单个字符，使用 % 匹配任意数量的字符（包括0个字符），默认情况下 SQL 匹配模式是不区分大小写的。在使用匹配模式时，不要使用类似于 = 、<、>等比较运算符。

Note

在 SQL 的语句中，如果匹配的字符串需要区分大小写，那么，有两种做法，一是在 SQL 语句中增加 binary 关键字，例如：
SELECT * FROM pet WHERE binary name LIKE 'b%'; 或 SELECT * FROM pet WHERE name LIKE binary 'b%'; 关键字 binary 的位置不同，效果是一样的。另一种做法是：将 pet 表中 name 子段的数据类型由 varchar 更改成 binary、varbinary即可。

关键字 like 是我们在模糊匹配中，经常用到的，下面来看几个例子：

检索名字以 b 开头

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-30 | 1995-07-29 |
+----------------------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

检索名字以 fy 结尾

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

检索名字中包含一个 w 字符

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-30 | 1995-07-29 |
| Whistler             | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------------------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

检索 5 个字符的名字

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

MySQL 除了 LIKE 和 NOT LIKE 外，还提供了一种使用正则表达式的匹配模式，使用关键字 REGEXP 、NOT REGEXP 或 RLIKE、NOT RLIKE（ RLIKE 是 REGEXP 的别名），下面的列表给出使用正则表达式的一些特性。

• . 匹配任意单个字符
• [] 中括号匹配；例如：[abc] 匹配a、b 或者 c；[0-9]匹配数字；[a-z]匹配字母；
• *匹配0或多个字符；例如：x* 匹配0或多个字母x；[0-9]*匹配0或多个数字；.*匹配0或多个字符；
• REGEXP 的 LIKE 区别；LIKE 匹配整个值，而 REGEXP 匹配值的某一部分，除非使用 ^ $ 来表示匹配整个值；

下面来看一下使用 REGEXP 实现上面的示例：

检索名字以 b 开头

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-30 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)

如果要区分大小写：

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

检索名字以 fy 结尾

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

检索名字中包含一个 w 字符

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-30 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

检索 5 个字符的名字

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

更推荐使用下面的这种方式：

mysql> select * from pet where name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)