# 模式匹配操作符 数据库提供了\[NOT\] LIKE操作符和正则表达式模式匹配。 #### \[NOT\] LIKE 描述：判断字符串是否能匹配上LIKE后的模式字符串。如果字符串与提供的模式匹配，则LIKE表达式返回为真（NOT LIKE表达式返回假），否则返回为假（NOT LIKE表达式返回真）。 匹配规则： 1. 此操作符只有在模式匹配整个串的时候才能成功。如果要匹配在串内任何位置的序列，该模式必须以百分号开头和结尾。 2. 下划线 _代表匹配任何单个字符； 百分号%代表任意串的通配符。 3. 要匹配文本里的下划线或者百分号，在提供的模式里相应字符必须前导逃逸字符。逃逸字符的作用是禁用元字符的特殊含义，缺省的逃逸字符是反斜线，也可以用ESCAPE子句指定一个不同的逃逸字符。 4. 要匹配逃逸字符本身，写两个逃逸字符。例如要写一个包含反斜线的模式常量，需要在SQL语句里写两个反斜线。 5. 是否区分大小写，与字符序有关，比如，默认的utf8mb4_general_ci不区分大小写。 6. 操作符\~\~等效于LIKE，!\~\~等效于NOT LIKE。 示例： - 一般字符串匹配，字符序为utf8mb4_general_ci，不区分大小写。 ``` m_db=# CREATE TABLE t ( COL1 TEXT, COL2 TEXT, COL3 TEXT, COL4 TEXT, COL5 TEXT, COL6 TEXT ); m_db=# INSERT INTO t VALUES ('ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF','ab^CD&eF'); m_db=# SELECT COL1 FROM t WHERE COL1 LIKE '%B^c%'; COL1 ---------- ab^CD&eF (1 row) m_db=# DROP TABLE t; ``` - BINARY/BLOB相关字段，字符序为utf8mb4_bin，区分大小写。 ``` m_db=# CREATE TABLE t ( COL1 BLOB, COL2 BLOB ) DISTRIBUTE BY REPLICATION; m_db=# INSERT INTO t VALUES ('ab^CD&eF', 'ab^CD&eF'); m_db=# SELECT COL1 FROM t WHERE COL1 LIKE 'ab%'; COL1 ---------- ab^CD&eF (1 row) m_db=# DROP TABLE t; ``` - 用ESCAPE字符匹配_及%。 ``` m_db=# CREATE TABLE t ( COL1 TEXT, COL2 TEXT, COL3 TEXT, COL4 TEXT, COL5 TEXT, COL6 TEXT ); m_db=# INSERT INTO t VALUES ('ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF'); m_db=# SELECT COL1 FROM t WHERE COL1 LIKE '%B\_c%'; COL1 ---------- ab_CD%eF (1 row) m_db=# DROP TABLE t; ``` - 指定ESCAPE字符。 ``` m_db=# CREATE TABLE t ( COL1 TEXT, COL2 TEXT, COL3 TEXT, COL4 TEXT, COL5 TEXT, COL6 TEXT ); m_db=# INSERT INTO t VALUES ('ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF','ab_CD%eF'); m_db=# SELECT COL1 FROM t WHERE COL1 LIKE '%B^_c%' ESCAPE '^'; COL1 ---------- ab_CD%eF (1 row) m_db=# DROP TABLE t; ``` #### \[NOT\] REGEXP 描述：REGEXP操作符用于正则表达式匹配。根据自己的模式是否匹配给定字符串而返回真或者假，如[表1]所示。 表1正则表达式操作符 | 操作符名称 | 描述 | 语法说明 | |:---|:---|:---| | REGEXP | 字符串是否与正则表达式匹配。 | expr REGEXP pat | | NOT REGEXP | 字符串是否与正则表达式不匹配，REGEXP的否定。 | expr NOT REGEXP pat | 正则表达式语法： - \^：匹配字符串的开头。 - $：匹配字符串的末尾。 - .：匹配任意字符（包括回车和换行符）。 - a\*：匹配零个或多个"a"字符。 - a+：匹配一个或多个"a"字符。 - a?：匹配零个或一个"a"字符。 - de \| abc：匹配"de"字符或"abc"字符。 - (abc)\*：匹配零个或多个"abc"字符。 - a{1}，a{2,3}：a{1}表示重复"a"字符一次；a{2,3}表示重复"a"2\~3次。 - \[a-dX\]，\[\^a-dX\]：范围匹配。\[a-dX\]匹配a、b、c、d或X的字符。\[\^a-dX\]表示匹配不是a、b、c、d或X的字符。 - \[.characters.\]：匹配用于校对元素的字符序列。允许的字符名称以及匹配的字符如[表2]所示。 表2字符名称及匹配的字符 | Name | Character | Name | Character | |:---|:---|:---|:---| | NUL | 0 | SOH | 1 | | STX | 2 | ETX | 3 | | EOT | 4 | ENQ | 5 | | ACK | 6 | BEL | 7 | | alert | 7 | BS | 10 | | backspace | \\b' | HT | 11 | | tab | \\t' | LF | 12 | | newline | \\n' | VT | 13 | | vertical-tab | \\v' | FF | 14 | | form-feed | \\f' | CR | 15 | | carriage-return | \\r' | SO | 16 | | SI | 17 | DLE | 20 | | DC1 | 21 | DC2 | 22 | | DC3 | 23 | DC4 | 24 | | NAK | 25 | SYN | 26 | | ETB | 27 | CAN | 30 | | EM | 31 | SUB | 32 | | ESC | 33 | IS4 | 34 | | FS | 34 | IS3 | 35 | | GS | 35 | IS2 | 36 | | RS | 36 | IS1 | 37 | | US | 37 | space | ' | | exclamation-mark | !' | quotation-mark | "' | | number-sign | #' | dollar-sign | $' | | percent-sign | %' | ampersand | \&' | | apostrophe | \\'' | left-parenthesis | (' | | right-parenthesis | )' | asterisk | \*' | | plus-sign | +' | comma | ,' | | hyphen | -' | hyphen-minus | -' | | period | .' | full-stop | .' | | slash | /' | solidus | /' | | zero | 0' | one | 1' | | two | 2' | three | 3' | | four | 4' | five | 5' | | six | 6' | seven | 7' | | eight | 8' | nine | 9' | | colon | :' | semicolon | ;' | | less-than-sign | \<' | equals-sign | =' | | greater-than-sign | \>' | question-mark | ?' | | commercial-at | @' | left-square-bracket | \[' | | backslash | \\\\' | reverse-solidus | \\\\' | | right-square-bracket | \]' | circumflex | \^' | | circumflex-accent | \^' | underscore | _' | | low-line | _' | grave-accent | \`' | | left-brace | {' | left-curly-bracket | {' | | vertical-line | \|' | right-brace | }' | | right-curly-bracket | }' | tilde | \~' | | DEL | 177 | - | - | - \[=character_class=\]：表示等价类。匹配具有相同排序校对值的所有字符，包括其本身。例如，如果o和(+)是等同类的成员，那么\[\[=o=\]\]、\[\[=(+)=\]\]和\[o(+)\]是同义词。 - \[:character_class:\]：表示匹配属于该类的所有字符的字符类，如[表3]所示。 表3标准的类名介绍 | 字符类名 | 描述 | 字符范围 | |:---|:---|:---| | 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\] | - \[\[:\<:\]\]，\[\[:\>:\]\]：分别匹配字符串的开始和结束。 示例： ``` m_db=# SELECT 'fo\nfo' REGEXP '^fo$'; ?column? ---------- f (1 row) m_db=# SELECT 'fofo' REGEXP '^fo'; ?column? ---------- t (1 row) m_db=# SELECT 'Ban' REGEXP '^Ba*n'; ?column? ---------- t (1 row) m_db=# SELECT 'Bn' REGEXP '^Ba?n'; ?column? ---------- t (1 row) m_db=# SELECT 'pi' REGEXP 'pi|apa'; ?column? ---------- t (1 row) m_db=# SELECT 'aXbc' NOT REGEXP '^[a-dXYZ]$'; ?column? ---------- t (1 row) ``` 当m_format_behavior_compat_options开启enable_escape_string配置开关，支持的转义字符匹配如[表4]所示。 表4转义字符 | 转义字符 | 描述 | |:---|:---| | \\' | 一个单引号字符。 | | \\" | 一个双引号字符。 | | \\b | 一个退格符。 | | \\n | 一个换行符。 | | \\r | 一个回车符。 | | \\t | 一个制表符。 | | \\Z | ASCII 26（Control Z）。 | | \\\\ | 一个反斜杠符。 | | \\% | 一个%符。 | | \\_ | 一个_符。 | | \\0 | GaussDB报错：invalid byte sequence for encoding "UTF8": 0x00。 | 当设置standard_conforming_strings='off', escape_string_warning='off', backslash_quote='on'，支持的转义字符匹配如[表5]所示。 表5转义字符 | 转义字符 | 描述 | |:---|:---| | \\' | 一个单引号字符。 | | \\b | 退格键。 | | \\f | 换页符，如C语言。 | | \\n | 换行符，如C语言。 | | \\r | 回车符，如C语言。 | | \\t | 水平制表符，如C语言 | | \\uwxyz | （其中wxyz 是四个十六进制数字）十六进制值为0xwxyz的字符。 | | \\xhhh | （其中hhh是十六进制数字的任何序列）十六进制值为0xhhh的字符。 | | \\0 | GaussDB报错：invalid byte sequence for encoding "UTF8": 0x00。 | | \\xy | （其中xy是两个八进制数字）八进制值为0xy的字符。 | | \\xyz | （其中xyz是三个八进制数字）八进制值为0xyz的字符。 | ![](https://support.huaweicloud.com/distributed-m-comp-devg-v8-gaussdb/public_sys-resources/notice_3.0-zh-cn.png) 词法语法解析按照字节流解析，当多字节字符中包含与\\、\\'、\\\\等符号一致的编码时，会导致与MySQL行为不一致，建议暂时关闭转义符开关进行规避