MySQL 提供标准的 SQL 模式匹配以及一种基于扩展正则表达式的模式匹配,类似于 Unix 实用程序(如 vi、grep 和 sed)使用的那些。
SQL 模式匹配使您可以使用 _ 匹配任何单个字符,使用 % 匹配任意数量的字符(包括零个字符)。在 MySQL 中,SQL 模式默认不区分大小写。此处显示了一些示例。使用 SQL 模式时不要使用 = 或 <>。请改用 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 | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
要查找以 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 |
+--------+--------+---------+------+------------+-------+
要查找包含 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-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
要查找恰好包含五个字符的名称,请使用 _ 模式字符的五个实例:
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 提供的另一种模式匹配使用扩展的正则表达式。当您测试此类模式的匹配时,请使用 REGEXP_LIKE() 函数(或 REGEXP 或 RLIKE 运算符,它们是 REGEXP_LIKE() 的同义词)。
以下列表描述了扩展正则表达式的一些特征:
- . (小圆点)匹配任何单个字符。
- 字符类 […] 匹配括号内的任何字符。例如,[abc] 匹配 a、b 或 c。要命名一系列字符,请使用破折号。 [a-z] 匹配任何字母,而 [0-9] 匹配任何数字。
- 匹配它前面的事物的零个或多个实例。例如,x 匹配任意数量的 x 字符,[0-9] 匹配任意数量的数字,而 .* 匹配任意数量的任何内容。
- 如果模式匹配在测试的值中的任何位置都能匹配,则正则表达式模式匹配成功。 (这与 LIKE 模式匹配不同,后者仅在模式匹配整个值时才成功。)
- 要锚定一个模式,使其必须匹配被测试值的开头或结尾,请在模式的开头使用 ^ 或在模式的结尾使用 $。
为了演示扩展正则表达式的工作原理,前面显示的 LIKE 查询在此处重写为使用 REGEXP_LIKE()。
要查找以 b 开头的名称,请使用 ^ 匹配名称的开头:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^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 |
+--------+--------+---------+------+------------+------------+
要强制正则表达式比较区分大小写,请使用区分大小写的排序规则,或使用 BINARY 关键字使其中一个字符串成为二进制字符串,或指定 c 匹配控制字符。这些查询中的每一个都只匹配名称开头的小写 b:
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
要查找以 fy 结尾的名称,请使用 $ 匹配名称的结尾:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
要查找包含 w 的名称,请使用以下查询:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '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 |
+----------+-------+---------+------+------------+------------+
因为如果正则表达式模式出现在值中的任何位置,它就会匹配,因此在前面的查询中,没有必要在模式的任一侧放置通配符以使其匹配整个值,就像 SQL 模式那样。
要查找恰好包含五个字符的名称,请使用 ^ 和 $ 来匹配名称的开头和结尾,以及 .介于两者之间:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
您还可以使用 {n}(“repeat-n-times”)运算符编写前面的查询:
mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+