# 模式匹配操作符 数据库提供了三种独立的实现模式匹配的方法：SQL LIKE操作符、SIMILAR TO操作符和POSIX-风格的正则表达式。除了这些基本的操作符外，还有一些函数可用于提取或替换匹配子串并在匹配位置分离一个串。 #### LIKE 描述：判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。 匹配规则： - 此操作符只有在它的模式匹配整个串时才能成功。若需匹配串内任意位置的字符序列，该模式必须以百分号开始和结束。 - 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 - 要匹配文本里的下划线或者百分号，在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。 - 要匹配逃逸字符本身，需要输入两个逃逸字符。例如要写一个包含反斜线的模式常量，需要在SQL语句中输入两个反斜线。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) - 参数standard_conforming_strings设置为off时，在文串常量中的任何反斜线需进行双写处理。因此，要在模式中匹配单个反斜线，SQL语句中需输入四个反斜线（可通过使用ESCAPE选择不同的逃逸字符来避免这种情况，此操作将使反斜线不再是LIKE的特殊字符。但仍然是字符文本分析器的特殊字符，所以需要两个反斜线。） - 在MYSQL模式下，可以通过写ESCAPE ''的方式不选择逃逸字符，此操作可以有效地禁用逃逸机制，但无法消除下划线和百分号在模式中的特殊含义。 - 关键字ILIKE可以用于替换LIKE，区别是LIKE大小写敏感，ILIKE大小写不敏感。 - 操作符\~\~等效于LIKE，操作符\~\~\*等效于ILIKE。 示例： ``` gaussdb=# SELECT 'abc' LIKE 'abc' AS RESULT; result ----------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' LIKE 'a%' AS RESULT; result ----------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' LIKE '_b_' AS RESULT; result ----------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' LIKE 'c' AS RESULT; result ----------- f (1 row) ``` #### SIMILAR TO 描述：SIMILAR TO操作符根据自己的模式是否匹配给定串而返回真或者假。该操作符与LIKE语法类似，但采用SQL标准定义的正则表达式进行模式匹配。 匹配规则： - 和LIKE相同，此操作符仅当模式匹配整个串时才能成功。若需匹配串中任何位置的字符序列，模式需要以百分号（%）开头和结尾。 - 下划线 （_）代表（匹配）任何单个字符； 百分号（%）代表任意串的通配符。 - SIMILAR TO也支持以下从POSIX正则表达式借用的模式匹配元字符。 | 元字符 | 含义 | |:---|:---| | \| | 表示选择（两个候选之一）。 | | \* | 表示重复前面的项零次或更多次。 | | + | 表示重复前面的项一次或更多次。 | | ? | 表示重复前面的项零次或一次。 | | {m} | 表示重复前面的项刚好m次。 | | {m,} | 表示重复前面的项m次或更多次。 | | {m,n} | 表示重复前面的项至少m次并且不超过n次。 | | () | 把多个项组合成一个逻辑项。 | | \[...\] | 声明一个字符类，与POSIX正则表达式相同。 | - 前导逃逸字符可以禁止所有这些元字符的特殊含义。逃逸字符的使用规则和LIKE相同。 正则表达式函数： 支持使用函数[substring(string from pattern for escape)](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section87461432711)截取匹配SQL正则表达式的子字符串。 示例： ``` gaussdb=# SELECT 'abc' SIMILAR TO 'abc' AS RESULT; result ----------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' SIMILAR TO 'a' AS RESULT; result ----------- f (1 row) ``` ``` gaussdb=# SELECT 'abc' SIMILAR TO '%(b|d)%' AS RESULT; result ----------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' SIMILAR TO '(b|c)%' AS RESULT; result ----------- f (1 row) ``` #### POSIX正则表达式 描述：正则表达式是一个字符序列，定义一个串集合 （一个正则集）的缩写。 如果一个串是正则表达式描述的正则集中的一员时， 则说明这个串匹配该正则表达式。 POSIX正则表达式提供了比LIKE和SIMILAR TO操作符更强大的含义。[表1 正则表达式匹配操作符]列出了所有可用于POSIX正则表达式模式匹配的操作符。 表1正则表达式匹配操作符 | 操作符 | 描述 | 例子 | |:---|:---|:---| | \~ | 匹配正则表达式，大小写敏感 | 'thomas' \~ '.\*thomas.\*' | | \~\* | 匹配正则表达式，大小写不敏感 | 'thomas' \~\* '.\*Thomas.\*' | | !\~ | 不匹配正则表达式，大小写敏感 | 'thomas' !\~ '.\*Thomas.\*' | | !\~\* | 不匹配正则表达式，大小写不敏感 | 'thomas' !\~\* '.\*vadim.\*' | 匹配规则： - 与LIKE不同，正则表达式允许匹配串里的任何位置，除非该正则表达式显式地挂接在串的开头或者结尾。 - 除了上文提到的元字符外， POSIX正则表达式还支持下列模式匹配元字符。 | 元字符 | 含义 | |:---|:---| | \^ | 表示串开头的匹配 | | $ | 表示串末尾的匹配 | | . | 匹配任意单个字符 | 正则表达式函数： POSIX正则表达式支持以下函数。 - [substring(string from pattern)](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section1651228277)函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。 - [regexp_count(string text, pattern text \[, position int \[, flags text\]\])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section1636526192814)函数提供了获取匹配POSIX正则表达式模式的子串数量的功能。 - [regexp_instr(string text, pattern text \[, position int \[, occurrence int \[, return_opt int \[, flags text\]\]\]\])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section4825103732813)函数提供了获取匹配POSIX正则表达式模式子串位置的功能。 - [regexp_substr(string text, pattern text \[, position int \[, occurrence int \[, flags text\]\]\])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section942313451272)函数提供了抽取一个匹配POSIX正则表达式模式的子串的方法。 - [regexp_replace(string, pattern, replacement \[,flags \])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section133551451164013)函数提供了将匹配POSIX正则表达式模式的子串替换为新文本的功能。 - [regexp_matches(string text, pattern text \[, flags text\])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section131071150172811)函数返回一个文本数组，该数组由匹配一个POSIX正则表达式模式得到的所有被捕获子串构成。 - [regexp_split_to_table(string text, pattern text \[, flags text\])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section15141545305)函数把一个POSIX正则表达式模式当作一个定界符来分离一个串。 - [regexp_split_to_array(string text, pattern text \[, flags text \])](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/gaussdb-12-1910.html#ZH-CN_TOPIC_0000002527826090__zh-cn_topic_0000002526896266_section22271152142913)和regexp_split_to_table类似，是一个正则表达式分离函数，不过它的结果以一个text数组的形式返回。 ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 正则表达式分离函数会忽略零长度的匹配，这种匹配发生在串的开头或结尾或者正好发生在前一个匹配之后。这和正则表达式匹配的严格定义是相悖的，后者由regexp_matches实现，但是通常前者是实际中最常用的行为。 示例： ``` gaussdb=# SELECT 'abc' ~ 'Abc' AS RESULT; result -------- f (1 row) ``` ``` gaussdb=# SELECT 'abc' ~* 'Abc' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' !~ 'Abc' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'abc'!~* 'Abc' AS RESULT; result -------- f (1 row) ``` ``` gaussdb=# SELECT 'abc' ~ '^a' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' ~ '(b|d)'AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'abc' ~ '^(b|c)'AS RESULT; result -------- f (1 row) ``` 虽然大部分的正则表达式搜索都能很快地执行，但是仍可能被人为地处理成需要任意长的时间和任意量的内存。不建议从非安全模式来源接受正则表达式搜索模式，如果必须这样做，建议加上语句超时限制。使用SIMILAR TO模式的搜索具有同样的安全性危险， 因为SIMILAR TO提供了很多和POSIX-风格正则表达式相同的能力。LIKE搜索比其他两种选项简单得多，因此在接受非安全模式来源搜索时要更安全些。 #### \[NOT\] REGEXP/ \[NOT\] RLIKE 描述：REGEXP操作符用于正则表达式匹配，符合POSIX正则匹配规则。根据自己的模式是否匹配给定字符串而返回真或者假。 下表描述了正则表达式操作符： | 操作符名称 | 描述 | 语法说明 | |:---|:---|:---| | REGEXP | 字符串是否与正则表达式匹配 | expr REGEXP pat | | RLIKE | 字符串是否与正则表达式匹配（同REGEXP） | expr RLIKE pat | | NOT REGEXP | 字符串是否与正则表达式不匹配 | expr NOT REGEXP pat | | NOT RLIKE | 字符串是否与正则表达式不匹配（同NOT REGEXP） | expr NOT RLIKE pat | 匹配规则： - 正则表达式允许匹配字符串中的任何位置，除非该正则表达式显式地拼接在字符串的起始位置或结尾位置。 - REGEXP操作符支持的模式匹配元字符与POSIX正则表达式支持的模式匹配元字符一致。 - 操作符支持的转义字符匹配包括： | 转义字符 | 描述 | |:---|:---| | \\b | 退格键。 | | \\f | 换页符，如C语言。 | | \\n | 换行符，如C语言。 | | \\r | 回车符，如C语言。 | | \\t | 水平制表符，如C语言。 | | \\uwxyz | （其中wxyz正好是四个十六进制数字）十六进制值为0xwxyz的字符。 | | \\xhhh | （其中hhh是十六进制数字的任何序列）十六进制值为0xhhh的字符。 | | \\xy | （其中xy正好是两个八进制数字）八进制值为0xy的字符。 | | \\xyz | （其中xyz正好是三个八进制数字）八进制值为0xyz的字符。 | - 模式字符串pat范围匹配：\[a-dX\]，\[\^a-dX\] \[a-dX\]表示匹配任何a、b、c、d或X的字符。\[\^a-dX\]表示匹配不是a、b、c、d或X的字符。 两个字符之间的"-"构成一个范围，表示范围内所有字符匹配。要想包含文字字符"\]"，该文字字符必须紧跟在第一个方括号"\["之后。要想包含文字字符"-"，它必须在第一个方括号"\["之后或在结束的方括号"\]"之前。对于\[\]内未定义任何特殊含义的任何字符，与其本身匹配。 示例： ``` gaussdb=# SELECT 'abd' REGEXP 'a[bc]d' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'aed' REGEXP 'a[^bc]d' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'a-' REGEXP 'a[-b]' AS RESULT; result -------- t (1 row) ``` ``` gaussdb=# SELECT 'aX]bc' REGEXP '^[]a-dXYZ]*$' AS RESULT; result -------- t (1 row) ``` - 模式字符串pat中\[.characters.\]匹配元素的字符序列。在括号表达式中（使用"\["和"\]"），匹配用于校对元素的字符序列。字符为单个字符或诸如"space"等字符名。在文件regex/regc_locale中，可找到字符名称的完整列表。 示例： ``` gaussdb=# SELECT ' ' REGEXP '[[.space.]]' AS RESULT; result -------- t (1 row) ``` - 模式字符串pat中\[=character_class=\]字符匹配的字符类。在方括号表达式中编写，\[=character_class=\]代表等价类。它匹配具有相同排序校对值的所有字符，包括其本身。例如，如果o和(+)是等同类的成员，那么\[\[=o=\]\]、\[\[=(+)=\]\]和\[o(+)\]是同义词。等同类不得用作范围的端点。 - 模式字符串pat中\[:character_class:\]字符匹配的字符类。在方括号表达式中编写（使用"\[\]"），\[:character_class:\]表示匹配属于该类的所有字符的字符类。特定区域可能会提供其他类名。字符类不得用作范围的端点。下表列出了标准类名。当涉及\\字符时，需按说明配置相应的参数。 | 字符类名 | 描述 | 字符范围 | |:---|:---|:---| | alnum | 文字数字字符 | \[0-9a-zA-Z\] | | alpha | 文字字符 | \[a-zA-Z\] | | blank | 空白字符 | 空白字符\[\\t\] | | cntrl | 控制字符 | \[\\x01-\\x1F\] | | digit | 数字字符 | \[0-9\] | | graph | 图形字符 | \[\^\\x01-\\x20\] | | lower | 小写文字字符 | \[a-z\] | | print | 图形字符 | \[\^\\x01-\\x20\] | | punct | 标点字符 | \[-!"#$%\&'( )\*+,./:;\<=\>?@\[\\\\\]\^_\`{\|}\~\] | | space | 空格、制表符、新行、回车 | \[\\n\\r\\t\\x0B\] | | upper | 大写文字字符 | \[A-Z\] | | xdigit | 十六进制数字字符 | \[0-9a-fA-F\] | 示例： ``` gaussdb=# SELECT '\n' REGEXP '[[:space:]]' AS RESULT; result -------- t (1 row) ``` - 模式字符串pat中\[\[:\<:\]\], \[\[:\>:\]\]匹配字符串的开始和结束匹配。 示例： ``` gaussdb=# SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]' AS RESULT; result -------- t (1 row) ``` - 匹配特殊字符的文字实例，需要在特殊字符前加入两个反斜线，包括(、)、"、\^、+、? 特殊字符。特别地，对与单引号的匹配，需要在源字符串写'\\''，与模式字符串中'\\\\\\''匹配。当涉及\\字符时，需按说明配置相应的参数。 示例： ``` gaussdb=# SELECT 'a+b' REGEXP 'a\\+b' AS RESULT; result -------- t (1 row) gaussdb=# SELECT '\'' REGEXP '\\\'' AS RESULT; result -------- t (1 row) gaussdb=# SELECT '\\' REGEXP '\\\\' AS RESULT; result -------- t (1 row) ``` - 当设置了区分大小写的字符集字符序时，正则匹配也会区分大小写。 示例： ``` gaussdb=# SELECT 'abc' REGEXP 'ABC' COLLATE utf8mb4_bin AS RESULT; result -------- f (1 row) ``` - 入参Expr或pat为NULL时，返回空值。 ``` gaussdb=# SELECT NULL REGEXP '*' AS RESULT; result -------- (1 row) gaussdb=# SELECT '-' REGEXP NULL AS RESULT; result -------- (1 row) ``` ![](https://support.huaweicloud.com/distributed-devg-v8-gaussdb/public_sys-resources/note_3.0-zh-cn.png) 此操作符仅在MYSQL模式数据库下支持，仅在sql_compatibility='MYSQL'有效，设置参数b_format_version='5.7'。此时REGEXP操作符与\~\*操作符等效，NOT REGEXP操作符与!\~\*操作符等效。 - 当设置b_format_dev_version为s2时，默认开启standard_conforming_strings=off、escape_string_warning=off，此时在文串常量中写的任何反斜线都需要被重复书写。因此，写一个匹配单个反斜线的模式，实际上要在语句里写四个反斜线。 - 此操作符仅支持字符串类型、文本类型、以及可以隐式转换成字符串类型和文本类型的其他数据类型，不支持bytea类型。