MYSQL - 模式匹配

MySQL提供标准的SQL模式匹配，可以使用"_"匹配单个字符，%匹配任意数量的字符(包括0个字符)。当使用模式匹配时，用LIKE和NOT 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    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)


mysql>

查询以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)


mysql>

查询名字中包含'w'的宠物

mysql> select * from pet where name like '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       |
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.01 sec)


mysql>

查询名字中精确包含5个字符的宠物，使用5个'_'匹配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>

MySQL提供的另一种类型的模式匹配用的是扩展的正则表达式。当使用这种类型的模式匹配时，用REGEXP和NOT REGEXP操作(或RLIKE和NOT RLIKE)

下面列表描述了扩展的正则表达式的一些使用

• "."匹配任意的单个字符
• "[...]"匹配在括号中的任意字符。如[abc]匹配“a", "b" 或"c"。匹配一个范围的字符，用一个连字符"-"，[a-z]匹配任意字母，而[0-9]匹配任意数字。
• "*"匹配0或多个其之前的实例。如"x*"匹配任意数量的"x"字符，"[0-9]*"匹配任意数量的数字，而".*"匹配任意数量的字符。
• 如果被测值的任意位置能与模式匹配，则REGEXP模式匹配成功（LIKE类型的模式匹配则要求整个被测值都匹配模式成功）
• 为使一个模式从开头或结尾匹配，使用"^"在模式开头或使用"$"在模式结尾。

使用REGEXP重写之前的查询语句

查询以"b"开头的名字的宠物

mysql> select * from pet where name rlike '^b';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)


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


mysql>

查询以fy结尾的名字的宠物

mysql> select * from pet where name rlike '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)


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)


mysql>

如果希望REGEXP比较是大小写敏感的，使用BINARY关键字。以下查询仅匹配小写字母"b"在名字的开头。

mysql> select * from pet where name rlike binary '^b';
Empty set (0.00 sec)


mysql> select * from pet where name regexp binary '^b';
Empty set (0.00 sec)


mysql>

查询名字中包含'w'的宠物

mysql> select * from pet where name rlike 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       |
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)


mysql> select * from pet where name regexp 'w';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       |
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)


mysql>

查询名字中精确包含5个字符的宠物

mysql> select * from pet where name rlike '^.....$';
+-------+--------+---------+------+------------+-------+
| 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 '^.....$';
+-------+--------+---------+------+------------+-------+
| 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>

也可以使用{n}（重复n次）操作

mysql> select * from pet where name rlike '^.{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)


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)


mysql>