mysql 匹配任意字符_MySQL入门-6：通配符匹配和正则表达式

曾经终败给现在 2022-11-19 08:00 260阅读 0赞

大纲

1、文件名匹配

2、LIKE操作符

3、使用正则表达式

一、文件名匹配

在Windows的CMD命令行和Linux的Bash中，都支持文件名通配，但是这个匹配能力非常弱，由于仅用于匹配文件名，所以也不算什么缺陷。比如我想查看后缀名为.txt的所有文件：\# ls \*.txt

通常支持的通配符有:\*     匹配任意字符任意次

？    仅匹配任意单个字符

\[0-9a-zA-Z\]      字符集，匹配任意列出的单个字符

\[^0-9a-zA-Z\]    字符集取反，匹配未列出的单个字符

二、LIKE 操作符

前一篇博客中的操作符都是针对已知值进行过滤的，我们在过滤条件中使用的值都是已知的。但是这种方法并不是什么时候都好用。利用通配符可以创建特定的文本匹配模式。通配符(wildcard)就是对于MySQL来说，具有特殊含义的特殊字符。

为了在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后面跟的搜索模式利用通配符匹配而不是直接相等(=)匹配进行比较。

MySQL支持几种通配符(不要和正则表达式搞混淆了)：%    表示任何字符出现任意次数， 和Bash命令行的文件名通配符\*含义相同

\_      下划线仅匹配单个字符而不是多个字符

\[ \]        字符集

![6b9333fc62c657e32b68105f5562e3d6.png][]

默认情况下，对于LIKE来说，%和\_都有特殊含义，如果我们需要匹配字面意义上的 % 或 \_ ， 我们需要对它进行转义。或者是放到字符集中(如下图所示)

![c85bcd2b411ebd0839916e396aa1aeba.png][]

检索prod\_name字段以 jet 开头的那些行：mysql> SELECT prod\_id ,prod\_name

\-> FROM products

\-> WHERE prod\_name LIKE 'jet%';

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。

注意NULL

虽然似乎 % 可以匹配任何东西，但有一个例外，那就是NULL。

使用通配符的技巧

使用通配符是需要付出代价的：通配符搜索的处理一般要比使用已知值搜索花费的时间更长。这里给出使用通配符的技巧：不要过度使用通配符

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的最开始处

仔细注意通配符的位置

三、使用正则表达式

正则表达式是一种表达能力非常强大的文本匹配描述工具。MySQL对正则表达式提供了初步的支持，允许指定正则表达式，过滤SELECT检索出的数据。MySQL仅支持正则表达式(ERE)的一个子集，使用正则表达式和LIKE的语法比较相似，只是使用REGEXP关键字，它告诉MySQL后面跟的内容作为正则表达式处理。

检索prod\_name中包含’1000‘的那些行：mysql> SELECT prod\_name

\-> FROM products

\-> WHERE prod\_name REGEXP '1000'

\-> ORDER BY prod\_name;

LIKE和REGEXP之间的重要差别：LIKE匹配列值整个串，而REGEXP匹配列值的子串。LIKE必须完全匹配整个列。如果被匹配的文本仅在列值中出现，LIKE将不会找到它，相应的行也不会被返回(除非使用通配符)。

REGEXP在列值中进行部分或完全匹配(是否成功匹配)，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行被返回。那么REGEXP能不能用来匹配整个列值(与LIKE作用相同)？ 答案是肯定的，使用锚点^和$即可。对于MySQL来说，整个列值(字段的值)就是一个完整的行。

LIKE必须要完全匹配列值整个串，结果才为 TRUE。而REGEXP 仅仅匹配列值的一个子串，结果就为 TRUE。

关于大小写的区分：

MySQL中正则表达式匹配(从版本3.23.4后)不区分大小写 。如果要区分大小写，应该使用BINARY关键字，如where prod\_name REGEXP BINARY 'Hello .000'a、匹配字符类

\[:alnum:\]     任意字母和数字(通［a-zA-Z0-9］)

\[:alpha:\]     任意字符(同\[a-zA-Z\])

\[:blank:\]     空格和制表符(同\[\\\\t\])

\[:digit:\]     任意数字(同\[0-9\])

\[:lower:\]     任意小写字母

\[:upper:\]     任意大写字母

\[:space:\]     包括空格在内的任意空白字符

b、量词

\*      0个或者多个匹配

\+      1个或者多个匹配(等于\{1,\})

？     1个或者多个匹配(等于\{0,1\})

\{n\}    指定数目的匹配

\{n,\}   不少于指定数目的匹配

\{n,m\}  匹配数目的范围(m不超过255)

元字符和转义序列的讨论：

![44106701e54d1e88e81d94eb41972104.png][]

a、LIKE 能够识别%和\_这两个特殊字符，如果我仅想匹配 %或 \_ 字符怎么办？

默认情况下，对于LIKE来说，%和\_都有特殊含义，我们需要对它进行转义。LIKE "\\%";  LIKE "\\\_" 。但是 '\\%' 和 '\\\_'序列仅用于搜索可能会解释为通配符的模式匹配环境中的 '%'和 '\_'。请注意如果你在其他环境中使用 '\\%'或 '\\\_'， 它们返回字符串'\\%'和'\\\_',而不是'%'和'\_'。 在其他转义序列中，反斜线被移除。也就是说，如果 \\ 之后接的字符，不是MySQL的特殊字符，则会把\\ 移除。

b、对于正则表达式来说，它有自己的一套元字符(meta character)， 如何转义正则表达式的元字符呢？

"\\"对于正则表达式本身来说，是一个元字符。可以通过"\\"对正则表达式的其他元字符进行转义。但是这里有一个问题，"\\"同时也是mysql的元字符，字符串在传给正则表达式引擎的时候，首先会被mysql处理一次。

假设，我要匹配包含小数点， 则要REGEXP "\\\\."，首先mysql处理解释一次，传给正则表达式引擎的部分： REGEXP "\\."， 然后正则表达式引擎再次处理， 这样就把 "." 转义了。

c、如果结合编程语言，C或者JAVA，来处理这种特殊字符，情况变得更加复杂

d、关键在于区分是哪一个程序在解释这些元字符，下一个程序接收到的字符串是什么

MySQL转义正则表达式特殊字符：必须用 \\\\

(必须区分是那个程序的特殊字符： meta字符)

因为MySQL自身有一组特殊字符，需要用特殊字符 \\ 进行转义。然而对于正则表达式，其又有一组正则表达式特殊字符，所以还需要对自身的\\先进行转义\\\\，然后传递给REGEXP 才是单个 \\ 。

多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠。【MySQL自身解释一个， 正则表达式库解释另一个】

简单的正则表达式测试：

可以用SELECT来测试正则表达式。 REGEXP检查总是返回0(不匹配)或1(匹配)。mysql> SELECT 'hello' REGEXP '\[0-9\]';

当编写应用程序时，在包含这些特殊字符的字符串用于发送到MySQL服务器的SQL语句中的数据值之前，必须对它们正确进行转义。可以用两种方法来完成：用转义特殊字符的函数处理字符串。例如，在C程序中，可以使用mysql\_real\_escape\_string()  C API函数来转义字符。参见25.2.3.52节，“mysql\_real\_escape\_string()”。Perl  DBI接口提供一个quote方法来将特殊字符转换为正确的转义序列。参见25.4节，“MySQL Perl  API”。

显式转义特殊字符，许多MySQL  API提供了占位符功能，允许你在查询字符串中插入特殊标记，然后当你发出查询时将数据值同它们绑定起来。在这种情况下，API关注转义值中的特殊字符。

SQL Server :对于其他的特殊字符：'^'， '-'， '\]' 因为它们本身在包含在 '\[\]' 中使用，所以需要用另外的方式来转义，于是就引入了 like 中的 escape 子句，另外值得注意的是：escape 可以转义所有的特殊字符(可以把任意的字符当做通配符)。

select 1 where '^ABCDE' like '!^ABCDE' escape '!'

select 1 where '-ABCDE' like '!-ABCDE' escape '!'

select 1 where '\]ABCDE' like '!\]ABCDE' escape '!'

select 1 where '%ABCDE' like '\\%ABCDE' escape '\\'

select 1 where '%ABCDE' like '!%ABCDE' escape '!'

select 1 where '%ABCDE' like '\#%ABCDE' escape '\#'

select 1 where '%ABCDE' like '@%ABCDE' escape '@'

select 1 where '\[ABCDE' like '!\[ABCDE' escape '!'

select 1 where '\]ABCDE' like '!\]ABCDE' escape '!'

规律就是用 escape 后面紧跟着的字符来做转义字符。 escape 后面的字符相当于 C 语言字符串中的转义字符 '\\'。

[6b9333fc62c657e32b68105f5562e3d6.png]: /images/20221119/c9f01f6e7b924e1f93c7dfd3aebf02a1.png
[c85bcd2b411ebd0839916e396aa1aeba.png]: /images/20221119/301101586a3640d3889408ea4adce6b0.png
[44106701e54d1e88e81d94eb41972104.png]: https://img-blog.csdnimg.cn/img_convert/44106701e54d1e88e81d94eb41972104.png