Skip to main content

第 09 篇 字符串模式匹配

imodouAbout 7 minMySQL

《MySQL 入门教程》第 09 篇 字符串模式匹配

上一篇我们介绍了如何通过 WHERE 查询条件过滤数据，包括比较运算符、逻辑运算符以及空值判断等。本篇我们来讨论一下字符串的模糊查找。

9.1 LIKE 运算符

MySQL 中的LIKE运算符可以用于判断字符串是否包含某个模式，返回 1（True）或者 0（False）。使用LIKE运算符的语法如下：

expr LIKE pat

如果表达式 expr 能够匹配模式 pat，结果返回 True（1）；否则，返回 False（0）。如果 expr 或者 pat 为 NULL， 返回 NULL。

根据 SQL 标准，MySQL 支持两个通配符：

  • 百分号（%）匹配零个或多个任意字符。
  • 下划线（_）匹配一个任意字符。

例如，以下语句用于查询“关”姓员工：

select emp_name
from employee
where emp_name like '关%';
  emp_name|
----------|
关兴      |
关平      |
关羽      |

其中，“关%”表示以“关”字开始的字符串。另外，“%xyz%”表示包含 xyz 的字符串；“%xyz”表示以 xyz 结束的字符串。

以下语句演示了下划线的作用：

select emp_name
from employee
where emp_name like '孙__';
  emp_name|
----------|
孙丫鬟     |
孙尚香     |

其中，“孙__”表示以“孙”字开始并且姓名为三个字的员工；因此查询结果中没有包含“孙乾”。

9.1.1 转义字符

由于百分号和下划线是通配符，具有特殊的意义。当我们想要判断字符串中是否包含这两个字符时，例如“50%”，就需要使用一个转义字符将模式中的通配符解释为普通字符。转义字符使用ESCAPE进行指定：

expr LIKE pat ESCAPE 'escape_char'

默认情况下，MySQL 使用反斜线（\）作为转义字符。例如：

select '完成进度：50% 已完成。' like '%50%%' as like1, '日期 20150101' like '%50%%' as like2;
like1|like2|
-----|-----|
    1|    1|

select '完成进度：50% 已完成。' like '%50\%%' as like1, '日期 20150101' like '%50\%%' as like2;
like1|like2|
-----|-----|
    1|    0|

第一个查询没有使用转义字符，直接使用“50%” 进行匹配，结果“日期 20150101”也满足条件；第二个查询使用转义字符，“50%”只匹配百分之五十（50%）。

我们也可以指定其他的转义字符，例如：

select '2020_06_13' like '%06#_13%' escape '#' as like3;
like3|
-----|
    1|

以上语句使用“#”作为转义字符。此时如果字符串中存在“#”，需要连写两个“#”表示匹配井号自身。

另外，需要注意 MySQL 中的LIKE运算符不区分大小写。例如：

select 'Tony' like 'tony';
'Tony' like 'tony'|
------------------|
                 1|

如果想要实现区分大小写的匹配，可以使用下文中的REGEXP_LIKE函数。

上一篇中介绍的NOT运算符可以将表达式的结果进行取反，因此NOT LIKE运算符可以进行反向模式匹配。例如：

select emp_name, email
from employee
where email not like '%a%';
  emp_name|email             |
----------|------------------|
刘备      |liubei@shuguo.com |
糜竺      |mizhu@shuguo.com  |
邓芝      |dengzhi@shuguo.com|

该语句返回了 email 中不包含字母 a 的员工。

9.2 正则表达式匹配

LIKE 运算符可以实现简单的模式匹配。但是当我们需要匹配更复杂的模式时，例如判断用户输入的电子邮箱是否合法，则无法通过 LIKE 运算符实现。为此， MySQL 提供了更加强大的正则表达式（Regular Expression）函数和运算符。

正则表达式是一个包含字母、数字和特殊符号的模式，可以用于检索或者替换符合某个模式（规则）的文本字符串。正则表达式可以用于查找电子邮箱、IP 地址、身份证等具有特定规则的数据，也可以用于验证用户名是否符合指定规则（例如只包含字符、数字、下划线并且字符数量为某个范围）。

📝关于正则表达式的具体内容可以参考 GitHub上的 正则表达式教程。

MySQL 提供以下三种的正则表达式匹配函数和运算符：

REGEXP_LIKE(expr, pat[, match_type])
expr REGEXP pat
expr RLIKE pat

如果字符串 expr 匹配模式 pat 指定的正则表达式，返回 Ture（1），否则返回 False（0）。如果 expr 或者 pat 为 NULL，返回 NULL。可选参数 match_type 可以用于指定匹配方式，可以是以下选项之一或者全部：

  • c：区分大小写；
  • i：不区分大小写，默认方式；
  • m：多行匹配，可以识别字符串内部的行终止符。默认情况下只在字符串的开始或结束匹配行终止符；
  • n：点号（ . ）匹配行终止符。默认情况下，点号遇到一行的结束时会终止匹配；
  • u：只匹配 Unix 行终止符，此时只有换行符被 .、^ 和 $ 看作一行的结束。

REGEXP 和 RLIKE 运算符实际上是 REGEXP_LIKE 函数的同义词，但是不支持 match_type 匹配选项，而是使用默认选项。

我们看一个区分大小写的匹配示例：

select regexp_like('Tony', 'tony') as regexp1, regexp_like('Tony', 'tony', 'c') as regexp2;
regexp1|regexp2|
-------|-------|
      1|      0|

默认情况下不区分大小写，所以返回了 1；c 选项表示区分大小写，因此返回了 0。

正则表达式的强大之处在于它提供了许多元字符（metacharacter），可以用于构造复杂的模式。下表列出了 MySQL 支持的元字符：

元字符作用示例
^匹配字符串的开始SELECT REGEXP_LIKE('fo\nfo', '^fo$'); -- 0 SELECT REGEXP_LIKE('fofo', '^fo'); -- 1
$匹配字符串的结束SELECT REGEXP_LIKE('fo\no', '^fo\no$'); -- 1 SELECT REGEXP_LIKE('fo\no', '^fo$'); -- 0
.匹配任意单个字符SELECT REGEXP_LIKE('fofo', '^f.$'); -- 1 SELECT REGEXP_LIKE('fo\r\nfo', '^f.$'); -- 0 SELECT REGEXP_LIKE('fo\r\nfo', '^f.$', 'm'); -- 1 SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.$'); -- 1
*匹配前面的字符零次或者多次SELECT REGEXP_LIKE('Baaan', '^Ban'); -- 1 SELECT REGEXP_LIKE('Ban', '^Ban'); -- 1 SELECT REGEXP_LIKE('Bn', '^Ba*n'); -- 1
+匹配前面的字符一次或者多次SELECT REGEXP_LIKE('Ban', '^Ba+n'); -- 1 SELECT REGEXP_LIKE('Bn', '^Ba+n'); -- 0
?匹配前面的字符零次或者一次SELECT REGEXP_LIKE('Bn', '^Ba?n'); -- 1 SELECT REGEXP_LIKE('Ban', '^Ba?n'); -- 1 SELECT REGEXP_LIKE('Baan', '^Ba?n'); -- 0
|匹配左边或者右边的模式SELECT REGEXP_LIKE('pi', 'pi|apa'); -- 1 SELECT REGEXP_LIKE('apa', 'pi|apa'); -- 1 SELECT REGEXP_LIKE('axe', 'pi|apa'); -- 0
匹配前面的字符 n 次SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e'); -- 1 SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e'); -- 0
匹配前面的字符 m 次到 n 次SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e'); -- 1
[…]匹配方括号内的任意单个字符，支持使用连字符号（-）指定一个范围SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]'); -- 1
[^…]匹配不在方括号内的任意单个字符，支持使用连字符号（-）指定一个范围SELECT REGEXP_LIKE('gheis', '[^a-dXYZ]+'); -- 1
[:character_class:]匹配一个字符类，例如 [:alnum:] 可以匹配所有的字母和数字SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+'); -- 1 SELECT REGEXP_LIKE('!!', '[[:alnum:]]+'); -- 0

对于 Web 开发中常见邮箱地址合法性验证，可以采用以下简单的规则：

  • 以字母或者数字开头；
  • 后面是一个或者多个字母、数组或特殊字符（ . _ - ）；
  • 然后是一个 @ 字符；
  • 之后包含一个或者多个字母、数组或特殊字符（ . - ）；
  • 最后是域名，即 . 以及 2 到 4 个字母。

使用正则表达式可以表示为：

^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$

其中，^ 匹配字符串的开头；[a-zA-Z0-9] 匹配大小写字母或数字；+ 表示匹配前面的内容一次或多次；. 匹配任何一个字符，. 匹配点号自身；{2,4} 匹配前面的内容 2 次到 4次；$ 匹配字符串的结束。

我们创建一个测试表：

CREATE TABLE t_regexp (
  email VARCHAR(50)
);

INSERT INTO t_regexp VALUES ('TEST@shuguo.com');
INSERT INTO t_regexp VALUES ('test@shuguo');
INSERT INTO t_regexp VALUES ('.123@shuguo.com');
INSERT INTO t_regexp VALUES ('test+email@shuguo.cn');
INSERT INTO t_regexp VALUES ('me.me@ shuguo.com');
INSERT INTO t_regexp VALUES ('123.test@shuguo-sanguo.org');

使用以下语句查找合法的邮箱地址：

SELECT email
  FROM t_regexp
 WHERE REGEXP_LIKE(email, '^[a-zA-Z0-9]+[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,4}$');

email                     |
--------------------------|
TEST@shuguo.com           |
123.test@shuguo-sanguo.org|

查询返回了两个合法的邮箱地址。注意其中的转义字符需要使用两个反斜线（\），因为 MySQL 解析器会解析一个反斜线，正则表达式会解析另一个。

📝表达式expr NOT REGEXP pat和expr NOT RLIKE pat可以用于执行反向模式匹配。

Default footer
Copyright © 2024 imodou