MySQL8-中文参考-四十八-

布客飞龙
31 阅读58分钟

MySQL8 中文参考（四十八）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

14.8 字符串函数和运算符

原文：dev.mysql.com/doc/refman/8.0/en/string-functions.html

14.8.1 字符串比较函数和运算符

14.8.2 正则表达式

14.8.3 函数结果的字符集和排序规则

表 14.12 字符串函数和运算符

名称描述
ASCII()返回最左字符的数值
BIN()返回包含数字二进制表示的字符串
BIT_LENGTH()返回参数的位长度
CHAR()返回每个传递整数的字符
CHAR_LENGTH()返回参数中的字符数
CHARACTER_LENGTH()CHAR_LENGTH()的同义词
CONCAT()返回连接的字符串
CONCAT_WS()返回带有分隔符的连接字符串
ELT()返回索引号处的字符串
EXPORT_SET()返回一个字符串，对于值位设置的每个位，您会得到一个打开的字符串，对于未设置的位，您会得到一个关闭的字符串
FIELD()第一个参数在后续参数中的索引（位置）
FIND_IN_SET()第一个参数在第二个参数中的索引（位置）
FORMAT()返回格式化为指定小数位数的数字
FROM_BASE64()解码 base64 编码的字符串并返回结果
HEX()十进制或字符串值的十六进制表示
INSERT()在指定位置插入子字符串，最多指定数量的字符
INSTR()返回子字符串的第一个出现的索引
LCASE()LOWER()的同义词
LEFT()返回指定数量的最左字符
LENGTH()返回字符串的字节长度
LIKE简单的模式匹配
LOAD_FILE()加载指定文件
LOCATE()返回子字符串的第一个出现位置
LOWER()返回小写参数
LPAD()返回左侧填充了指定字符串的字符串参数
LTRIM()移除前导空格
MAKE_SET()返回一组逗号分隔的字符串，其中对应位在 bits 中设置
MATCH()执行全文搜索
MID()返回从指定位置开始的子字符串
NOT LIKE简单模式匹配的否定
NOT REGEXPREGEXP 的否定
OCT()返回包含数字的八进制表示的字符串
OCTET_LENGTH()LENGTH() 的同义词
ORD()返回参数的最左字符的字符代码
POSITION()LOCATE() 的同义词
QUOTE()为在 SQL 语句中使用而转义参数
REGEXP字符串是否匹配正则表达式
REGEXP_INSTR()返回匹配正则表达式的子字符串的起始索引
REGEXP_LIKE()字符串是否匹配正则表达式
REGEXP_REPLACE()替换与正则表达式匹配的子字符串
REGEXP_SUBSTR()返回匹配正则表达式的子字符串
REPEAT()重复指定次数的字符串
REPLACE()替换指定字符串的出现次数
REVERSE()反转字符串中的字符
RIGHT()返回指定右侧字符数
RLIKE字符串是否匹配正则表达式
RPAD()追加指定次数的字符串
RTRIM()移除尾部空格
SOUNDEX()返回一个 Soundex 字符串
SOUNDS LIKE比较声音
SPACE()返回指定数量的空格字符串
STRCMP()比较两个字符串
SUBSTR()返回指定的子字符串
SUBSTRING()返回指定的子字符串
SUBSTRING_INDEX()返回指定分隔符之前的字符串子串
TO_BASE64()返回转换为 base-64 字符串的参数
TRIM()删除前导和尾随空格
UCASE()UPPER()的同义词
UNHEX()返回包含数字的十六进制表示的字符串
UPPER()转换为大写
WEIGHT_STRING()返回字符串的权重字符串
名称描述

如果结果的长度大于max_allowed_packet系统变量的值，则返回NULL。有关详细信息，请参阅第 7.1.1 节，“配置服务器”。

对于操作字符串位置的函数，第一个位置编号为 1。

对于需要长度参数的函数，非整数参数将四舍五入为最接近的整数。

  • ASCII(*str*)

    返回字符串*str的最左字符的数值。如果str为空字符串，则返回0。如果str*为NULL，则返回NULL。ASCII()适用于 8 位字符。

    mysql> SELECT ASCII('2');
 -> 50
mysql> SELECT ASCII(2);
 -> 50
mysql> SELECT ASCII('dx');
 -> 100

    另请参见ORD()函数。

  • BIN(*N*)

    返回*N的二进制值的字符串表示，其中N是长整型(BIGINT)数字。这等效于CONV(*N*,10,2)。如果N*为NULL，则返回NULL。

    mysql> SELECT BIN(12);
 -> '1100'

  • BIT_LENGTH(*str*)

    返回字符串*str的位数。如果str*为NULL，则返回NULL。

    mysql> SELECT BIT_LENGTH('text');
 -> 32

  • CHAR(*N*,... [USING *charset_name*])

    CHAR()将每个参数*N*解释为整数，并返回由这些整数的代码值给出的字符组成的字符串。跳过NULL值。

    mysql> SELECT CHAR(77,121,83,81,'76');
+--------------------------------------------------+
| CHAR(77,121,83,81,'76')                          |
+--------------------------------------------------+
| 0x4D7953514C                                     |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(77,77.3,'77.3');
+--------------------------------------------+
| CHAR(77,77.3,'77.3')                       |
+--------------------------------------------+
| 0x4D4D4D                                   |
+--------------------------------------------+
1 row in set (0.00 sec)

    默认情况下，CHAR()返回一个二进制字符串。要生成给定字符集中的字符串，请使用可选的USING子句：

    mysql> SELECT CHAR(77,121,83,81,'76' USING utf8mb4);
+---------------------------------------+
| CHAR(77,121,83,81,'76' USING utf8mb4) |
+---------------------------------------+
| MySQL                                 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(77,77.3,'77.3' USING utf8mb4);
+------------------------------------+
| CHAR(77,77.3,'77.3' USING utf8mb4) |
+------------------------------------+
| MMM                                |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '77.3' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

    如果提供了USING并且结果字符串对于给定的字符集是非法的，则会发出警告。此外，如果启用了严格的 SQL 模式，则CHAR()的结果将变为NULL。

    如果从mysql客户端内调用CHAR()，二进制字符串将根据--binary-as-hex的值以十六进制表示。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。

    大于 255 的CHAR()参数将转换为多个结果字节。例如，CHAR(256)等同于CHAR(1,0)，而CHAR(256*256)等同于CHAR(1,0,0)：

    mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000           | 010000             |
+------------------+--------------------+
1 row in set (0.00 sec)

  • CHAR_LENGTH(*str*)

    返回*str*的字符串长度，以代码点计算。多字节字符计为一个代码点。这意味着，对于包含两个 3 字节字符的字符串，LENGTH()返回6，而CHAR_LENGTH()返回2，如下所示：

    mysql> SET @dolphin:='海豚';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+------------------+-----------------------+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+------------------+-----------------------+
|                6 |                     2 |
+------------------+-----------------------+
1 row in set (0.00 sec)

    如果*str*为NULL，则CHAR_LENGTH()返回NULL。

  • CHARACTER_LENGTH(*str*)

    CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。

  • CONCAT(*str1*,*str2*,...)

    返回从连接参数结果的字符串。可能有一个或多个参数。如果所有参数都是非二进制字符串，则结果是非二进制字符串。如果参数包含任何二进制字符串，则结果是二进制字符串。数值参数转换为其等效的非二进制字符串形式。

    如果任何参数为NULL，CONCAT()返回NULL。

    mysql> SELECT CONCAT('My', 'S', 'QL');
 -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
 -> NULL
mysql> SELECT CONCAT(14.3);
 -> '14.3'

    对于带引号的字符串，可以通过将字符串放在一起来执行连接：

    mysql> SELECT 'My' 'S' 'QL';
 -> 'MySQL'

    如果从mysql客户端内调用CONCAT()，二进制字符串结果将根据--binary-as-hex的值以十六进制表示。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。

  • CONCAT_WS(*分隔符*,*str1*,*str2*,...)

    CONCAT_WS()代表带分隔符连接，是CONCAT()的特殊形式。第一个参数是其余参数的分隔符。分隔符添加在要连接的字符串之间。分隔符可以是字符串，其余参数也可以是字符串。如果分隔符为NULL，则结果为NULL。

    mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
 -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
 -> 'First name,Last Name'

    CONCAT_WS()不会跳过空字符串。但是，在分隔符参数之后，它会跳过任何NULL值。

  • ELT(*N*,*str1*,*str2*,*str3*,...)

    ELT()返回字符串列表中第*N个元素：如果N* = 1，则返回*str1，如果N* = 2，则返回*str2，依此类推。如果N*小于1，大于参数数量或为NULL，则返回NULL。ELT()是FIELD()的补充。

    mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
 -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
 -> 'Dd'

  • EXPORT_SET(*bits*,*on*,*off*[,*separator*[,*number_of_bits*]])

    返回一个字符串，对于值*bits中设置的每个位，您会得到一个on字符串，对于值中未设置的每个位，您会得到一个off字符串。从右到左（从低位到高位）检查bits中的位。字符串从左到右添加到结果中，由separator字符串分隔（默认为逗号字符,）。检查的位数由number_of_bits给出，如果未指定，默认为64。如果number_of_bits*大于64，则会被静默截断为64。它被视为无符号整数，因此值为−1 实际上等同于64。

    mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
 -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
 -> '0,1,1,0,0,0,0,0,0,0'

  • FIELD(*str*,*str1*,*str2*,*str3*,...)

    返回*str在str1、str2、str3、...列表中的索引（位置）。如果未找到str*，则返回0。

    如果FIELD()的所有参数都是字符串，则所有参数将作为字符串进行比较。如果所有参数都是数字，则它们将作为数字进行比较。否则，参数将作为双精度数进行比较。

    如果*str*为NULL，则返回值为0，因为NULL与任何值的相等比较失败。FIELD()是ELT()的补充。

    mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
 -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
 -> 0

  • FIND_IN_SET(*str*,*strlist*)

    如果字符串*str在由N个子字符串组成的字符串列表strlist中，则返回 1 到N的值。字符串列表是由,字符分隔的子字符串组成的字符串。如果第一个参数是常量字符串，第二个参数是SET类型的列，则FIND_IN_SET()函数会优化使用位运算。如果str不在strlist中或strlist*为空字符串，则返回0。如果任一参数为NULL，则返回NULL。如果第一个参数包含逗号（,）字符，则此函数无法正常工作。

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
 -> 2

  • FORMAT(*X*,*D*[,*locale*])

    将数字*X格式化为类似'#,###,###.##'的格式，四舍五入到D位小数，并将结果作为字符串返回。如果D为0，则结果没有小数点或小数部分。如果X或D*为NULL，则函数返回NULL。

    可选的第三个参数允许指定要用于结果数字的小数点、千位分隔符和分隔符之间的分组的区域设置。允许的区域设置值与lc_time_names系统变量的合法值相同（请参阅第 12.16 节，“MySQL 服务器区域设置支持”）。如果区域设置为NULL或未指定，则默认区域设置为'en_US'。

    mysql> SELECT FORMAT(12332.123456, 4);
 -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
 -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
 -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
 -> '12.332,20'

  • FROM_BASE64(*str*)

    将一个使用TO_BASE64()使用的 base-64 编码规则编码的字符串解码，并将解码结果作为二进制字符串返回。如果参数为NULL或不是有效的 base-64 字符串，则结果为NULL。有关编码和解码规则的详细信息，请参阅TO_BASE64()的描述。

    mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
 -> 'JWJj', 'abc'

    如果在mysql客户端内部调用FROM_BASE64()，二进制字符串将使用十六进制表示。您可以通过在启动mysql客户端时将--binary-as-hex的值设置为0来禁用此行为。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — The MySQL Command-Line Client”。

  • HEX(*str*), HEX(*N*)

    对于字符串参数*str，HEX() 返回一个十六进制字符串表示str，其中str*中每个字符的每个字节都转换为两个十六进制数字。（因此，多字节字符会变成两个以上的数字。）此操作的逆操作由UNHEX() 函数执行。

    对于数值参数*N，HEX() 返回将N*的值视为长整型（BIGINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")）数字的十六进制字符串表示。这等效于CONV(*N*,10,16)。此操作的逆操作由CONV(HEX(*N*),16,10)执行。

    对于NULL参数，此函数返回NULL。

    mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
 -> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
 -> 'FF', 255

  • INSERT(*str*,*pos*,*len*,*newstr*)

    返回字符串*str，从位置pos开始的子字符串，长度为len个字符，被字符串newstr替换。如果pos不在字符串长度范围内，则返回原始字符串。如果len不在剩余字符串的长度范围内，则从位置pos*开始替换剩余字符串。如果任何参数为NULL，则返回NULL。

    mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
 -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
 -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
 -> 'QuWhat'

    此函数支持多字节字符。

  • INSTR(*str*,*substr*)

    返回字符串*str中子字符串substr*第一次出现的位置。这与LOCATE()的两参数形式相同，只是参数的顺序相反。

    mysql> SELECT INSTR('foobarbar', 'bar');
 -> 4
mysql> SELECT INSTR('xbar', 'foobar');
 -> 0

    此函数支持多字节字符，仅在至少一个参数为二进制字符串时区分大小写。如果任一参数为NULL，此函数返回NULL。

  • LCASE(*str*)

    LCASE() 是 LOWER() 的同义词。

    在视图中使用的LCASE()在存储视图定义时会被重写为LOWER()。（Bug #12844279）

  • LEFT(*str*,*len*)

    返回字符串*str中最左边的len*个字符，如果任何参数为NULL，则返回NULL。

    mysql> SELECT LEFT('foobarbar', 5);
 -> 'fooba'

    此函数支持多字节字符。

  • LENGTH(*str*)

    返回字符串*str的长度，以字节为单位。多字节字符计为多个字节。这意味着对于包含五个 2 字节字符的字符串，LENGTH() 返回10，而CHAR_LENGTH() 返回5。如果str*为NULL，则返回NULL。

    mysql> SELECT LENGTH('text');
 -> 4

    注意

    Length() OpenGIS 空间函数在 MySQL 中被命名为ST_Length()。

  • LOAD_FILE(*file_name*)

    读取文件并将文件内容作为字符串返回。要使用此函数，文件必须位于服务器主机上，必须指定文件的完整路径名，并且必须具有FILE权限。文件必须可被服务器读取，且其大小必须小于max_allowed_packet字节。如果secure_file_priv系统变量设置为非空目录名称，则要加载的文件必须位于该目录中。（在 MySQL 8.0.17 之前，文件必须可被所有人读取，而不仅仅是服务器可读取。）

    如果文件不存在或由于不满足前述条件之一而无法读取，则函数返回NULL。

    character_set_filesystem系统变量控制给定为文字字符串的文件名的解释。

    mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

  • LOCATE(*substr*,*str*), LOCATE(*substr*,*str*,*pos*)

    第一个语法返回子字符串*substr在字符串str中第一次出现的位置。第二个语法返回子字符串substr在字符串str中从位置pos开始第一次出现的位置。如果str中没有substr*，则返回0。如果任何参数为NULL，则返回NULL。

    mysql> SELECT LOCATE('bar', 'foobarbar');
 -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
 -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
 -> 7

    此函数是多字节安全的，仅在至少一个参数为二进制字符串时区分大小写。

  • LOWER(*str*)

    返回根据当前字符集映射将所有字符更改为小写的字符串*str，如果str*为NULL，则返回NULL。默认字符集为utf8mb4。

    mysql> SELECT LOWER('QUADRATICALLY');
 -> 'quadratically'

    当应用于二进制字符串（BINARY, VARBINARY, BLOB）时，LOWER()（和UPPER()）是无效的。要对二进制字符串执行大小写转换，首先使用适合存储在字符串中的数据的字符集将其转换为非二进制字符串：

    mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+-------------+------------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
+-------------+------------------------------------+
| New York    | new york                           |
+-------------+------------------------------------+

    对于 Unicode 字符集的排序规则，如果在排序规则名称中指定了 Unicode 排序算法（UCA）版本，则LOWER()和UPPER()将按照该版本的 UCA 工作，如果未指定版本，则按照 UCA 4.0.0 工作。例如，utf8mb4_0900_ai_ci和utf8mb3_unicode_520_ci分别按照 UCA 9.0.0 和 5.2.0 工作，而utf8mb3_unicode_ci按照 UCA 4.0.0 工作。参见第 12.10.1 节，“Unicode 字符集”。

    此函数是多字节安全的。

    在视图中使用的LCASE()被重写为LOWER()。

  • LPAD(*str*,*len*,*padstr*)

    返回左填充了字符串*padstr到长度为len字符的字符串str。如果str比len长，则返回值被缩短为len*个字符。

    mysql> SELECT LPAD('hi',4,'??');
 -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
 -> 'h'

    如果其任何参数为NULL，则返回NULL。

  • LTRIM(*str*)

    返回去除前导空格字符的字符串*str。如果str*为NULL，则返回NULL。

    mysql> SELECT LTRIM('  barbar');
 -> 'barbar'

    此函数是多字节安全的。

  • MAKE_SET(*bits*,*str1*,*str2*,...)

    返回一个集合值（一个包含由,字符分隔的子字符串的字符串），其中包含具有对应位在*bits中设置的字符串。str1对应于位 0，str2对应于位 1，依此类推。str1、str2*、...中的NULL值不会附加到结果中。

    mysql> SELECT MAKE_SET(1,'a','b','c');
 -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
 -> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
 -> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
 -> ''

  • MID(*str*,*pos*,*len*)

    MID(*str*,*pos*,*len*)是SUBSTRING(*str*,*pos*,*len*)的同义词。

  • OCT(*N*)

    返回*N的八进制值的字符串表示，其中N是一个长长整型(BIGINT)数字。这等同于CONV(*N*,10,8)。如果N*为NULL，则返回NULL。

    mysql> SELECT OCT(12);
 -> '14'

  • OCTET_LENGTH(*str*)

    OCTET_LENGTH()是LENGTH()的同义词。

  • ORD(*str*)

    如果字符串*str*的最左边字符是一个多字节字符，则返回该字符的代码，使用以下公式从其组成字节的数值计算：

     (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256²) ...

    如果最左边的字符不是一个多字节字符，ORD()返回与ASCII()函数相同的值。如果*str*为NULL，则函数返回NULL。

    mysql> SELECT ORD('2');
 -> 50

  • POSITION(*substr* IN *str*)

    POSITION(*substr* IN *str*)是LOCATE(*substr*,*str*)的同义词。

  • QUOTE(*str*)

    引用一个字符串以生成一个结果，该结果可以作为 SQL 语句中的正确转义数据值使用。返回的字符串用单引号括起来，并且每个反斜杠(\)、单引号(')、ASCII NUL和 Control+Z 的实例前面都有一个反斜杠。如果参数为NULL，返回值是不用单引号括起来的单词NULL。

    mysql> SELECT QUOTE('Don\'t!');
 -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
 -> NULL

    有关比较，请参阅第 11.1.1 节，“字符串文字”中的文字引用规则以及 C API 中的 mysql_real_escape_string_quote()。

  • REPEAT(*str*,*count*)

    返回由字符串*str重复count次组成的字符串。如果count小于 1，则返回空字符串。如果str或count*是NULL，则返回NULL。

    mysql> SELECT REPEAT('MySQL', 3);
 -> 'MySQLMySQLMySQL'

  • REPLACE(*str*,*from_str*,*to_str*)

    返回将所有出现的字符串*from_str替换为字符串to_str的字符串str。在搜索from_str*时，REPLACE()执行区分大小写的匹配。

    mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
 -> 'WwWwWw.mysql.com'

    这个函数是多字节安全的。如果任何参数是NULL，则返回NULL。

  • REVERSE(*str*)

    返回字符串*str的字符顺序颠倒，如果str*是NULL，则返回NULL。

    mysql> SELECT REVERSE('abc');
 -> 'cba'

    这个函数是多字节安全的。

  • RIGHT(*str*,*len*)

    返回字符串*str中最右边的len*个字符，如果任何参数���NULL，则返回NULL。

    mysql> SELECT RIGHT('foobarbar', 4);
 -> 'rbar'

    这个函数是多字节安全的。

  • RPAD(*str*,*len*,*padstr*)

    返回字符串*str，右侧用字符串padstr填充到长度为len个字符。如果str比len长，则返回值被缩短为len个字符。如果str、padstr或len*是NULL，则函数返回NULL。

    mysql> SELECT RPAD('hi',5,'?');
 -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
 -> 'h'

    这个函数是多字节安全的。

  • RTRIM(*str*)

    返回删除尾随空格字符的字符串*str*。

    mysql> SELECT RTRIM('barbar   ');
 -> 'barbar'

    这个函数是多字节安全的，如果*str*是NULL，则返回NULL。

  • SOUNDEX(*str*)

    从*str返回一个 soundex 字符串，如果str是NULL，则返回NULL。两个听起来几乎相同的字符串应该具有相同的 soundex 字符串。标准的 soundex 字符串长度为四个字符，但SOUNDEX()函数返回一个任意长的字符串。您可以对结果使用SUBSTRING()来获取标准的 soundex 字符串。str*中的所有非字母字符都将被忽略。所有 A-Z 范围之外的国际字母字符都被视为元音。

    重要

    在使用SOUNDEX()时，您应该注意以下限制：

    • 此函数目前的实现仅适用于英语语言的字符串。其他语言的字符串可能产生不可靠的结果。

    • 不能保证此函数与使用多字节字符集（包括utf-8）的字符串提供一致的结果。有关更多信息，请参见 Bug #22638。

    mysql> SELECT SOUNDEX('Hello');
 -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
 -> 'Q36324'

    注意

    此函数实现原始 Soundex 算法，而不是更流行的增强版本（也由 D. Knuth 描述）。区别在于原始版本首先丢弃元音，其次是重复字符，而增强版本首先丢弃重复字符，其次是元音。

  • *expr1* SOUNDS LIKE *expr2*

    这与SOUNDEX(*expr1*) = SOUNDEX(*expr2*)相同。

  • SPACE(*N*)

    返回由*N个空格字符组成的字符串，如果N*为NULL，则返回NULL。

    mysql> SELECT SPACE(6);
 -> '      '

  • SUBSTR(*str*,*pos*), SUBSTR(*str* FROM *pos*), SUBSTR(*str*,*pos*,*len*), SUBSTR(*str* FROM *pos* FOR *len*)

    SUBSTR()是SUBSTRING()的同义词。

  • SUBSTRING(*str*,*pos*), SUBSTRING(*str* FROM *pos*), SUBSTRING(*str*,*pos*,*len*), SUBSTRING(*str* FROM *pos* FOR *len*)

    没有*len参数的形式返回从位置pos开始的字符串str的子字符串。有len参数的形式返回从位置pos开始，长度为len的子字符串。使用FROM的形式是标准的 SQL 语法。也可以对pos使用负值。在这种情况下，子字符串的开头是距离字符串末尾pos个字符，而不是距离开头。在这个函数的任何形式中，pos都可以使用负值。pos*的值为 0 时返回空字符串。

    对于SUBSTRING()的所有形式，要提取子字符串的字符串中第一个字符的位置被认为是1。

    mysql> SELECT SUBSTRING('Quadratically',5);
 -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
 -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
 -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
 -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
 -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
 -> 'ki'

    此函数是多字节安全的。如果其任何参数为NULL，则返回NULL。

    如果*len*小于 1，则结果为空字符串。

  • SUBSTRING_INDEX(*str*,*delim*,*count*)

    返回字符串*str中在定界符delim出现count次之前的子字符串。如果count为正数，则返回最终定界符（从左边计数）左侧的所有内容。如果count为负数，则返回最终定界符（从右边计数）右侧的所有内容。在搜索delim*时，SUBSTRING_INDEX()执行区分大小写的匹配。

    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
 -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
 -> 'mysql.com'

    此函数是多字节安全的。

    如果SUBSTRING_INDEX()的任何参数为NULL，则返回NULL。

  • TO_BASE64(*str*)

    将字符串参数转换为 base-64 编码形式，并将结果作为具有连接字符集和排序规则的字符字符串返回。如果参数不是字符串，则在进行转换之前将其转换为字符串。如果参数为NULL，则结果为NULL。可以使用FROM_BASE64()函数解码 base-64 编码的字符串。

    mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
 -> 'JWJj', 'abc'

    存在不同的 base-64 编码方案。这些是TO_BASE64()和FROM_BASE64()使用的编码和解码规则：

    • 字母值为 62 的编码为'+'。

    • 字母值为 63 的编码为'/'。

    • 编码输出由 4 个可打印字符组成。每 3 个字节的输入数据使用 4 个字符进行编码。如果最后一组不完整，则使用'='字符填充至长度为 4。

    • 每 76 个编码输出字符后添加一个换行符，将长输出分成多行。

    • 解码识别并忽略换行符、回车符、制表符和空格。

  • TRIM([{BOTH | LEADING | TRAILING} [*remstr*] FROM] *str*), TRIM([*remstr* FROM] *str*)

    返回去除所有*remstr前缀或后缀的字符串str。如果未给出BOTH、LEADING或TRAILING中的任何一个标识符，则假定为BOTH。remstr*是可选的，如果未指定，则删除空格。

    mysql> SELECT TRIM('  bar   ');
 -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
 -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
 -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
 -> 'barx'

    此函数是多字节安全的。如果其任何参数为NULL，则返回NULL。

  • UCASE(*str*)

    UCASE()是UPPER()的同义词。

    在视图中使用的UCASE()会被重写为UPPER()。

  • UNHEX(*str*)

    对于字符串参数*str*，UNHEX(*str*)将参数中的每对字符解释为十六进制数，并将其转换为由该数字表示的字节。返回值是一个二进制字符串。

    mysql> SELECT UNHEX('4D7953514C');
 -> 'MySQL'
mysql> SELECT X'4D7953514C';
 -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
 -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
 -> '1267'

    参数字符串中的字符必须是合法的十六进制数字：'0' .. '9'，'A' .. 'F'，'a' .. 'f'。如果参数包含任何非十六进制数字，或者本身为NULL，则结果为NULL：

    mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+

mysql> SELECT UNHEX(NULL);
+-------------+
| UNHEX(NULL) |
+-------------+
| NULL        |
+-------------+

    如果UNHEX()的参数是BINARY列，则还可能出现NULL结果，因为存储时值会使用0x00字节进行填充，但在检索时这些字节不会被去除。例如，'41'被存储到CHAR(3)列中为'41 '，检索时为'41'（去除了尾随填充空格），因此列值的UNHEX()返回X'41'。相比之下，'41'被存储到BINARY(3)列中为'41\0'，检索时为'41\0'（尾随填充0x00字节未被去除）。'\0'不是合法的十六进制数字，因此列值的UNHEX()返回NULL。

    对于数值参数*N*，UNHEX()不执行HEX(*N*)的逆操作。请改用CONV(HEX(*N*),16,10)。请参阅HEX()的描述。

    如果在mysql客户端中调用UNHEX()，二进制字符串将以十六进制表示形式显示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。

  • UPPER(*str*)

    返回根据当前字符集映射将所有字符更改为大写的字符串*str，如果str*为NULL则返回NULL。默认字符集为utf8mb4。

    mysql> SELECT UPPER('Hej');
 -> 'HEJ'

    有关如何对二进制字符串（BINARY, VARBINARY, BLOB）执行大小写转换以及有关 Unicode 字符集的大小写折叠的信息，请参阅LOWER()的描述，这些信息也适用于UPPER()。

    此函数支持多字节。

    在视图中使用的UCASE()将被重写为UPPER()。

  • WEIGHT_STRING(*str* [AS {CHAR|BINARY}(*N*)] [*flags*])

    此函数返回输入字符串的权重字符串。返回值是一个二进制字符串，表示字符串的比较和排序值，如果参数为NULL则返回NULL。具有以下属性：

    • 如果 WEIGHT_STRING(*str1*) = WEIGHT_STRING(*str2*)，那么 *str1* = *str2*（str1 和 str2 被视为相等）

    • 如果 WEIGHT_STRING(*str1*) < WEIGHT_STRING(*str2*)，那么 *str1* < *str2*（str1 在 str2 之前排序）

    WEIGHT_STRING() 是一个用于内部调试的函数。其行为可能会在 MySQL 版本之间发生变化而无需通知。它可用于测试和调试排序规则，特别是在添加新的排序规则时。参见 第 12.14 节，“向字符集添加排序规则”。

    这个列表简要总结了参数。更多细节在列表后的讨论中给出。

    • str：输入字符串表达式。

    • AS 子句：可选；将输入字符串转换为给定类型和长度。

    • flags：可选；未使用。

    输入字符串 str 是一个字符串表达式。如果输入是非二进制（字符）字符串，如 CHAR、VARCHAR 或 TEXT 值，则返回值包含字符串的排序规则权重。如果输入是二进制（字节）字符串，如 BINARY、VARBINARY 或 BLOB 值，则返回值与输入相同（二进制字符串中每个字节的权重是字节值）。如果输入为 NULL，WEIGHT_STRING() 返回 NULL。

    示例：

    mysql> SET @s = _utf8mb4 'AB' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 1C471C60               |
+------+---------+------------------------+

    mysql> SET @s = _utf8mb4 'ab' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 1C471C60               |
+------+---------+------------------------+

    mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+

    mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   |
+------+---------+------------------------+

    前面的示例使用 HEX() 来显示 WEIGHT_STRING() 的结果。因为结果是一个二进制值，当结果包含不可打印的值时，HEX() 尤其有用，可以将其显示为可打印形式：

    mysql> SET @s = CONVERT(X'C39F' USING utf8mb4) COLLATE utf8mb4_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               |
+------------------------+

    对于非NULL的返回值，如果值的长度在 VARBINARY 的最大长度内，则值的数据类型为 VARBINARY，否则数据类型为 BLOB。

    AS 子句可以用于将输入字符串转换为非二进制或二进制字符串，并强制其达到给定长度：

    • AS CHAR(*N*) 将字符串转换为非二进制字符串，并在右侧用空格填充到长度为 N 个字符。N 必须至少为 1。如果 N 小于输入字符串的长度，则字符串将被截断为 N 个字符。截断不会发出警告。

    • AS BINARY(*N*) 类似，但将字符串转换为二进制字符串，N 以字节为单位（而非字符），填充使用 0x00 字节（而非空格）。

    mysql> SET NAMES 'latin1';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
mysql> SET NAMES 'utf8mb4';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 1C471C60                            |
+-------------------------------------+

    mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+

    flags 子句目前未被使用。

    如果在mysql客户端中调用WEIGHT_STRING()，二进制字符串将以十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — The MySQL Command-Line Client”。

14.8.1 字符串比较函数和运算符

原文：dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html

表 14.13 字符串比较函数和运算符

名称描述
LIKE简单模式匹配
NOT LIKE简单模式匹配的否定
STRCMP()比较两个字符串

如果将二进制字符串作为参数传递给字符串函数，则结果字符串也是二进制字符串。将数字转换为字符串时被视为二进制字符串。这仅影响比较。

通常，如果字符串比较中的任何表达式是区分大小写的，则比较将以区分大小写的方式执行。

如果在 mysql 客户端中调用字符串函数，则二进制字符串将以十六进制表示形式显示，具体取决于 --binary-as-hex 的值。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。

  • *expr* LIKE *pat* [ESCAPE '*escape_char*']

    使用 SQL 模式进行模式匹配。返回 1（TRUE）或 0（FALSE）。如果 expr 或 pat 中的任一者是 NULL，结果为 NULL。

    模式不一定是一个字面字符串。例如，它可以被指定为一个字符串表达式或表列。在后一种情况下，该列必须被定义为 MySQL 字符串类型之一（参见 第 13.3 节，“字符串数据类型”）。

    根据 SQL 标准，LIKE 按字符进行匹配，因此它可能产生与 = 比较运算符不同的结果：

    mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

    特别是，尾随空格始终是有意义的。这与使用 = 运算符执行的比较不同，对于非二进制字符串（CHAR、VARCHAR 和 TEXT 值）的尾随空格的重要性取决于用于比较的排序规则的填充属性。有关更多信息，请参见 比较中的尾随空格处理。

    使用 LIKE 可以在模式中使用以下两个通配符：

    • % 匹配任意数量的字符，甚至零个字符。

    • _ 匹配正好一个字符。

    mysql> SELECT 'David!' LIKE 'David_';
 -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
 -> 1

    要测试通配符字符的文字实例，需在其前面加上转义字符。如果不指定ESCAPE字符，则假定为\，除非启用了NO_BACKSLASH_ESCAPES SQL 模式。在这种情况下，不使用转义字符。

    • \%匹配一个%字符。

    • \_匹配一个_字符。

    mysql> SELECT 'David!' LIKE 'David\_';
 -> 0
mysql> SELECT 'David_' LIKE 'David\_';
 -> 1

    要指定不同的转义字符，请使用ESCAPE子句：

    mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
 -> 1

    转义序列应为一个字符长，用于指定转义字符，或为空以指定不使用转义字符。表达式必须在执行时评估为常量。如果启用了NO_BACKSLASH_ESCAPES SQL 模式，则序列不能是空的。

    以下语句说明字符串比较不区分大小写，除非操作数之一是区分大小写的（使用区分大小写的排序规则或是二进制字符串）：

    mysql> SELECT 'abc' LIKE 'ABC';
 -> 1
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;
 -> 0
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_bin;
 -> 0
mysql> SELECT 'abc' LIKE BINARY 'ABC';
 -> 0

    作为对标准 SQL 的扩展，MySQL 允许在数值表达式上使用LIKE。

    mysql> SELECT 10 LIKE '1%';
 -> 1

    在这种情况下，MySQL 尝试对表达式进行隐式转换为字符串。参见第 14.3 节，“表达式评估中的类型转换”。

    注意

    MySQL 在字符串中使用 C 转义语法（例如，\n表示换行符）。如果要让LIKE字符串包含文字\，必须将其双写。（除非启用了NO_BACKSLASH_ESCAPES SQL 模式，在这种情况下不使用转义字符。）例如，要搜索\n，请指定为\\n。要搜索\，请指定为\\\\；这是因为解析器会将反斜杠剥离一次，然后在进行模式匹配时再次剥离，留下一个单独的反斜杠进行匹配。

    例外情况：在模式字符串的末尾，可以指定反斜杠为\\。在字符串的末尾，反斜杠代表自身，因为后面没有需要转义的内容。假设一个表包含以下值：

    mysql> SELECT filename FROM t1;
+--------------+
| filename     |
+--------------+
| C:           |
| C:\          |
| C:\Programs  |
| C:\Programs\ |
+--------------+

    要测试以反斜杠结尾的值，可以使用以下任一模式匹配这些值：

    mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+
| filename     | filename LIKE '%\\' |
+--------------+---------------------+
| C:           |                   0 |
| C:\          |                   1 |
| C:\Programs  |                   0 |
| C:\Programs\ |                   1 |
+--------------+---------------------+

mysql> SELECT filename, filename LIKE '%\\\\' FROM t1;
+--------------+-----------------------+
| filename     | filename LIKE '%\\\\' |
+--------------+-----------------------+
| C:           |                     0 |
| C:\          |                     1 |
| C:\Programs  |                     0 |
| C:\Programs\ |                     1 |
+--------------+-----------------------+

  • *expr* NOT LIKE *pat* [ESCAPE '*escape_char*']

    这与NOT (*expr* LIKE *pat* [ESCAPE '*escape_char*'])相同。

    注意

    包含NULL的列进行NOT LIKE比较的聚合查询可能产生意外结果。例如，考虑以下表和数据：

    CREATE TABLE foo (bar VARCHAR(10));

INSERT INTO foo VALUES (NULL), (NULL);

    查询 SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; 返回 0。你可能会认为 SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; 会返回 2。然而，事实并非如此：第二个查询返回 0。这是因为 NULL NOT LIKE *expr* 总是返回 NULL，无论 expr 的值如何。对于涉及 NULL 和使用 NOT RLIKE 或 NOT REGEXP 进行比较的聚合查询，必须显式测试 NOT NULL，使用 OR（而不是 AND），如下所示：

    SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;

  • STRCMP(*expr1*,*expr2*)

    STRCMP() 如果字符串相同则返回 0，如果第一个参数根据当前排序顺序小于第二个参数则返回 -1，如果任一参数为 NULL 则返回 NULL，否则返回 1。

    mysql> SELECT STRCMP('text', 'text2');
 -> -1
mysql> SELECT STRCMP('text2', 'text');
 -> 1
mysql> SELECT STRCMP('text', 'text');
 -> 0

    STRCMP() 使用参数的排序规则执行比较。

    mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|                0 |               -1 |
+------------------+------------------+

    如果排序规则不兼容，则其中一个参数必须转换为与另一个兼容。参见 Section 12.8.4, “Collation Coercibility in Expressions”。

    mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
-->
mysql> SELECT STRCMP(@s1, @s3);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)
and (utf8mb4_0900_as_cs,IMPLICIT) for operation 'strcmp'
mysql> SELECT STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci);
+---------------------------------------------+
| STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

14.8.2 正则表达式

原文：dev.mysql.com/doc/refman/8.0/en/regexp.html

表 14.14 正则表达式函数和运算符

名称描述
NOT REGEXPREGEXP 的否定
REGEXP字符串是否匹配正则表达式
REGEXP_INSTR()返回匹配正则表达式的子字符串的起始索引
REGEXP_LIKE()字符串是否匹配正则表达式
REGEXP_REPLACE()替换匹配正则表达式的子字符串
REGEXP_SUBSTR()返回匹配正则表达式的子字符串
RLIKE字符串是否匹配正则表达式

正则表达式是指定复杂搜索模式的强大方式。本节讨论了用于正则表达式匹配的函数和运算符，并举例说明了一些特殊字符和结构，可用于正则表达式操作。另请参见第 5.3.4.7 节，“模式匹配”。

MySQL 使用国际化组件 Unicode（ICU）实现正则表达式支持，提供完整的 Unicode 支持并且是多字节安全的。（在 MySQL 8.0.4 之前，MySQL 使用 Henry Spencer 的正则表达式实现，以字节方式操作，不是多字节安全的。有关使用正则表达式的应用程序可能受到实现更改影响的信息，请参见正则表达式兼容性注意事项.）

在 MySQL 8.0.22 之前，可以使用二进制字符串参数与这些函数，但结果不一致。在 MySQL 8.0.22 及更高版本中，使用任何 MySQL 正则表达式函数的二进制字符串将被拒绝，并显示ER_CHARACTER_SET_MISMATCH。

  • 正则表达式函数和运算符描述

  • 正则表达式语法

  • 正则表达式资源控制

  • 正则表达式兼容性注意事项

正则表达式函数和运算符描述

  • *expr* NOT REGEXP *pat*, *expr* NOT RLIKE *pat*

    这与 NOT (*expr* REGEXP *pat*) 相同。

  • *expr* REGEXP *pat*, *expr* RLIKE *pat*

    如果字符串*expr与模式pat指定的正则表达式匹配，则返回 1，否则返回 0。如果expr或pat*为NULL，则返回值为NULL。

    REGEXP和RLIKE是REGEXP_LIKE()的同义词。

    有关匹配发生的更多信息，请参阅REGEXP_LIKE()的描述。

    mysql> SELECT 'Michael!' REGEXP '.*';
+------------------------+
| 'Michael!' REGEXP '.*' |
+------------------------+
|                      1 |
+------------------------+
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line' |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
mysql> SELECT 'a' REGEXP '^[a-d]';
+---------------------+
| 'a' REGEXP '^[a-d]' |
+---------------------+
|                   1 |
+---------------------+

  • REGEXP_INSTR(*expr*, *pat*[, *pos*[, *occurrence*[, *return_option*[, *match_type*]]]])

    返回字符串*expr中与模式pat指定的正则表达式匹配的子字符串的起始索引，如果没有匹配则返回 0。如果expr或pat*为NULL，则返回值为NULL。字符索引从 1 开始。

    REGEXP_INSTR()接受这些可选参数：

    • pos：开始搜索的*expr*中的位置。如果省略，则默认为 1。

    • occurrence：要搜索的匹配的哪个出现。如果省略，则默认为 1。

    • return_option：要返回的位置类型。如果此值为 0，REGEXP_INSTR()将返回匹配子字符串的第一个字符的位置。如果此值为 1，REGEXP_INSTR()将返回匹配子字符串后面的位置。如果省略，则默认为 0。

    • match_type：指定如何执行匹配的字符串。其含义如REGEXP_LIKE()中描述的。

    有关匹配发生的更多信息，请参阅REGEXP_LIKE()的描述。

    mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+

  • REGEXP_LIKE(*expr*, *pat*[, *match_type*])

    如果字符串*expr与模式pat指定的正则表达式匹配，则返回 1，否则返回 0。如果expr或pat*为NULL，则返回值为NULL。

    模式可以是扩展正则表达式，其语法在正则表达式语法中讨论。模式不必是一个字面字符串。例如，它可以被指定为一个字符串表达式或表列。

    可选的*match_type*参数是一个字符串，可以包含任何或所有以下字符，指定如何执行匹配：

    • c：区分大小写匹配。

    • i：不区分大小写匹配。

    • m：多行模式。识别字符串内的行终止符。默认行为是仅在字符串表达式的开头和结尾匹配行终止符。

    • n：.字符匹配行终止符。默认情况下，.匹配会在行尾停止。

    • u：仅适用于 Unix 换行符。只有换行符被.、^和$匹配操作符识别为换行符。

    如果在*match_type*中指定了指定矛盾选项的字符，则最右边的选项优先。

    默认情况下，正则表达式操作在决定字符类型和执行比较时使用*expr和pat*参数的字符集和排序规则。如果参数具有不同的字符集或排序规则，则会应用强制转换规则，如第 12.8.4 节“表达式中的排序规则可转换性”中所述。可以使用显式排序标识符指定参数以更改比较行为。

    mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
+---------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+

    *match_type可以用c或i字符指定以覆盖默认的大小写敏感性。例外：如果任一参数是二进制字符串，则参数将作为二进制字符串进行大小写敏感处理，即使match_type*包含i字符。

    注意

    MySQL 在字符串中使用 C 转义语法（例如，\n表示换行符）。如果您希望您的*expr或pat*参数包含文字\，则必须将其加倍。（除非启用了NO_BACKSLASH_ESCAPES SQL 模式，在这种情况下不使用转义字符。）

    mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+

    mysql> SELECT REGEXP_LIKE('abc', 'ABC');
+---------------------------+
| REGEXP_LIKE('abc', 'ABC') |
+---------------------------+
|                         1 |
+---------------------------+
mysql> SELECT REGEXP_LIKE('abc', 'ABC', 'c');
+--------------------------------+
| REGEXP_LIKE('abc', 'ABC', 'c') |
+--------------------------------+
|                              0 |
+--------------------------------+

  • REGEXP_REPLACE(*expr*, *pat*, *repl*[, *pos*[, *occurrence*[, *match_type*]]])

    用替换字符串*repl替换与模式pat指定的正则表达式匹配的字符串expr中的出现，并返回结果字符串。如果expr、pat或repl*为NULL，则返回值为NULL。

    REGEXP_REPLACE()接受这些可选参数：

    • pos：在*expr*中开始搜索的位置。如果省略，默认为 1。

    • occurrence：要替换的匹配的哪个出现。如果省略，默认为 0（表示“替换所有出现”）。

    • match_type：一个指定如何执行匹配的字符串。其含义如REGEXP_LIKE()中所述。

    在 MySQL 8.0.17 之前，此函数返回的结果使用UTF-16字符集；在 MySQL 8.0.17 及更高版本中，使用搜索匹配的表达式的字符集和排序规则。（Bug #94203，Bug #29308212）

    有关匹配发生的更多信息，请参阅REGEXP_LIKE()的描述。

    mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+

  • REGEXP_SUBSTR(*expr*, *pat*[, *pos*[, *occurrence*[, *match_type*]]])

    返回与模式*pat指定的正则表达式匹配的字符串expr的子字符串，如果没有匹配则返回NULL。如果expr或pat*为NULL，则返回值为NULL。

    REGEXP_SUBSTR()接受这些可选参数：

    • pos：在*expr*中开始搜索的位置。如果省略，默认为 1。

    • occurrence：要搜索的匹配的哪个出现。如果省略，默认为 1。

    • match_type：指定如何执行匹配的字符串。其含义与REGEXP_LIKE()中描述的相同。

    在 MySQL 8.0.17 之前，此函数返回的结果使用UTF-16字符集；在 MySQL 8.0.17 及更高版本中，使用搜索匹配的表达式的字符集和校对规则。（Bug #94203，Bug #29308212）

    有关匹配发生的更多信息，请参阅REGEXP_LIKE()的描述。

    mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+

正则表达式语法

正则表达式描述了一组字符串。最简单的正则表达式是没有特殊字符的正则表达式。例如，正则表达式hello匹配hello，不匹配其他任何内容。

复杂的正则表达式使用特定的特殊构造，以便能够匹配多个字符串。例如，正则表达式hello|world包含|交替运算符，匹配hello或world。

作为一个更复杂的例子，正则表达式B[an]*s匹配任何字符串Bananas，Baaaaas，Bs，以及任何以B开头，以s结尾，并且在中间包含任意数量的a或n字符的字符串。

以下列表涵盖了一些基本的特殊字符和构造，可用于正则表达式中。有关 ICU 库支持的完整正则表达式语法的信息，请访问国际 Unicode 组件网站。

  • ^

    匹配字符串的开头。

    mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$');                   -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^fo');                      -> 1

  • $

    匹配字符串的结尾。

    mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$');                 -> 1
mysql> SELECT REGEXP_LIKE('fo\no', '^fo$');                    -> 0

  • .

    匹配任何字符（包括回车和换行符，尽管要匹配字符串中间的这些字符，必须给定m（多行）匹配控制字符或(?m)模式内修改器）。

    mysql> SELECT REGEXP_LIKE('fofo', '^f.*$');                    -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');                -> 0
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');           -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');           -> 1

  • a*

    匹配零个或多个a字符的序列。

    mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');                     -> 1
mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n');                   -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n');                      -> 1

  • a+

    匹配一个或多个a字符的序列。

    mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');                     -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');                      -> 0

  • a?

    匹配零个或一个a字符。

    mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n');                      -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n');                     -> 1
mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n');                    -> 0

  • de|abc

    交替；匹配de或abc中的任一序列。

    mysql> SELECT REGEXP_LIKE('pi', 'pi|apa');                     -> 1
mysql> SELECT REGEXP_LIKE('axe', 'pi|apa');                    -> 0
mysql> SELECT REGEXP_LIKE('apa', 'pi|apa');                    -> 1
mysql> SELECT REGEXP_LIKE('apa', '^(pi|apa)$');                -> 1
mysql> SELECT REGEXP_LIKE('pi', '^(pi|apa)$');                 -> 1
mysql> SELECT REGEXP_LIKE('pix', '^(pi|apa)$');                -> 0

  • (abc)*

    匹配序列abc的零个或多个实例。

    mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$');                    -> 1
mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$');                   -> 0
mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$');                  -> 1

  • {1}, {2,3}

    重复；{*n*}和{*m*,*n*}表示一种更通用的写正则表达式的方式，可以匹配前一个原子（或“片段”）的模式的多个出现。*m和n*是整数。

    • a*

      可以写成a{0,}。

    • a+

      可以写成a{1,}。

    • a?

      可以写成a{0,1}。

    要更精确地说，a{*n*} 精确匹配 n 个 a。a{*n*,} 匹配 n 个或更多个 a。a{*m*,*n*} 匹配 m 到 n 个 a，包括两端。如果同时给出 m 和 n，则 m 必须小于或等于 n。

    mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e');              -> 0
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');              -> 1
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');           -> 1

  • [a-dX], [^a-dX]

    匹配任何是（或不是，如果使用 ^）a、b、c、d 或 X 的字符。两个字符之间的 - 形成一个范围，匹配从第一个字符到第二个字符的所有字符。例如，[0-9] 匹配任何十进制数字。要包含文字 ] 字符，它必须紧跟在开方括号 [ 之后。要包含文字 - 字符，它必须写在最前面或最后面。在 [] 对中没有定义特殊含义的任何字符只匹配它本身。

    mysql> SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');                 -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$');               -> 0
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');              -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');             -> 0
mysql> SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');            -> 1
mysql> SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');           -> 0

  • [=character_class=]

    在方括号表达式（使用 [ 和 ] 编写）中，[=character_class=] 表示一个等价类。它匹配所有具有相同排序值的字符，包括它本身。例如，如果 o 和 (+) 是等价类的成员，则 [[=o=]]、[[=(+)=]] 和 [o(+)] 都是同义词。等价类不能用作范围的端点。

  • [:character_class:]

    在方括号表达式（使用 [ 和 ] 编写）中，[:character_class:] 表示一个匹配属于该类的所有字符的字符类。以下表列出了标准类名。这些名称代表 ctype(3) 手册页中定义的字符类。特定区域设置可能提供其他类名。字符类不能用作范围的端点。

    字符类名含义
    alnum字母数字字符
    alpha字母字符
    blank空白字符
    cntrl控制字符
    digit数字字符
    graph图形字符
    lower小写字母字符
    print图形或空格字符
    punct标点字符
    space空格、制表符、换行符和回车符
    upper大写字母字符
    xdigit十六进制数字字符
    字符类名含义
    mysql> SELECT REGEXP_LIKE('justalnums', '[[:alnum:]]+');       -> 1
mysql> SELECT REGEXP_LIKE('!!', '[[:alnum:]]+');               -> 0

    由于 ICU 知道 utf16_general_ci 中的所有字母字符，因此某些字符类的性能可能不如字符范围高。例如，[a-zA-Z] 比 [[:alpha:]] 更快，[0-9] 通常比 [[:digit:]] 更快。如果您正在迁移使用 [[:alpha:]] 或 [[:digit:]] 的应用程序从旧版本的 MySQL，您应该将这些替换为等效的范围以在 MySQL 8.0 中使用。

要在正则表达式中使用特殊字符的文字实例，请在其前面加上两个反斜杠（\）字符。MySQL 解析器解释其中一个反斜杠，而正则表达式库解释另一个。例如，要匹配包含特殊+字符的字符串1+2，只有以下正则表达式中的最后一个是正确的：

mysql> SELECT REGEXP_LIKE('1+2', '1+2');                       -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\+2');                      -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\\+2');                     -> 1

正则表达式资源控制

REGEXP_LIKE()和类似函数使用可以通过设置系统变量来控制的资源：

  • 匹配引擎使用内部堆栈的内存。要控制堆栈的最大可用内存（以字节为单位），请设置regexp_stack_limit系统变量。

  • 匹配引擎以步骤方式运行。要控制引擎执行的最大步骤数（从而间接影响执行时间），请设置regexp_time_limit系统变量。因为此限制表示为步骤数，所以它只间接影响执行时间。通常，它的数量级为毫秒。

正则表达式兼容性考虑

在 MySQL 8.0.4 之前，MySQL 使用 Henry Spencer 正则表达式库来支持正则表达式操作，而不是国际 Unicode 组件（ICU）。以下讨论描述了 Spencer 和 ICU 库之间可能影响应用程序的差异：

  • 使用 Spencer 库，REGEXP和RLIKE运算符以字节方式工作，因此它们不是多字节安全的，可能会在使用多字节字符集时产生意外结果。此外，这些运算符通过它们的字节值比较字符，即使给定排序将它们视为相等，重音字符也可能不相等。

    ICU 具有完整的 Unicode 支持，并且是多字节安全的。其正则表达式函数将所有字符串视为UTF-16。您应该记住，位置索引是基于 16 位块而不是代码点的。这意味着，当传递给这些函数时，使用多个块的字符可能会产生意想不到的结果，例如下面所示：

    mysql> SELECT REGEXP_INSTR('🍣🍣b', 'b');
+--------------------------+
| REGEXP_INSTR('??b', 'b') |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('🍣🍣bxxx', 'b', 4);
+--------------------------------+
| REGEXP_INSTR('??bxxx', 'b', 4) |
+--------------------------------+
|                              5 |
+--------------------------------+
1 row in set (0.00 sec)

    Unicode 基本多文种平面内的字符（包括大多数现代语言使用的字符）在这方面是安全的：

    mysql> SELECT REGEXP_INSTR('бжb', 'b');
+----------------------------+
| REGEXP_INSTR('бжb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('עבb', 'b');
+----------------------------+
| REGEXP_INSTR('עבb', 'b')   |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('µå周çб', '周');
+------------------------------------+
| REGEXP_INSTR('µå周çб', '周')       |
+------------------------------------+
|                                  3 |
+------------------------------------+
1 row in set (0.00 sec)

    表情符号，例如第一个两个示例中使用的“寿司”字符🍣（U+1F363），不包括在基本多语言平面中，而是在 Unicode 的补充多语言平面中。当REGEXP_SUBSTR()或类似函数从字符中间开始搜索时，表情符号和其他 4 字节字符可能会出现问题。以下示例中的两个语句中的每个都从第一个参数的第二个 2 字节位置开始。第一个语句适用于仅由 2 字节（BMP）字符组成的字符串。第二个语句包含 4 字节字符，结果中错误解释了这些字符，因为前两个字节被剥离，因此字符数据的其余部分错位。

    mysql> SELECT REGEXP_SUBSTR('周周周周', '.*', 2);
+----------------------------------------+
| REGEXP_SUBSTR('周周周周', '.*', 2)     |
+----------------------------------------+
| 周周周                                 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_SUBSTR('🍣🍣🍣🍣', '.*', 2);
+--------------------------------+
| REGEXP_SUBSTR('????', '.*', 2) |
+--------------------------------+
| ?㳟揘㳟揘㳟揘                  |
+--------------------------------+
1 row in set (0.00 sec)

  • 对于.运算符，Spencer 库在字符串表达式中的任何位置（包括中间）匹配行终止字符（回车，换行）。要在 ICU 中匹配字符串中间的行终止字符，请指定m匹配控制字符。

  • Spencer 库支持单词开头和单词结尾边界标记（[[:<:]]和[[:>:]]表示）。ICU 不支持。对于 ICU，您可以使用\b来匹配单词边界；双反斜杠因为 MySQL 将其解释为字符串中的转义字符。

  • Spencer 库支持整理元素括号表达式（[.characters.]表示）。ICU 不支持。

  • 对于重复计数（{n}和{m,n}表示），Spencer 库最多为 255。ICU 没有此限制，尽管可以通过设置regexp_time_limit系统变量来限制匹配引擎步骤的最大数量。

  • ICU 将括号解释为元字符。要在正则表达式中指定字面上的开放(或关闭括号`)，必须进行转义：

    mysql> SELECT REGEXP_LIKE('(', '(');
ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE('(', '\\(');
+-------------------------+
| REGEXP_LIKE('(', '\\(') |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT REGEXP_LIKE(')', ')');
ERROR 3692 (HY000): Mismatched parenthesis in regular expression.
mysql> SELECT REGEXP_LIKE(')', '\\)');
+-------------------------+
| REGEXP_LIKE(')', '\\)') |
+-------------------------+
|                       1 |
+-------------------------+

  • ICU 还将方括号解释为元字符，但只有开放方括号需要转义才能用作字面字符：

    mysql> SELECT REGEXP_LIKE('[', '[');
ERROR 3696 (HY000): The regular expression contains an
unclosed bracket expression.
mysql> SELECT REGEXP_LIKE('[', '\\[');
+-------------------------+
| REGEXP_LIKE('[', '\\[') |
+-------------------------+
|                       1 |
+-------------------------+
mysql> SELECT REGEXP_LIKE(']', ']');
+-----------------------+
| REGEXP_LIKE(']', ']') |
+-----------------------+
|                     1 |
+-----------------------+

14.8.3 函数结果的字符集和排序规则

原文：dev.mysql.com/doc/refman/8.0/en/string-functions-charset.html

MySQL 有许多返回字符串的运算符和函数。本节回答了一个问题：这样的字符串的字符集和排序规则是什么？

对于接受字符串输入并返回字符串结果的简单函数，输出的字符集和排序规则与主要输入值相同。例如，UPPER(*X*) 返回一个与 X 相同字符集和排序规则的字符串。对于 INSTR()、LCASE()、LOWER()、LTRIM()、MID()、REPEAT()、REPLACE()、REVERSE()、RIGHT()、RPAD()、RTRIM()、SOUNDEX()、SUBSTRING()、TRIM()、UCASE() 和 UPPER() 也适用相同规则。

注意

与所有其他函数不同，REPLACE() 函数始终忽略字符串输入的排序规则并执行区分大小写的比较。

如果字符串输入或函数结果是二进制字符串，则该字符串具有 binary 字符集和排序规则。可以通过使用 CHARSET() 和 COLLATION() 函数来检查，两者对于二进制字符串参数都返回 binary：

mysql> SELECT CHARSET(BINARY 'a'), COLLATION(BINARY 'a');
+---------------------+-----------------------+
| CHARSET(BINARY 'a') | COLLATION(BINARY 'a') |
+---------------------+-----------------------+
| binary              | binary                |
+---------------------+-----------------------+

对于将多个字符串输入组合并返回单个字符串输出的操作，标准 SQL 的“聚合规则”适用于确定结果的排序规则：

  • 如果明确出现 COLLATE *Y*，则使用 Y。

  • 如果明确出现 COLLATE *Y* 和 COLLATE *Z*，则引发错误。

  • 否则，如果所有排序规则都是 Y，则使用 Y。

  • 否则，结果没有排序规则。

例如，使用 CASE ... WHEN a THEN b WHEN b THEN c COLLATE *X* END，结果的排序规则是 X。对于 UNION、||、CONCAT()、ELT()、GREATEST()、IF() 和 LEAST() 也适用相同规则。

对于转换为字符数据的操作，操作结果字符串的字符集和排序由确定默认连接字符集和排序的character_set_connection和collation_connection系统变量定义（参见第 12.4 节，“连接字符集和排序”）。这仅适用于BIN_TO_UUID()、CAST()、CONV()、FORMAT()、HEX()和SPACE()。

对于虚拟生成列的表达式，前述原则有一个例外。在这种表达式中，无论连接字符集如何，都使用表字符集来处理BIN_TO_UUID()、CONV()或HEX()的结果。

如果对字符串函数返回的结果的字符集或排序有任何疑问，请使用CHARSET()或COLLATION()函数来查找：

mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());
+----------------+-----------------+--------------------+
| USER()         | CHARSET(USER()) | COLLATION(USER())  |
+----------------+-----------------+--------------------+
| test@localhost | utf8mb3         | utf8mb3_general_ci |
+----------------+-----------------+--------------------+
mysql> SELECT CHARSET(COMPRESS('abc')), COLLATION(COMPRESS('abc'));
+--------------------------+----------------------------+
| CHARSET(COMPRESS('abc')) | COLLATION(COMPRESS('abc')) |
+--------------------------+----------------------------+
| binary                   | binary                     |
+--------------------------+----------------------------+

14.9 全文搜索函数

原文：dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

14.9.1 自然语言全文搜索

14.9.2 布尔全文搜索

14.9.3 带有查询扩展的全文搜索

14.9.4 全文搜索停用词

14.9.5 全文搜索限制

14.9.6 调整 MySQL 全文搜索

14.9.7 为全文索引添加用户定义的排序规则

14.9.8 ngram 全文解析器

14.9.9 MeCab 全文解析器插件

MATCH (*col1*,*col2*,...) AGAINST (*expr* [*search_modifier*])

*search_modifier:*
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

MySQL 支持全文索引和搜索：

  • MySQL 中的全文索引是FULLTEXT类型的索引。

  • 全文索引只能用于InnoDB或MyISAM表，并且只能为CHAR、VARCHAR或TEXT列创建。

  • MySQL 提供了内置的全文 ngram 解析器，支持中文、日文和韩文（CJK），以及用于日文的可安装的 MeCab 全文解析器插件。解析差异在第 14.9.8 章“ngram 全文解析器”和第 14.9.9 章“MeCab 全文解析器插件”中有详细说明。

  • 在创建表时，可以在CREATE TABLE语句中给出FULLTEXT索引定义，或者稍后使用ALTER TABLE或CREATE INDEX添加。

  • 对于大型数据集，将数据加载到没有FULLTEXT索引的表中，然后在此之后创建索引，比将数据加载到已有FULLTEXT索引的表中要快得多。

使用MATCH() AGAINST()语法执行全文搜索。MATCH()接受一个逗号分隔的列表，列出要搜索的列。AGAINST接受要搜索的字符串，以及一个可选的修饰符，指示要执行的搜索类型。搜索字符串必须是在查询评估期间保持不变的字符串值。例如，表列就不符合这个规则，因为每行可能不同。

以前，MySQL 允许在MATCH()中使用一个 rollup 列，但使用这种结构的查询性能不佳且结果不可靠。（这是因为MATCH()不是根据其参数的函数实现，而是根据基表的底层扫描中当前行的行 ID 的函数实现。）从 MySQL 8.0.28 开始，MySQL 不再允许这样的查询；更具体地说，任何符合此处列出的所有标准的查询都将被拒绝，并显示ER_FULLTEXT_WITH_ROLLUP：

  • MATCH()出现在查询块的SELECT列表、GROUP BY子句、HAVING子句或ORDER BY子句中。

  • 查询块包含一个GROUP BY ... WITH ROLLUP子句。

  • 调用MATCH()函数的参数是分组列之一。

这里显示了一些此类查询的示例：

# MATCH() in SELECT list...
SELECT MATCH (a) AGAINST ('abc') FROM t GROUP BY a WITH ROLLUP;
SELECT 1 FROM t GROUP BY a, MATCH (a) AGAINST ('abc') WITH ROLLUP;

# ...in HAVING clause...
SELECT 1 FROM t GROUP BY a WITH ROLLUP HAVING MATCH (a) AGAINST ('abc');

# ...and in ORDER BY clause
SELECT 1 FROM t GROUP BY a WITH ROLLUP ORDER BY MATCH (a) AGAINST ('abc');

在WHERE子句中允许使用带有 rollup 列的MATCH()。

有三种类型的全文搜索：

  • 自然语言搜索将搜索字符串解释为自然人类语言中的短语（自由文本中的短语）。除了双引号（"）字符外，没有特殊运算符。停用词列表适用。有关停用词列表的更多信息，请参见第 14.9.4 节，“全文停用词”。

    如果给定IN NATURAL LANGUAGE MODE修饰符或未给定修饰符，则全文搜索是自然语言搜索。有关更多信息，请参见第 14.9.1 节，“自然语言全文搜索”。

  • 布尔搜索使用特殊查询语言的规则解释搜索字符串。字符串包含要搜索的单词。它还可以包含指定要求的运算符，例如一个单词必须存在或不存在于匹配行中，或者它应该比通常更高或更低权重。某些常见单词（停用词）被省略在搜索索引中，如果在搜索字符串中存在则不匹配。IN BOOLEAN MODE修饰符指定布尔搜索。有关更多信息，请参见第 14.9.2 节，“布尔全文搜索”。

  • 查询扩展搜索是自然语言搜索的修改。搜索字符串用于执行自然语言搜索。然后从搜索返回的最相关行中添加单词到搜索字符串，并再次进行搜索。查询返回第二次搜索的行。IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION或WITH QUERY EXPANSION修饰符指定查询扩展搜索。有关更多信息，请参见第 14.9.3 节，“带查询扩展的全文搜索”。

有关FULLTEXT查询性能的信息，请参见 Section 10.3.5, “Column Indexes”。

关于InnoDB FULLTEXT索引的更多信息，请参见 Section 17.6.2.4, “InnoDB Full-Text Indexes”。

全文搜索的限制条件列在 Section 14.9.5, “Full-Text Restrictions”。

myisam_ftdump实用程序会转储MyISAM全文索引的内容。这对于调试全文查询可能会有所帮助。请参见 Section 6.6.3, “myisam_ftdump — Display Full-Text Index information”。

14.9.1 自然语言全文搜索

原文：dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html

默认情况下或使用IN NATURAL LANGUAGE MODE修饰符时，MATCH()函数针对文本集合执行自然语言搜索。集合是包含在FULLTEXT索引中的一个或多个列的集合。搜索字符串作为参数传递给AGAINST()。对于表中的每一行，MATCH()返回一个相关性值；即，搜索字符串与列中文本之间的相似度量，在MATCH()列表中命名的列中。

mysql> CREATE TABLE articles (
 ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 ->   title VARCHAR(200),
 ->   body TEXT,
 ->   FULLTEXT (title,body)
 -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO articles (title,body) VALUES
 ->   ('MySQL Tutorial','DBMS stands for DataBase ...'),
 ->   ('How To Use MySQL Well','After you went through a ...'),
 ->   ('Optimizing MySQL','In this tutorial, we show ...'),
 ->   ('1001 MySQL Tricks','1\. Never run mysqld as root. 2\. ...'),
 ->   ('MySQL vs. YourSQL','In the following database comparison ...'),
 ->   ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
 -> WHERE MATCH (title,body)
 -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

默认情况下，搜索是以不区分大小写的方式执行的。要执行区分大小写的全文搜索，请为索引列使用区分大小写或二进制排序规则。例如，使用utf8mb4字符集的列可以分配utf8mb4_0900_as_cs或utf8mb4_bin排序规则，以使其对全文搜索区分大小写。

当MATCH()在WHERE子句中使用时，如前面示例所示，只要满足以下条件，返回的行将自动按相关性最高的顺序排序：

  • 不得有显式的ORDER BY子句。

  • 搜索必须使用全文索引扫描，而不是表扫描。

  • 如果查询涉及表连接，则全文索引扫描必须是连接中最左边的非常量表。

鉴于刚刚列出的条件，当需要或希望时，通常更容易通过使用ORDER BY指定显式排序顺序。

相关性值是非负浮点数。零相关性表示没有相似性。相关性是基于行中的单词数、行中唯一单词数、集合中的总单词数以及包含特定单词的行数进行计算的。

注意

“文档”一词可以与“行”一词互换使用，两个术语都指的是行的索引部分。“集合”一词指的是索引列，并包括所有行。

要简单计算匹配项，可以使用如下查询：

mysql> SELECT COUNT(*) FROM articles
 -> WHERE MATCH (title,body)
 -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

你可能会发现将查询重写为以下形式更快：

mysql> SELECT
 -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
 -> AS count
 -> FROM articles;
+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.03 sec)

第一个查询做了一些额外的工作（按相关性对结果进行排序），但也可以根据WHERE子句使用索引查找。如果搜索匹配的行数较少，索引查找可能会使第一个查询更快。第二个查询执行全表扫描，如果搜索词在大多数行中存在，可能比索引查找更快。

对于自然语言全文搜索，MATCH()函数中命名的列必须与表中某个FULLTEXT索引中包含的相同列相同。对于上述查询，请注意MATCH()函数中命名的列（title和body）与article表的FULLTEXT索引定义中命名的列相同。要分别搜索title或body，您需要为每个列创建单独的FULLTEXT索引。

你还可以执行布尔搜索或带有查询扩展的搜索。这些搜索类型在第 14.9.2 节，“布尔全文搜索”和第 14.9.3 节，“带有查询扩展的全文搜索”中有描述。

使用索引的全文搜索在MATCH()子句中只能命名来自单个表的列，因为索引不能跨越多个表。对于MyISAM表，如果没有索引，可以执行布尔搜索（尽管速度较慢），在这种情况下，可以命名来自多个表的列。

上面的示例是一个基本示例，展示了如何在返回的行按相关性递减的顺序中使用MATCH()函数。下一个示例展示了如何显式检索相关性值。返回的行没有排序，因为SELECT语句中既没有WHERE也没有ORDER BY子句：

mysql> SELECT id, MATCH (title,body)
 -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
 -> FROM articles;
+----+---------------------+
| id | score               |
+----+---------------------+
|  1 | 0.22764469683170319 |
|  2 |                   0 |
|  3 | 0.22764469683170319 |
|  4 |                   0 |
|  5 |                   0 |
|  6 |                   0 |
+----+---------------------+
6 rows in set (0.00 sec)

下面的示例更为复杂。该查询返回相关性值，并按相关性递减的顺序对行进行排序。为了实现这个结果，在SELECT列表中指定MATCH()两次：一次在SELECT列表中，一次在WHERE子句中。这不会增加额外开销，因为 MySQL 优化器注意到两次MATCH()调用是相同的，并且只调用一次全文搜索代码。

mysql> SELECT id, body, MATCH (title,body)
 ->   AGAINST ('Security implications of running MySQL as root'
 ->   IN NATURAL LANGUAGE MODE) AS score
 -> FROM articles
 ->   WHERE MATCH (title,body) 
 ->   AGAINST('Security implications of running MySQL as root'
 ->   IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1\. Never run mysqld as root. 2\. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

用双引号(")括起来的短语仅匹配包含该短语的行文字，就像它被输入的那样。全文引擎将短语拆分为单词，并在FULLTEXT索引中为这些单词执行搜索。非单词字符不需要完全匹配：短语搜索只需要匹配包含与短语完全相同的单词并且顺序相同的匹配项。例如，"test phrase"匹配"test, phrase"。如果短语不包含索引中的任何单词，则结果为空。例如，如果所有单词要么是停用词要么比索引单词的最小长度短，结果为空。

MySQL 的FULLTEXT实现将任何真实单词字符序列（字母，数字和下划线）视为一个单词。该序列也可以包含撇号（'），但不能连续超过一个。这意味着aaa'bbb被视为一个单词，但aaa''bbb被视为两个单词。单词开头或结尾的撇号将被FULLTEXT解析器去除；'aaa'bbb'会被解析为aaa'bbb。

内置的FULLTEXT解析器通过查找特定的分隔符字符来确定单词的起始和结束位置；例如，（空格），,（逗号）和.（句号）。如果单词没有被分隔符（例如，中文）分隔，内置的FULLTEXT解析器无法确定单词的起始或结束位置。为了能够将这些语言中的单词或其他索引术语添加到使用内置FULLTEXT解析器的FULLTEXT索引中，您必须预处理它们，以便它们被某种任意的分隔符分隔。或者，您可以使用 ngram 解析器插件（用于中文，日文或韩文）或 MeCab 解析器插件（用于日文）创建FULLTEXT索引。

可以编写一个插件来替换内置的全文解析器。有关详细信息，请参阅 MySQL 插件 API。例如解析器插件源代码，请查看 MySQL 源代码分发的plugin/fulltext目录。

在全文搜索中会忽略一些词：

  • 任何太短的单词都会被忽略。通过全文搜索找到的单词的默认最小长度为InnoDB搜索索引为三个字符，或者MyISAM为四个字符。您可以通过在创建索引之前设置配置选项来控制截断：InnoDB搜索索引的innodb_ft_min_token_size配置选项，或者MyISAM的ft_min_word_len。

    注意

    这种行为不适用于使用 ngram 解析器的FULLTEXT索引。对于 ngram 解析器，标记长度由ngram_token_size选项定义。

  • 停用词列表中的单词会被忽略。停用词是一些如“the”或“some”这样常见以至于被认为没有语义价值的单词。有一个内置的停用词列表，但可以被用户定义的列表覆盖。停用词列表和相关的配置选项对InnoDB搜索索引和MyISAM索引是不同的。停用词处理由配置选项innodb_ft_enable_stopword，innodb_ft_server_stopword_table和innodb_ft_user_stopword_table控制InnoDB搜索索引，以及ft_stopword_file控制MyISAM索引。

参见第 14.9.4 节，“全文停用词”查看默认停用词列表以及如何更改它们。默认最小单词长度可以按照第 14.9.6 节，“调整 MySQL 全文搜索”中描述的方式进行更改。

集合和查询中的每个正确单词根据其在集合或查询中的重要性进行加权。因此，出现在许多文档中的单词权重较低，因为在这个特定集合中它的语义价值较低。相反，如果单词很少见，它将获得更高的权重。单词的权重组合在一起计算行的相关性。这种技术在大型集合中效果最佳。

MyISAM 限制

对于非常小的表，单词分布不能充分反映它们的语义价值，这种模型有时可能会为MyISAM表上的搜索索引产生奇怪的结果。例如，尽管单词“MySQL”出现在先前显示的articles表的每一行中，但在MyISAM搜索索引中搜索该单词却没有结果：

mysql> SELECT * FROM articles
 -> WHERE MATCH (title,body)
 -> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

由于单词“MySQL”至少出现在 50%的行中，搜索结果为空，因此实际上被视为停用词。这种过滤技术更适用于大数据集，您可能不希望结果集从 1GB 表中返回每一秒的行，而不适用于小数据集，因为这可能导致热门术语的结果不佳。

当您首次尝试全文搜索以查看其工作原理时，50%的阈值可能会让您感到惊讶，并使InnoDB表更适合用于全文搜索的实验。如果您创建一个MyISAM表并只插入一两行文本，那么文本中的每个单词至少在 50%的行中出现。因此，在表包含更多行之前，任何搜索都不会返回任何结果。需要绕过 50%限制的用户可以在InnoDB表上构建搜索索引，或者使用第 14.9.2 节，“布尔全文搜索”中解释的布尔搜索模式。

14.9.2 布尔全文搜索

原文：dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

MySQL 可以使用IN BOOLEAN MODE修饰符执行布尔全文搜索。使用此修饰符，搜索字符串中的某些字符在单词开头或结尾具有特殊含义。在以下查询中，+和-运算符表示单词必须存在或不存在，才能进行匹配。因此，该查询检索包含单词“MySQL”但不包含单词“YourSQL”的所有行：

mysql> SELECT * FROM articles WHERE MATCH (title,body)
 -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial, we show ...       |
|  4 | 1001 MySQL Tricks     | 1\. Never run mysqld as root. 2\. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

注意

在实现此功能时，MySQL 使用所谓的隐含布尔逻辑，其中

  • +代表AND

  • -代表NOT

  • [无运算符]表示OR

布尔全文搜索具有以下特点：

  • 它们不会自动按相关性降序对行进行排序。

  • InnoDB表在执行布尔查询时需要在MATCH()表达式的所有列上创建FULLTEXT索引。对MyISAM搜索索引执行布尔查询即使没有FULLTEXT索引也可以工作，尽管以这种方式执行的搜索速度会非常慢。

  • 最小和最大单词长度全文参数适用于使用内置FULLTEXT解析器和 MeCab 解析器插件创建的FULLTEXT索引。innodb_ft_min_token_size和innodb_ft_max_token_size用于InnoDB搜索索引。ft_min_word_len和ft_max_word_len用于MyISAM搜索索引。

    最小和最大单词长度全文参数不适用于使用 ngram 解析器创建的FULLTEXT索引。 ngram 标记大小由ngram_token_size选项定义。

  • 停用词列表适用于InnoDB搜索索引，由innodb_ft_enable_stopword、innodb_ft_server_stopword_table和innodb_ft_user_stopword_table控制，以及MyISAM索引由ft_stopword_file控制。

  • InnoDB全文搜索不支持在单个搜索词上使用多个运算符，例如：'++apple'。在单个搜索词上使用多个运算符会返回一个语法错误到标准输出。MyISAM 全文搜索成功处理相同的搜索，忽略除了紧邻搜索词的运算符之外的所有运算符。

  • InnoDB全文搜索仅支持前导加号或减号。例如，InnoDB支持'+apple'，但不支持'apple+'。指定尾随加号或减号会导致InnoDB报告语法错误。

  • InnoDB全文搜索不支持在通配符（'+*'）、加减号组合（'+-'）或前导加减号组合（'+-apple'）中使用前导加号。这些无效查询会返回语法错误。

  • InnoDB全文搜索不支持在布尔全文搜索中使用@符号。@符号保留供@distance接近搜索运算符使用。

  • 它们不使用适用于MyISAM搜索索引的 50%阈值。

布尔全文搜索功能支持以下运算符：

  • +

    前导或尾随加号表示该单词必须出现在返回的每一行中。InnoDB仅支持前导加号。

  • -

    前导或尾随减号表示这个词必须不出现在返回的任何行中。InnoDB仅支持前导减号。

    注意：-运算符仅用于排除其他搜索项匹配的行。因此，仅包含由-前导的术语的布尔模式搜索会返回空结果。它不会返回“除了包含任何被排除术语的行之外的所有行”。

  • （无运算符）

    默认情况下（未指定+或-时），该单词是可选的，但包含它的行会被评分更高。这模仿了MATCH() AGAINST()在没有IN BOOLEAN MODE修饰符的情况下的行为。

  • @*distance*

    此运算符仅适用于InnoDB表。它测试两个或更多单词是否都在指定距离内开始，距离以单词为单位测量。在@*distance*运算符之前的双引号字符串中指定搜索词，例如，MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)

  • > <

    这两个运算符用于改变单词对分配给行的相关性值的贡献。>运算符增加贡献，<运算符减少贡献。请参见此列表后面的示例。

  • ( )

    括号将单词分组为子表达式。括号组可以嵌套。

  • ~

    前导波浪号充当否定运算符，导致单词对行的相关性的贡献为负值。这对标记“噪音”词很有用。包含这样一个词的行评分低于其他行，但不会完全被排除，就像使用-运算符一样。

  • *

    星号用作截断（或通配符）运算符。与其他运算符不同，它附加到要受影响的单词之后。如果单词以*运算符之前的单词开头，则匹配。

    如果使用截断运算符指定了一个单词，则即使它太短或是停用词，也不会从布尔查询中删除。一个单词是否太短是根据InnoDB表的innodb_ft_min_token_size设置，或MyISAM表的ft_min_word_len来确定的。这些选项不适用于使用 ngram 解析器的FULLTEXT索引。

    通配符单词被视为必须出现在一个或多个单词的开头的前缀。如果最小单词长度为 4，搜索'+*word* +the*'可能返回的行比搜索'+*word* +the'更少，因为第二个查询忽略了太短的搜索词the。

  • "

    用双引号(")括起来的短语仅匹配包含该短语的行文字，就像输入的那样。全文引擎将短语拆分为单词，并在FULLTEXT索引中搜索这些单词。非单词字符不需要完全匹配：短语搜索只要求匹配包含与短语完全相同的单词且顺序相同的内容。例如，"test phrase"匹配"test, phrase"。

    如果短语不包含索引中的任何单词，则结果为空。这些单词可能不在索引中，因为存在多种因素的组合：如果它们不存在于文本中，是停用词，或者比索引单词的最小长度更短。

以下示例演示了一些使用布尔全文搜索运算符的搜索字符串：

  • 'apple banana'

    查找包含两个词中至少一个的行。

  • '+apple +juice'

    查找同时包含两个单词的行。

  • '+apple macintosh'

    查找包含单词“apple”的行，但如果它们还包含“macintosh”，则将其排名提高。

  • '+apple -macintosh'

    查找包含单词“apple”但不包含“macintosh”的行。

  • '+apple ~macintosh'

    查找包含单词“apple”的行，但如果该行还包含单词“macintosh”，则将其评级低于不包含该词的行。这比搜索'+apple -macintosh'“更柔和”，因为“macintosh”的存在会导致该行根本不返回。

  • '+apple +(>turnover <strudel)'

    查找包含单词“apple”和“turnover”，或“apple”和“strudel”（顺序不限），但将“apple turnover”排名高于“apple strudel”。

  • 'apple*'

    查找包含诸如“apple”、“apples”、“applesauce”或“applet”等单词的行。

  • '"some words"'

    查找包含确切短语“some words”的行（例如，包含“some words of wisdom”但不包含“some noise words”的行）。请注意，包围短语的"字符是界定短语的操作符字符。它们不是包围搜索字符串本身的引号。

InnoDB 布尔模式搜索的相关性排名

InnoDB全文搜索是基于Sphinx全文搜索引擎建模的，所使用的算法基于BM25和TF-IDF排名算法。因此，InnoDB布尔全文搜索的相关性排名可能与MyISAM的相关性排名不同。

InnoDB使用“词项频率-逆文档频率”（TF-IDF）加权系统的变体来为给定的全文搜索查询对文档的相关性进行排名。TF-IDF加权是基于一个词在文档中出现的频率，减去该词在整个文档集合中出现的频率。换句话说，一个词在文档中出现的频率越高，而在文档集合中出现的频率越低，文档的排名就越高。

如何计算相关性排名

词项频率（TF）值是一个词在文档中出现的次数。一个词的逆文档频率（IDF）值是使用以下公式计算的，其中total_records是集合中的记录数，matching_records是搜索词出现在的记录数。

${IDF} = log10( ${total_records} / ${matching_records} )

当一个文档包含多次出现的单词时，IDF 值将乘以 TF 值：

${TF} * ${IDF}

使用TF和IDF值，文档的相关性排名是使用以下公式计算的：

${rank} = ${TF} * ${IDF} * ${IDF}

公式在以下示例中进行演示。

单词搜索的相关性排名

本示例演示了单词搜索的相关性排名计算。

mysql> CREATE TABLE articles (
 ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 ->   title VARCHAR(200),
 ->   body TEXT,
 ->   FULLTEXT (title,body)
 ->)  ENGINE=InnoDB;
Query OK, 0 rows affected (1.04 sec)

mysql> INSERT INTO articles (title,body) VALUES
 ->   ('MySQL Tutorial','This database tutorial ...'),
 ->   ("How To Use MySQL",'After you went through a ...'),
 ->   ('Optimizing Your Database','In this database tutorial ...'),
 ->   ('MySQL vs. YourSQL','When comparing databases ...'),
 ->   ('MySQL Security','When configured properly, MySQL ...'),
 ->   ('Database, Database, Database','database database database'),
 ->   ('1001 MySQL Tricks','1\. Never run mysqld as root. 2\. ...'),
 ->   ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT id, title, body, 
 ->   MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) AS score
 ->   FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+---------------------+
| id | title                        | body                                | score               |
+----+------------------------------+-------------------------------------+---------------------+
|  6 | Database, Database, Database | database database database          |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...       | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...          | 0.18144935369491577 |
|  2 | How To Use MySQL             | After you went through a ...        |                   0 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |                   0 |
|  5 | MySQL Security               | When configured properly, MySQL ... |                   0 |
|  7 | 1001 MySQL Tricks            | 1\. Never run mysqld as root. 2\. ... |                   0 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |                   0 |
+----+------------------------------+-------------------------------------+---------------------+
8 rows in set (0.00 sec)

总共有 8 条记录，其中有 3 条匹配“database”搜索词。第一条记录（id 6）包含搜索词 6 次，相关性排名为1.0886961221694946。此排名值是使用 TF 值为 6（“database”搜索词在记录id 6中出现 6 次）和 IDF 值为 0.42596873216370745 计算的，计算如下（其中 8 是总记录数，3 是搜索词出现在的记录数）：

${IDF} = LOG10( 8 / 3 ) = 0.42596873216370745

然后将TF和IDF值输入到排名公式中：

${rank} = ${TF} * ${IDF} * ${IDF}

在 MySQL 命令行客户端中进行计算返回一个排名值为 1.088696164686938。

mysql> SELECT 6*LOG10(8/3)*LOG10(8/3);
+-------------------------+
| 6*LOG10(8/3)*LOG10(8/3) |
+-------------------------+
|       1.088696164686938 |
+-------------------------+
1 row in set (0.00 sec)

注意

您可能会注意到SELECT ... MATCH ... AGAINST语句返回的排名值与 MySQL 命令行客户端返回的排名值之间存在轻微差异（1.0886961221694946与1.088696164686938）。这种差异是由于InnoDB内部执行整数和浮点数/双精度数之间的转换（以及相关的精度和舍入决策），以及在其他地方执行这些转换的方式，比如在 MySQL 命令行客户端或其他类型的计算器中。

多词搜索的相关性排名

本示例演示了基于articles表和前面示例中使用的数据进行多词全文搜索的相关性排名计算。

如果您搜索超过一个词，相关性排名值是每个词的相关性排名值的总和，如下所示：

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

对两个术语（'mysql 教程'）进行搜索返回以下结果：

mysql> SELECT id, title, body, MATCH (title,body)  
 ->   AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score
 ->   FROM articles ORDER BY score DESC;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1\. Never run mysqld as root. 2\. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

在第一条记录（id 8）中，'mysql'出现一次，'tutorial'出现两次。有六条匹配记录为'mysql'，两条匹配记录为'tutorial'。当将这些值插入到多词搜索的排名公式中时，MySQL 命令行客户端返回了预期的排名值：

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));
+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
|                                    0.7405621541938003 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

注意

SELECT ... MATCH ... AGAINST语句和 MySQL 命令行客户端返回的排名值之间的轻微差异在前面的示例中有解释。

avatar
文章
阅读
粉丝