MySQL8-中文参考-二十二-
MySQL8 中文参考（二十二）
14.9.2 布尔全文搜索
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 命令行客户端返回的排名值之间的轻微差异在前面的示例中有解释。
14.9.3 使用查询扩展进行全文搜索
原文：
dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html
全文搜索支持查询扩展（特别是其变体“盲目的查询扩展”）。当搜索短语太短时，这通常是很有用的，这意味着用户依赖于全文搜索引擎缺乏的暗示知识。例如，一个搜索“数据库”的用户可能真正想要匹配“数据库”并返回“MySQL”、“Oracle”、“DB2”和“RDBMS”等短语。这是暗示的知识。
通过在搜索短语后添加WITH QUERY EXPANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION来启用盲目的查询扩展（也称为自动相关反馈）。它通过执行两次搜索来工作，第二次搜索的搜索短语是原始搜索短语与第一次搜索中最相关的几篇文档连接在一起。因此，如果这些文档中有一个包含“数据库”和“MySQL”这两个词，第二次搜索将找到包含“MySQL”这个词的文档，即使它们不包含“数据库”这个词。以下示例展示了这种差异：
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)
mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title | body |
+----+-----------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 3 | Optimizing MySQL | In this tutorial we show ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 4 | 1001 MySQL Tricks | 1\. Never run mysqld as root. 2\. ... |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)
另一个例子可能是搜索乔治·西梅诺关于梅格雷的书籍，当用户不确定如何拼写“梅格雷”时。在没有查询扩展的情况下，搜索“Megre and the reluctant witnesses”只会找到“Maigret and the Reluctant Witnesses”。而使用查询扩展的搜索会在第二次搜索时找到所有包含“Maigret”这个词的书籍。
注意
盲目的查询扩展往往会通过返回不相关的文档显著增加噪音，因此只有在搜索短语很短的情况下才使用它。
14.9.4 全文停用词
停用词列表是使用服务器字符集和排序规则（character_set_server和collation_server系统变量的值）加载和搜索全文查询的。如果停用词文件或用于全文索引或搜索的列的字符集或排序规则与character_set_server或collation_server不同，停用词查找可能会出现错误的命中或遗漏。
停用词查找的大小写敏感性取决于服务器排序规则。例如，如果排序规则是utf8mb4_0900_ai_ci，则查找是不区分大小写的，而如果排序规则是utf8mb4_0900_as_cs或utf8mb4_bin，则查找是区分大小写的。
-
InnoDB 搜索索引的停用词
-
MyISAM 搜索索引的停用词
InnoDB 搜索索引的停用词
InnoDB具有相对较短的默认停用词列表，因为来自技术、文学和其他来源的文档通常使用短词作为关键词或重要短语。例如，您可能搜索“to be or not to be”并期望得到一个合理的结果，而不是忽略所有这些单词。
要查看默认的InnoDB停用词列表，请查询信息模式INNODB_FT_DEFAULT_STOPWORD表。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.00 sec)
要为所有InnoDB表定义自己的停用词列表，请定义一个与INNODB_FT_DEFAULT_STOPWORD表结构相同的表，填充它的停用词，并在创建全文索引之前将innodb_ft_server_stopword_table选项的值设置为形式为*db_name*/*table_name*的值。停用词表必须有一个名为value的单个VARCHAR列。以下示例演示了为InnoDB创建和配置新的全局停用词表。
-- Create a new stopword table
mysql> CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)
-- Insert stopwords (for simplicity, a single stopword is used in this example)
mysql> INSERT INTO my_stopwords(value) VALUES ('Ishmael');
Query OK, 1 row affected (0.00 sec)
-- Create the table
mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
-- Insert data into the table
mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
-- Set the innodb_ft_server_stopword_table option to the new stopword table
mysql> SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';
Query OK, 0 rows affected (0.00 sec)
-- Create the full-text index (which rebuilds the table if no FTS_DOC_ID column is defined)
mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (1.17 sec)
Records: 0 Duplicates: 0 Warnings: 1
通过查询信息模式INNODB_FT_INDEX_TABLE表来验证指定的停用词（'Ishmael'）是否出现。
注意
默认情况下，长度小于 3 个字符或大于 84 个字符的单词不会出现在InnoDB全文搜索索引中。最大和最小单词长度值可通过innodb_ft_max_token_size和innodb_ft_min_token_size变量进行配置。此默认行为不适用于 ngram 解析器插件。 ngram 标记大小由ngram_token_size选项定义。
mysql> SET GLOBAL innodb_ft_aux_table='test/opening_lines';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 15;
+-----------+
| word |
+-----------+
| across |
| all |
| burn |
| buy |
| call |
| comes |
| dalloway |
| first |
| flowers |
| happened |
| herself |
| invisible |
| less |
| love |
| man |
+-----------+
15 rows in set (0.00 sec)
要逐表创建停用词列表，请创建其他停用词表，并在创建全文索引之前使用innodb_ft_user_stopword_table选项指定要使用的停用词表。
MyISAM搜索索引的停用词
如果character_set_server为ucs2、utf16、utf16le或utf32，则使用latin1加载和搜索停用词文件。
要覆盖MyISAM表的默认停用词列表，请设置ft_stopword_file系统变量。（参见第 7.1.8 节，“服务器系统变量”。）变量值应为包含停用词列表的文件的路径名，或空字符串以禁用停用词过滤。服务器会在数据目录中查找该文件，除非给定绝对路径名以指定不同目录。更改此变量的值或停用词文件的内容后，重新启动服务器并重建您的FULLTEXT索引。
停用词列表是自由格式的，用任何非字母数字字符（如换行符、空格或逗号）分隔停用词。下划线字符（_）和单引号（'）是单词的一部分。停用词列表的字符集是服务器的默认字符集；请参阅第 12.3.2 节，“服务器字符集和排序规则”。
以下列表显示了MyISAM搜索索引的默认停用词。在 MySQL 源分发中，您可以在storage/myisam/ft_static.c文件中找到此列表。
a's able about above according
accordingly across actually after afterwards
again against ain't all allow
allows almost alone along already
also although always am among
amongst an and another any
anybody anyhow anyone anything anyway
anyways anywhere apart appear appreciate
appropriate are aren't around as
aside ask asking associated at
available away awfully be became
because become becomes becoming been
before beforehand behind being believe
below beside besides best better
between beyond both brief but
by c'mon c's came can
can't cannot cant cause causes
certain certainly changes clearly co
com come comes concerning consequently
consider considering contain containing contains
corresponding could couldn't course currently
definitely described despite did didn't
different do does doesn't doing
don't done down downwards during
each edu eg eight either
else elsewhere enough entirely especially
et etc even ever every
everybody everyone everything everywhere ex
exactly example except far few
fifth first five followed following
follows for former formerly forth
four from further furthermore get
gets getting given gives go
goes going gone got gotten
greetings had hadn't happens hardly
has hasn't have haven't having
he he's hello help hence
her here here's hereafter hereby
herein hereupon hers herself hi
him himself his hither hopefully
how howbeit however i'd i'll
i'm i've ie if ignored
immediate in inasmuch inc indeed
indicate indicated indicates inner insofar
instead into inward is isn't
it it'd it'll it's its
itself just keep keeps kept
know known knows last lately
later latter latterly least less
lest let let's like liked
likely little look looking looks
ltd mainly many may maybe
me mean meanwhile merely might
more moreover most mostly much
must my myself name namely
nd near nearly necessary need
needs neither never nevertheless new
next nine no nobody non
none noone nor normally not
nothing novel now nowhere obviously
of off often oh ok
okay old on once one
ones only onto or other
others otherwise ought our ours
ourselves out outside over overall
own particular particularly per perhaps
placed please plus possible presumably
probably provides que quite qv
rather rd re really reasonably
regarding regardless regards relatively respectively
right said same saw say
saying says second secondly see
seeing seem seemed seeming seems
seen self selves sensible sent
serious seriously seven several shall
she should shouldn't since six
so some somebody somehow someone
something sometime sometimes somewhat somewhere
soon sorry specified specify specifying
still sub such sup sure
t's take taken tell tends
th than thank thanks thanx
that that's thats the their
theirs them themselves then thence
there there's thereafter thereby therefore
therein theres thereupon these they
they'd they'll they're they've think
third this thorough thoroughly those
though three through throughout thru
thus to together too took
toward towards tried tries truly
try trying twice two un
under unfortunately unless unlikely until
unto up upon us use
used useful uses using usually
value various very via viz
vs want wants was wasn't
way we we'd we'll we're
we've welcome well went were
weren't what what's whatever when
whence whenever where where's whereafter
whereas whereby wherein whereupon wherever
whether which while whither who
who's whoever whole whom whose
why will willing wish with
within without won't wonder would
wouldn't yes yet you you'd
you'll you're you've your yours
yourself yourselves zero
14.9.5 全文限制
原文：
dev.mysql.com/doc/refman/8.0/en/fulltext-restrictions.html
-
仅支持对
InnoDB和MyISAM表进行全文搜索。 -
不支持对分区表进行全文搜索。请参阅第 26.6 节“分区的限制和限制”。
-
大多数多字节字符集都可以与全文搜索一起使用。唯一的例外是对于 Unicode，可以使用
utf8mb3或utf8mb4字符集，但不能使用ucs2字符集。虽然无法使用ucs2列上的FULLTEXT索引，但可以在没有此类索引的ucs2列上执行IN BOOLEAN MODE搜索。utf8mb3的备注也适用于utf8mb4，ucs2的备注也适用于utf16、utf16le和utf32。 -
汉语和日语等表意语言没有词分隔符。因此，内置全文解析器无法确定这些语言中单词的起始和结束位置。
提供了支持中文、日文和韩文（CJK）的基于字符的 ngram 全文解析器，以及支持日文的基于词的 MeCab 解析器插件，可用于
InnoDB和MyISAM表。 -
尽管支持在单个表中使用多个字符集，但
FULLTEXT索引中的所有列必须使用相同的字符集和排序规则。 -
MATCH()列列表必须与表的某个FULLTEXT索引定义中的列列表完全匹配，除非这个MATCH()在MyISAM表上是IN BOOLEAN MODE。对于MyISAM表，布尔模式搜索可以在非索引列上进行，尽管可能会很慢。 -
AGAINST()的参数必须是在查询评估期间保持不变的字符串值。例如，表列就不符合要求，因为每行可能不同。截至 MySQL 8.0.28 版本，
MATCH()的参数不能使用汇总列。 -
对于
FULLTEXT搜索，索引提示比非FULLTEXT搜索更受限制。请参阅第 10.9.4 节“索引提示”。 -
对于
InnoDB，涉及具有全文索引的列的所有 DML 操作（INSERT、UPDATE、DELETE）在事务提交时处理。例如，对于INSERT操作，插入的字符串被标记化并分解为单词。当事务提交时，这些单词将被添加到全文索引表中。因此，全文搜索仅返回已提交的数据。 -
'%' 字符不是全文搜索的支持通配符字符。
14.9.6 调整 MySQL 全文搜索
原文：
dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html
MySQL 的全文搜索功能有很少的用户可调参数。如果你有 MySQL 源码分发，你可以更多地控制全文搜索行为，因为一些更改需要修改源代码。参见 Section 2.8, “从源代码安装 MySQL”。
全文搜索经过精心调整以提高效果。在大多数情况下修改默认行为实际上可能会降低效果。不要修改 MySQL 源码，除非你知道你在做什么。
本节描述的大多数全文变量必须在服务器启动时设置。更改它们需要重新启动服务器；不能在服务器运行时修改。
一些变量更改需要重建表中的FULLTEXT索引。如何执行此操作将在本节后面给出。
-
配置最小和最大单词长度
-
配置自然语言搜索阈值
-
修改布尔全文搜索运算符
-
字符集修改
-
重建 InnoDB 全文索引
-
优化 InnoDB 全文索引
-
重建 MyISAM 全文索引
配置最小和最大单词长度
要索引的单词的最小和最大长度由 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 选项定义。
在更改任何这些选项后，重新构建你的FULLTEXT索引以使更改生效。例如，要使两个字符的单词可搜索，你可以在选项文件中加入以下行：
[mysqld]
innodb_ft_min_token_size=2
ft_min_word_len=2
然后重新启动服务器并重建您的FULLTEXT索引。对于MyISAM表，请注意以下有关重建MyISAM全文索引的说明中关于myisamchk的备注。
配置自然语言搜索阈值
对于MyISAM搜索索引，自然语言搜索的 50%阈值取决于所选择的特定加权方案。要禁用它，请查找storage/myisam/ftdefs.h中的以下行：
#define GWS_IN_USE GWS_PROB
将该行更改为：
#define GWS_IN_USE GWS_FREQ
然后重新编译 MySQL。在这种情况下，无需重建索引。
注意
通过进行此更改，您严重降低了 MySQL 为MATCH()函数提供充分相关性值的能力。如果您真的需要搜索这样的常见词，最好使用IN BOOLEAN MODE进行搜索，该模式不遵守 50%的阈值。
修改布尔全文搜索运算符
要更改在MyISAM表上用于布尔全文搜索的运算符，请设置ft_boolean_syntax系统变量。（InnoDB没有相应的设置。）此变量可以在服务器运行时更改，但您必须具有足够的权限来设置全局系统变量（请参阅第 7.1.9.1 节，“系统变量权限”）。在这种情况下，不需要重建索引。
字符集修改
对于内置全文解析器，您可以通过以下列表中描述的几种方式更改被视为单词字符的字符集。进行修改后，重新为包含任何FULLTEXT索引的每个表重建索引。假设您希望将连字符字符（'-'）视为单词字符。使用以下方法之一：
-
修改 MySQL 源代码：在
storage/innobase/handler/ha_innodb.cc（对于InnoDB）或storage/myisam/ftdefs.h（对于MyISAM）中查看true_word_char()和misc_word_char()宏。将'-'添加到其中一个宏中，然后重新编译 MySQL。 -
修改字符集文件：这不需要重新编译。
true_word_char()宏使用“字符类型”表来区分字母和数字与其他字符。 您可以编辑一个字符集 XML 文件中的<ctype><map>数组的内容，以指定'-'是一个“字母”。然后使用给定的字符集为您的FULLTEXT索引。有关<ctype><map>数组格式的信息，请参阅第 12.13.1 节，“字符定义数组”。 -
为使用索引列的字符集添加新的排序规则，并修改列以使用该排序规则。有关添加排序规则的一般信息，请参见 Section 12.14, “Adding a Collation to a Character Set”。有关全文索引的特定示例，请参见 Section 14.9.7, “Adding a User-Defined Collation for Full-Text Indexing”。
重建 InnoDB 全文索引
要使更改生效，必须在修改以下任一全文索引变量后重建FULLTEXT索引：innodb_ft_min_token_size; innodb_ft_max_token_size; innodb_ft_server_stopword_table; innodb_ft_user_stopword_table; innodb_ft_enable_stopword; ngram_token_size。修改innodb_ft_min_token_size、innodb_ft_max_token_size或ngram_token_size需要重新启动服务器。
要为InnoDB表重建FULLTEXT索引，请使用ALTER TABLE与DROP INDEX和ADD INDEX选项，以删除并重新创建每个索引。
优化 InnoDB 全文索引
在具有全文索引的表上运行OPTIMIZE TABLE会重建全文索引，删除已删除的文档 ID，并在可能的情况下 consololidating 多个相同单词的条目。
要优化全文索引，请启用innodb_optimize_fulltext_only并运行OPTIMIZE TABLE。
mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> OPTIMIZE TABLE opening_lines;
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| test.opening_lines | optimize | status | OK |
+--------------------+----------+----------+----------+
1 row in set (0.01 sec)
为了避免在大表上进行全文索引的长时间重建，您可以使用innodb_ft_num_word_optimize选项分阶段执行优化。innodb_ft_num_word_optimize选项定义了每次运行OPTIMIZE TABLE时优化的单词数。默认设置为 2000，这意味着每次运行OPTIMIZE TABLE时会优化 2000 个单词。后续的OPTIMIZE TABLE操作将从前一个OPTIMIZE TABLE操作结束的地方继续。
重建 MyISAM 全文索引
如果您修改影响索引的全文变量（ft_min_word_len、ft_max_word_len或ft_stopword_file），或者更改停用词文件本身，则在进行更改并重新启动服务器后，必须重建您的FULLTEXT索引。
要重建MyISAM表的FULLTEXT索引，只需进行QUICK修复操作即可：
mysql> REPAIR TABLE *tbl_name* QUICK;
或者，如刚才所述使用ALTER TABLE。在某些情况下，这可能比修复操作更快。
每个包含任何FULLTEXT索引的表必须按照刚才展示的方式进行修复。否则，对该表的查询可能会产生不正确的结果，并且对表的修改会导致服务器将表视为损坏并需要修复。
如果您使用myisamchk执行修改MyISAM表索引的操作（如修复或分析），FULLTEXT索引将使用默认的全文参数值进行重建，包括最小单词长度、最大单词长度和停用词文件，除非您另有规定。这可能导致查询失败。
问题出现在这些参数仅由服务器知道。它们不存储在MyISAM索引文件中。如果您已修改了服务器使用的最小或最大单词长度或停用词文件值，为了避免问题，请为myisamchk指定与mysqld使用的相同的ft_min_word_len、ft_max_word_len和ft_stopword_file值。例如，如果您将最小单词长度设置为 3，您可以像这样使用myisamchk修复表：
myisamchk --recover --ft_min_word_len=3 *tbl_name*.MYI
为了确保myisamchk和服务器使用相同的全文参数值，将每个值放在选项文件的[mysqld]和[myisamchk]部分中：
[mysqld]
ft_min_word_len=3
[myisamchk]
ft_min_word_len=3
用于MyISAM表索引修改的一种替代方法是使用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE或ALTER TABLE语句。这些语句由服务器执行，服务器知道要使用的正确全文参数值。
14.9.7 为全文索引添加用户定义的排序规则
原文：
dev.mysql.com/doc/refman/8.0/en/full-text-adding-collation.html
警告
用户定义的排序规则已被弃用；您应该期望在未来的 MySQL 版本中删除对它们的支持。从 MySQL 8.0.33 开始，服务器对任何 SQL 语句中使用 COLLATE *user_defined_collation* 都会发出警告；当服务器以 --collation-server 设置为用户定义的排序规则的名称时，也会发出警告。
本节描述如何为使用内置全文解析器进行全文搜索添加用户定义的排序规则。示例排序规则类似于 latin1_swedish_ci，但将 '-' 字符视为字母而不是标点符号，以便将其索引为单词字符。有关添加排序规则的一般信息在 Section 12.14, “Adding a Collation to a Character Set” 中给出；假定您已经阅读并熟悉了涉及的文件。
要为全文索引添加排序规则，请使用以下过程。这里的说明添加了一个简单字符集的排序规则，如 Section 12.14, “Adding a Collation to a Character Set” 中所讨论的，可以使用描述字符集属性的配置文件来创建。对于像 Unicode 这样的复杂字符集，请使用描述字符集属性的 C 源文件创建排序规则。
-
在
Index.xml文件中添加一个排序规则。用户定义的排序规则的允许 ID 范围在 Section 12.14.2, “Choosing a Collation ID” 中给出。ID 必须未使用，因此如果系统中已经使用了 ID 1025，则选择一个不同的值。<charset name="latin1"> ... <collation name="latin1_fulltext_ci" id="1025"/> </charset> -
在
latin1.xml文件中声明排序规则的排序顺序。在这种情况下，排序顺序可以从latin1_swedish_ci复制：<collation name="latin1_fulltext_ci"> <map> 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F 40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F 50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F 60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F 50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF 41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49 44 4E 4F 4F 4F 4F 5D D7 D8 55 55 55 59 59 DE DF 41 41 41 41 5C 5B 5C 43 45 45 45 45 49 49 49 49 44 4E 4F 4F 4F 4F 5D F7 D8 55 55 55 59 59 DE FF </map> </collation> -
修改
latin1.xml中的ctype数组。将对应于'-'字符的代码 0x2D（即连字符的代码）的值从 10（标点符号）更改为 01（大写字母）。在下面的数组中，这是从下面第四行开始，从末尾数第三个值的元素。<ctype> <map> 00 20 20 20 20 20 20 20 20 20 28 28 28 28 28 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 48 10 10 10 10 10 10 10 10 10 10 10 10 *01* 10 10 84 84 84 84 84 84 84 84 84 84 10 10 10 10 10 10 10 81 81 81 81 81 81 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 10 10 10 10 10 10 82 82 82 82 82 82 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 10 10 10 10 20 10 00 10 02 10 10 10 10 10 10 01 10 01 00 01 00 00 10 10 10 10 10 10 10 10 10 02 10 02 00 02 01 48 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 10 01 01 01 01 01 01 01 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 10 02 02 02 02 02 02 02 02 </map> </ctype> -
重新启动服务器。
-
要使用新的排序规则，将其包含在要使用它的列的定义中：
mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.13 sec) mysql> CREATE TABLE t1 ( a TEXT CHARACTER SET latin1 COLLATE latin1_fulltext_ci, FULLTEXT INDEX(a) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.47 sec) -
测试排序规则，以验证连字符被视为单词字符：
mysql> INSERT INTO t1 VALUEs ('----'),('....'),('abcd'); Query OK, 3 rows affected (0.22 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1 WHERE MATCH a AGAINST ('----' IN BOOLEAN MODE); +------+ | a | +------+ | ---- | +------+ 1 row in set (0.00 sec)
14.9.8 ngram 全文解析器
原文：
dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html
内置的 MySQL 全文解析器使用单词之间的空格作为分隔符来确定单词的起始和结束位置，这在处理不使用单词分隔符的表意语言时存在限制。为了解决这个限制，MySQL 提供了一个支持中文、日文和韩文（CJK）的 ngram 全文解析器。ngram 全文解析器支持与InnoDB和MyISAM一起使用。
注意
MySQL 还为日语提供了一个 MeCab 全文解析器插件，将文档标记为有意义的单词。有关更多信息，请参见第 14.9.9 节，“MeCab 全文解析器插件”。
ngram 是从给定文本序列中的连续n个字符序列。ngram 解析器将文本序列标记为连续的n个字符序列。例如，您可以使用 ngram 全文解析器为不同的n值对“abcd”进行标记。
n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'
ngram 全文解析器是一个内置的服务器插件。与其他内置的服务器插件一样，在服务器启动时会自动加载。
描述在第 14.9 节，“全文搜索函数”中的全文搜索语法适用于 ngram 解析器插件。本节描述了解析行为的差异。除了最小和最大单词长度选项（innodb_ft_min_token_size，innodb_ft_max_token_size，ft_min_word_len，ft_max_word_len)之外，也适用于与全文搜索相关的配置选项。
配置 ngram Token 大小
ngram 解析器具有默认的 ngram token 大小为 2（bigram）。例如，使用大小为 2 的 token，ngram 解析器将字符串“abc def”解析为四个 token：“ab”，“bc”，“de”和“ef”。
ngram token 大小可通过ngram_token_size配置选项进行配置，最小值为 1，最大值为 10。
通常，ngram_token_size 被设置为您想要搜索的最大标记的大小。如果您只打算搜索单个字符，请将 ngram_token_size 设置为 1。较小的标记大小会产生较小的全文搜索索引，并且搜索速度更快。如果您需要搜索由多个字符组成的单词，请相应地设置 ngram_token_size。例如，“生日快乐”在简体中文中是“Happy Birthday”，其中“生日”是“birthday”，“快乐”翻译为“happy”。要搜索这样的两个字符单词，将 ngram_token_size 设置为 2 或更高的值。
作为只读变量，ngram_token_size 只能作为启动字符串的一部分或在配置文件中设置：
-
启动字符串：
mysqld --ngram_token_size=2 -
配置文件：
[mysqld] ngram_token_size=2
注意
对于使用 ngram 解析器的 FULLTEXT 索引，以下最小和最大单词长度配置选项将被忽略：innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, 和 ft_max_word_len。
创建使用 ngram 解析器的 FULLTEXT 索引
要创建使用 ngram 解析器的 FULLTEXT 索引，请在 CREATE TABLE, ALTER TABLE, 或 CREATE INDEX 中指定 WITH PARSER ngram。
以下示例演示了创建具有 ngram FULLTEXT 索引的表，插入示例数据（简体中文文本）以及在信息模式 INNODB_FT_INDEX_CACHE 表中查看标记化数据。
mysql> USE test;
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;
mysql> SET NAMES utf8mb4;
INSERT INTO articles (title,body) VALUES
('数据库管理','在本教程中我将向你展示如何管理数据库'),
('数据库应用开发','学习开发数据库应用程序');
mysql> SET GLOBAL innodb_ft_aux_table="test/articles";
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
要向现有表添加 FULLTEXT 索引，可以使用 ALTER TABLE 或 CREATE INDEX。例如：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
# Or:
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
ngram 解析器空格处理
ngram 解析器在解析时消除空格。例如：
-
“ab cd” 被解析为 “ab”, “cd”
-
“a bc” 被解析为 “bc”
ngram 解析器停用词处理
内置的 MySQL 全文解析器将单词与停用词列表中的条目进行比较。如果一个词等于停用词列表中的条目，则该词将从索引中排除。对于 ngram 解析器，停用词处理方式不同。它不是排除等于停用词列表中条目的标记，而是排除包含停用词的标记。例如，假设ngram_token_size=2，包含“a,b”的文档被解析为“a,”和“,b”。如果逗号（“,”）被定义为停用词，那么“a,”和“,b”都将因为包含逗号而被排除在索引之外。
默认情况下，ngram 解析器使用默认停用词列表，其中包含一组英语停用词。对于适用于中文、日文或韩文的停用词列表，您必须创建自己的停用词列表。有关创建停用词列表的信息，请参见第 14.9.4 节，“全文停用词”。
长度大于ngram_token_size的停用词将被忽略。
ngram 解析器术语搜索
对于自然语言模式搜索，搜索词被转换为 ngram 词的并集。例如，字符串“abc”（假设ngram_token_size=2）被转换为“ab bc”。给定两个文档，一个包含“ab”，另一个包含“abc”，搜索词“ab bc”匹配两个文档。
对于布尔模式搜索，搜索词被转换为 ngram 短语搜索。例如，字符串'abc'（假设ngram_token_size=2）被转换为'“ab bc”'。给定两个文档，一个包含'ab'，另一个包含'abc'，搜索短语'“ab bc”'仅匹配包含'abc'的文档。
ngram 解析器通配符搜索
因为 ngram FULLTEXT索引仅包含 ngrams，并不包含有关术语开头的信息，通配符搜索可能会返回意外结果。以下行为适用于使用 ngram FULLTEXT搜索索引进行通配符搜索的情况：
-
如果通配符搜索的前缀项短于 ngram 标记大小，查询将返回所有包含以前缀项开头的 ngram 标记的索引行。例如，假设
ngram_token_size=2，搜索“a*”将返回所有以“a”开头的行。 -
如果通配符搜索的前缀项长于 ngram 标记大小，则前缀项被转换为 ngram 短语，通配符操作符被忽略。例如，假设
ngram_token_size=2，一个“abc*”通配符搜索被转换为“ab bc”。
ngram 解析器短语搜索
短语搜索被转换为 ngram 短语搜索。例如，搜索短语“abc”被转换为“ab bc”，返回包含“abc”和“ab bc”的文档。
搜索短语“abc def”被转换为“ab bc de ef”，返回包含“abc def”和“ab bc de ef”的文档。不返回包含“abcdef”的文档。
14.9.9 MeCab 全文解析器插件
原文：
dev.mysql.com/doc/refman/8.0/en/fulltext-search-mecab.html
MySQL 内置的全文解析器使用单词之间的空格作为分隔符来确定单词的起始和结束位置，这在处理不使用单词分隔符的表意语言时存在限制。为了解决这个问题，MySQL 为日语提供了 MeCab 全文解析器插件。MeCab 全文解析器插件支持与InnoDB和MyISAM一起使用。
注意
MySQL 还提供了支持日语的 ngram 全文解析器插件。有关更多信息，请参见第 14.9.8 节“ngram 全文解析器”。
MeCab 全文解析器插件是用于日语的全文解析器插件，将文本序列标记为有意义的单词。例如，MeCab 将“データベース管理”（“数据库管理”）标记为“データベース”（“数据库”）和“管理”（“管理”）。相比之下，ngram 全文解析器将文本标记为连续的n个字符序列，其中n表示 1 到 10 之间的数字。
除了将文本标记为有意义的单词外，MeCab 索引通常比 ngram 索引小，并且 MeCab 全文搜索通常更快。一个缺点是与 ngram 全文解析器相比，MeCab 全文解析器可能需要更长的时间来标记文档。
第 14.9 节“全文搜索函数”中描述的全文搜索语法适用于 MeCab 解析器插件。本节描述了解析行为的差异。全文搜索相关的配置选项也适用。
有关 MeCab 解析器的更多信息，请参考 Github 上的MeCab: Yet Another Part-of-Speech and Morphological Analyzer项目。
安装 MeCab 解析器插件
MeCab 解析器插件需要mecab和mecab-ipadic。
在支持的 Fedora、Debian 和 Ubuntu 平台上（除了 Ubuntu 12.04，系统中的mecab版本太旧），如果mecab安装在默认位置，则 MySQL 会动态链接到系统的mecab安装。在其他支持的类 Unix 平台上，libmecab.so静态链接在libpluginmecab.so中，该文件位于 MySQL 插件目录中。mecab-ipadic包含在 MySQL 二进制文件中，位于*MYSQL_HOME*\lib\mecab中。
你可以使用本机包管理工具（在 Fedora、Debian 和 Ubuntu 上）安装mecab和mecab-ipadic，也可以从源代码构建mecab和mecab-ipadic。有关使用本机包管理工具安装mecab和mecab-ipadic的信息，请参见从二进制发行版安装 MeCab（可选）。如果你想从源代码构建mecab和mecab-ipadic，请参见从源代码构建 MeCab（可选）。
在 Windows 上，libmecab.dll位于 MySQL 的bin目录中。mecab-ipadic位于*MYSQL_HOME*/lib/mecab中。
要安装和配置 MeCab 解析器插件，请执行以下步骤：
-
在 MySQL 配置文件中，将
mecab_rc_file配置选项设置为mecabrc配置文件的位置，该文件是 MeCab 的配置文件。如果你使用 MySQL 分发的 MeCab 包，mecabrc文件位于MYSQL_HOME/lib/mecab/etc/。[mysqld] loose-mecab-rc-file=MYSQL_HOME/lib/mecab/etc/mecabrcloose前缀是一个选项修饰符。在安装 MeCab 解析器插件之前，MySQL 不会识别mecab_rc_file选项，但必须在尝试安装 MeCab 解析器插件之前设置它。loose前缀允许你重新启动 MySQL，而不会因为无法识别的变量而遇到错误。如果你使用自己的 MeCab 安装，或者从源代码构建 MeCab，
mecabrc配置文件的位置可能会有所不同。有关 MySQL 配置文件及其位置的信息，请参见 Section 6.2.2.2，“使用选项文件”。
-
同样在 MySQL 配置文件中，将最小标记大小设置为 1 或 2，这是与 MeCab 解析器一起使用时推荐的值。对于
InnoDB表，最小标记大小由innodb_ft_min_token_size配置选项定义，默认值为 3。对于MyISAM表，最小标记大小由ft_min_word_len定义，默认值为 4。[mysqld] innodb_ft_min_token_size=1 -
修改
mecabrc配置文件以指定你想使用的字典。MySQL 二进制文件中分发的mecab-ipadic包含三个字典（ipadic_euc-jp、ipadic_sjis和ipadic_utf-8）。MySQL 打包的mecabrc配置文件包含类似以下条目：dicdir = /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp要使用
ipadic_utf-8字典，例如，修改条目如下：dicdir=*MYSQL_HOME*/lib/mecab/dic/ipadic_utf-8如果您正在使用自己的 MeCab 安装或已经从源代码构建了 MeCab，那么
mecabrc文件中的默认dicdir条目可能会有所不同，字典及其位置也会不同。注意
安装完 MeCab 解析器插件后，您可以使用
mecab_charset状态变量查看与 MeCab 一起使用的字符集。MySQL 二进制文件提供的三个 MeCab 字典支持以下字符集。-
ipadic_euc-jp字典支持ujis和eucjpms字符集。 -
ipadic_sjis字典支持sjis和cp932字符集。 -
ipadic_utf-8字典支持utf8mb3和utf8mb4字符集。
mecab_charset仅报告第一个支持的字符集。例如，ipadic_utf-8字典支持utf8mb3和utf8mb4。当使用此字典时，mecab_charset总是报告utf8。 -
-
重新启动 MySQL。
-
安装 MeCab 解析器插件：
使用
INSTALL PLUGIN安装 MeCab 解析器插件。插件名称为mecab，共享库名称为libpluginmecab.so。有关安装插件的其他信息，请参见 Section 7.6.1, “Installing and Uninstalling Plugins”。INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';安装完成后，MeCab 解析器插件会在每次正常 MySQL 重启时加载。
-
使用
SHOW PLUGINS语句验证 MeCab 解析器插件是否已加载。mysql> SHOW PLUGINS;mecab插件应该出现在插件列表中。
创建使用 MeCab 解析器的 FULLTEXT 索引
要创建一个使用 mecab 解析器的FULLTEXT索引，请在CREATE TABLE、ALTER TABLE或CREATE INDEX中指定WITH PARSER ngram。
此示例演示了创建带有mecab FULLTEXT索引的表，插入示例数据，并在 Information Schema INNODB_FT_INDEX_CACHE表中查看标记化数据：
mysql> USE test;
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER mecab
) ENGINE=InnoDB CHARACTER SET utf8mb4;
mysql> SET NAMES utf8mb4;
mysql> INSERT INTO articles (title,body) VALUES
('データベース管理','このチュートリアルでは、私はどのようにデータベースを管理する方法を紹介します'),
('データベースアプリケーション開発','データベースアプリケーションを開発することを学ぶ');
mysql> SET GLOBAL innodb_ft_aux_table="test/articles";
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
要向现有表添加FULLTEXT索引，可以使用ALTER TABLE或CREATE INDEX。例如：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER mecab;
# Or:
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER mecab;
MeCab 解析器空格处理
MeCab 解析器在查询字符串中使用空格作为分隔符。例如，MeCab 解析器将データベース管理标记为データベース和管理。
MeCab 解析器停用词处理
默认情况下，MeCab 解析器使用默认的停用词列表，其中包含一小部分英文停用词。要使用适用于日语的停用词列表，您必须创建自己的停用词列表。有关创建停用词列表的信息，请参见 第 14.9.4 节，“全文停用词”。
MeCab 解析器术语搜索
对于自然语言模式搜索，搜索词被转换为标记的并集。例如，データベース管理 被转换为 データベース 管理。
SELECT COUNT(*) FROM articles WHERE MATCH(title,body) AGAINST('データベース管理' IN NATURAL LANGUAGE MODE);
对于布尔模式搜索，搜索词被转换为搜索短语。例如，データベース管理 被转换为 データベース 管理。
SELECT COUNT(*) FROM articles WHERE MATCH(title,body) AGAINST('データベース管理' IN BOOLEAN MODE);
MeCab 解析器通配符搜索
通配符搜索词不被标记。对前缀 データベース管理* 进行搜索时，会在前缀 データベース管理 上执行搜索。
SELECT COUNT(*) FROM articles WHERE MATCH(title,body) AGAINST('データベース*' IN BOOLEAN MODE);
MeCab 解析器短语搜索
短语被标记。例如，データベース管理 被标记为 データベース 管理。
SELECT COUNT(*) FROM articles WHERE MATCH(title,body) AGAINST('"データベース管理"' IN BOOLEAN MODE);
从二进制发行版安装 MeCab（可选）
本节描述了如何使用本机软件包管理工具从二进制发行版安装 mecab 和 mecab-ipadic。例如，在 Fedora 上，您可以使用 Yum 执行安装：
yum mecab-devel
在 Debian 或 Ubuntu 上，您可以执行 APT 安装：
apt-get install mecab
apt-get install mecab-ipadic
从源代码安装 MeCab（可选）
如果您想要从源代码构建 mecab 和 mecab-ipadic，以下是基本的安装步骤。有关更多信息，请参考 MeCab 文档。
-
从
taku910.github.io/mecab/#download下载mecab和mecab-ipadic的 tar.gz 软件包。截至 2016 年 2 月，最新可用的软件包是mecab-0.996.tar.gz和mecab-ipadic-2.7.0-20070801.tar.gz。 -
安装
mecab：tar zxfv mecab-0.996.tar cd mecab-0.996 ./configure make make check su make install -
安装
mecab-ipadic：tar zxfv mecab-ipadic-2.7.0-20070801.tar cd mecab-ipadic-2.7.0-20070801 ./configure make su make install -
使用
WITH_MECABCMake 选项编译 MySQL。如果您已将mecab和mecab-ipadic安装到默认位置，请将WITH_MECAB选项设置为system。-DWITH_MECAB=system如果您定义了自定义安装目录，请将
WITH_MECAB设置为自定义目录。例如：-DWITH_MECAB=/path/to/mecab
14.10 转换函数和运算符
表 14.15 转换函数和运算符
| 名称 | 描述 | 已弃用 |
|---|---|---|
BINARY |
将字符串转换为二进制字符串 | 8.0.27 |
CAST() |
将值转换为特定类型 | |
CONVERT() |
将值转换为特定类型 |
转换函数和运算符使值从一种数据类型转换为另一种数据类型。
-
转换函数和运算符描述
-
字符集转换
-
用于字符串比较的字符集转换
-
空间类型的转换操作
-
转换操作的其他用途
转换函数和运算符描述
-
BINARYexprBINARY运算符将表达式转换为二进制字符串（具有binary字符集和binary校对的字符串）。BINARY的常见用途是通过使用数值字节值而不是逐个字符来进行字符字符串比较。BINARY运算符还导致比较中的尾随空格具有重要意义。有关binary字符集和非二进制字符集的_bin校对之间的区别的信息，请参见 第 12.8.5 节，“二进制校对与 _bin 校对的比较”。BINARY运算符在 MySQL 8.0.27 中已弃用，并且您应该期望在将来的 MySQL 版本中将其移除。请改用CAST(... AS BINARY)。mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; -> OK mysql> SELECT 'a' = 'A'; -> 1 mysql> SELECT BINARY 'a' = 'A'; -> 0 mysql> SELECT 'a' = 'a '; -> 1 mysql> SELECT BINARY 'a' = 'a '; -> 0在比较中，
BINARY影响整个操作；可以在任一操作数之前使用，结果相同。要将字符串表达式转换为二进制字符串，以下结构是等效的：
CONVERT(*expr* USING BINARY) CAST(*expr* AS BINARY) BINARY *expr*如果一个值是字符串文字，可以通过使用
_binary字符集引导符将其指定为二进制字符串而无需转换：mysql> SELECT 'a' = 'A'; -> 1 mysql> SELECT _binary 'a' = 'A'; -> 0有关引导符的信息，请参见 第 12.3.8 节，“字符集引导符”。
在表达式中，
BINARY运算符的效果与字符列定义中的BINARY属性不同。对于使用BINARY属性定义的字符列，MySQL 分配表默认字符集和该字符集的二进制 (_bin) 校对。每个非二进制字符集都有一个_bin校对。例如，如果表默认字符集是utf8mb4，那么这两个列定义是等效的：CHAR(10) BINARY CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin在
CHAR、VARCHAR或TEXT列的定义中使用CHARACTER SET binary会导致该列被视为相应的二进制字符串数据类型。例如，以下定义对是等效的：CHAR(10) CHARACTER SET binary BINARY(10) VARCHAR(10) CHARACTER SET binary VARBINARY(10) TEXT CHARACTER SET binary BLOB如果从 mysql 客户端调用
BINARY，二进制字符串将使用十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
CAST(*expr* AS *type* [ARRAY])CAST(*timestamp_value* AT TIME ZONE *timezone_specifier* AS DATETIME[(*precision*)])timezone_specifier: [INTERVAL] '+00:00' | 'UTC'使用
CAST(*expr* AS *type*语法，CAST()函数接受任何类型的表达式，并生成指定类型的结果值。这个操作也可以表示为CONVERT(*expr*, *type*)，它是等效的。如果expr是NULL，CAST()返回NULL。允许使用以下
type值：-
BINARY[(*N*)]生成一个具有
VARBINARY数据类型的字符串，除非表达式expr为空（长度为零），结果类型为BINARY(0)。如果给定可选长度N，BINARY(*N*)使转换不超过N个字节的参数。长度小于N的值用0x00字节填充到长度为N。如果未给出可选长度N，MySQL 从表达式计算最大长度。如果提供或计算的长度大于内部阈值，则结果类型为BLOB。如果长度仍然太长，则结果类型为LONGBLOB。有关将
BINARY转换影响比较的描述，请参阅 Section 13.3.3, “The BINARY and VARBINARY Types”。 -
CHAR[(*N*)] [*charset_info*]生成一个具有
VARCHAR数据类型的字符串，除非表达式expr为空（长度为零），在这种情况下，结果类型为CHAR(0)。如果给定了可选长度N，CHAR(*N*)使转换不超过N个字符的参数。对于长度小于N的值，不会进行填充。如果未给出可选长度N，MySQL 会从表达式计算最大长度。如果提供或计算的长度大于内部阈值，则结果类型为TEXT。如果长度仍然太长，则结果类型为LONGTEXT。没有
charset_info子句时，CHAR生成一个具有默认字符集的字符串。要明确指定字符集，允许使用以下charset_info值：-
CHARACTER SET *charset_name*：生成具有给定字符集的字符串。 -
ASCII：CHARACTER SET latin1的简写。 -
UNICODE：CHARACTER SET ucs2的简写。
在所有情况下，字符串具有字符集默认排序规则。
-
-
日期生成一个
日期值。 -
DATETIME[(*M*)]生成一个
日期时间值。如果给定了可选M值，则指定了小数秒精度。 -
DECIMAL[(*M*[,*D*])]生成一个
DECIMAL值。如果给定了可选的M和D值，则它们指定了数字的最大位数（精度）和小数点后的位数（标度）。如果省略了D，则假定为 0。如果省略了M，则假定为 10。 -
DOUBLE生成一个
DOUBLE结果。在 MySQL 8.0.17 中添加。 -
FLOAT[(*p*)]如果未指定精度
p，则生成一个FLOAT类型的结果。如果提供了p并且 0 <= <p<= 24，则结果为FLOAT类型。如果 25 <=p<= 53，则结果为DOUBLE类型。如果p< 0 或p> 53，则返回错误。在 MySQL 8.0.17 中添加。 -
JSON生成一个
JSON值。有关值在JSON和其他类型之间转换规则的详细信息，请参阅 JSON 值的比较和排序。 -
NCHAR[(*N*)]类似于
CHAR，但生成具有国家字符集的字符串。请参阅第 12.3.7 节，“国家字符集”。与
CHAR不同，NCHAR不允许指定尾随字符集信息。 -
REAL产生一个
REAL类型的结果。如果启用了REAL_AS_FLOATSQL 模式，则实际上是FLOAT；否则结果为DOUBLE类型。 -
SIGNED [INTEGER]产生一个带符号的
BIGINT值。 -
spatial_type截至 MySQL 8.0.24 版本，
CAST()和CONVERT()支持将几何值从一种空间类型转换为另一种空间类型，适用于某些空间类型的组合。有关详细信息，请参见空间类型的转换操作。 -
TIME[(*M*)]产生一个
TIME值。如果给定了可选的M值，则指定小数秒精度。 -
UNSIGNED [INTEGER]产生一个无符号的
BIGINT值。 -
YEAR产生一个
YEAR值。在 MySQL 8.0.22 版本中添加。这些规则适用于转换为YEAR：-
对于范围在 1901-2155（包括）之间的四位数，或者可以解释为此范围内四位数的字符串，返回相应的
YEAR值。 -
对于由一到两位数字组成的数字，或者可以解释为这样一个数字的字符串，返回如下
YEAR值：-
如果数字在 1-69（包括）范围内，则加上 2000 并返回总和。
-
如果数字在 70-99（包括）范围内，则加上 1900 并返回总和。
-
-
对于评估为 0 的字符串，返回 2000。
-
对于数字 0，返回 0。
-
对于
DATE、DATETIME或TIMESTAMP值，返回值的YEAR部分。对于TIME值，返回当前年份。如果不指定
TIME参数的类型，则可能会得到与预期不同的结果，如下所示：mysql> SELECT CAST("11:35:00" AS YEAR), CAST(TIME "11:35:00" AS YEAR); +--------------------------+-------------------------------+ | CAST("11:35:00" AS YEAR) | CAST(TIME "11:35:00" AS YEAR) | +--------------------------+-------------------------------+ | 2011 | 2021 | +--------------------------+-------------------------------+ -
如果参数是
DECIMAL、DOUBLE、DECIMAL或REAL类型，则将值四舍五入到最接近的整数，然后尝试使用整数值的规则将值转换为YEAR，如下所示：mysql> SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR); +-----------------------+-----------------------+ | CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) | +-----------------------+-----------------------+ | 1944 | 1945 | +-----------------------+-----------------------+ mysql> SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR); +---------------------+---------------------+ | CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) | +---------------------+---------------------+ | 2066 | 2067 | +---------------------+---------------------+ -
不能将类型为
GEOMETRY的参数转换为YEAR。 -
对于无法成功转换为
YEAR的值，返回NULL。
包含必须在转换之前截断的非数字字符的字符串值会引发警告，如下所示：
mysql> SELECT CAST("1979aaa" AS YEAR); +-------------------------+ | CAST("1979aaa" AS YEAR) | +-------------------------+ | 1979 | +-------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' | +---------+------+-------------------------------------------+ -
在 MySQL 8.0.17 及更高版本中，
InnoDB允许在CREATE INDEX、CREATE TABLE和ALTER TABLE语句中使用额外的ARRAY关键字来创建 JSON 数组的多值索引。除了在这些语句中用于创建多值索引时，不支持ARRAY。被索引的列必须是 JSON 类型的列。使用ARRAY时，AS关键字后面的type可以指定CAST()支持的任何类型，但不包括BINARY、JSON和YEAR。有关语法信息和示例，以及其他相关信息，请参见多值索引。注意
与
CAST()不同，CONVERT()不支持多值索引创建或ARRAY关键字。从 MySQL 8.0.22 开始，
CAST()支持使用AT TIMEZONE运算符将TIMESTAMP值检索为 UTC 时间。唯一支持的时区是 UTC；可以将其指定为'+00:00'或'UTC'。此语法支持的唯一返回类型是DATETIME，其精度范围为 0 到 6，包括 0 和 6。使用时区偏移的
TIMESTAMP值也受支持。mysql> SELECT @@system_time_zone; +--------------------+ | @@system_time_zone | +--------------------+ | EDT | +--------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE TZ (c TIMESTAMP); Query OK, 0 rows affected (0.41 sec) mysql> INSERT INTO tz VALUES -> ROW(CURRENT_TIMESTAMP), -> ROW('2020-07-28 14:50:15+1:00'); Query OK, 1 row affected (0.08 sec) mysql> TABLE tz; +---------------------+ | c | +---------------------+ | 2020-07-28 09:22:41 | | 2020-07-28 09:50:15 | +---------------------+ 2 rows in set (0.00 sec) mysql> SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz; +---------------------+ | u | +---------------------+ | 2020-07-28 13:22:41 | | 2020-07-28 13:50:15 | +---------------------+ 2 rows in set (0.00 sec) mysql> SELECT CAST(c AT TIME ZONE 'UTC' AS DATETIME(2)) AS u FROM tz; +------------------------+ | u | +------------------------+ | 2020-07-28 13:22:41.00 | | 2020-07-28 13:50:15.00 | +------------------------+ 2 rows in set (0.00 sec)如果您在
CAST()的这种形式中使用'UTC'作为时区指定符，并且服务器引发错误，例如未知或不正确的时区：'UTC'，则可能需要安装 MySQL 时区表（请参阅填充时区表）。AT TIME ZONE不支持ARRAY关键字，并且不受CONVERT()函数支持。 -
-
CONVERT(*expr* USING *transcoding_name*)CONVERT(*expr*,*type*)CONVERT(*expr* USING *transcoding_name*)是标准的 SQL 语法。CONVERT()的非USING形式是 ODBC 语法。无论使用哪种语法，如果expr为NULL，该函数都会返回NULL。CONVERT(*expr* USING *transcoding_name*)在不同字符集之间转换数据。在 MySQL 中，转码名称与相应的字符集名称相同。例如，此语句将默认字符集中的字符串'abc'转换为utf8mb4字符集中的相应字符串：SELECT CONVERT('abc' USING utf8mb4);CONVERT(*expr*, *type*)语法（不带USING）接受一个表达式和一个type值，指定结果类型，并产生指定类型的结果值。此操作也可以表示为CAST(*expr* AS *type*)，这是等效的。有关更多信息，请参阅CAST()的描述。注意
在 MySQL 8.0.28 之前，这个函数有时允许将
BINARY值无效地转换为非二进制字符集。当CONVERT()作为索引生成列表达式的一部分时，这可能导致在从先前版本的 MySQL 升级后出现索引损坏。有关如何处理这种情况的信息，请参见 SQL 更改。
字符集转换
带有USING子句的CONVERT()在字符集之间转换数据：
CONVERT(*expr* USING *transcoding_name*)
在 MySQL 中，转码名称与相应的字符集名称相同。
例子：
SELECT CONVERT('test' USING utf8mb4);
SELECT CONVERT(_latin1'Müller' USING utf8mb4);
INSERT INTO utf8mb4_table (utf8mb4_column)
SELECT CONVERT(latin1_column USING utf8mb4) FROM latin1_table;
要在字符集之间转换字符串，还可以使用CONVERT(*expr*, *type*)语法（不带USING），或者等效的CAST(*expr* AS *type*)：
CONVERT(*string*, CHAR[(*N*)] CHARACTER SET *charset_name*)
CAST(*string* AS CHAR[(*N*)] CHARACTER SET *charset_name*)
例子：
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);
如果你像刚才展示的那样指定CHARACTER SET *charset_name*，结果的字符集和排序规则分别为charset_name和charset_name的默认排序规则。如果省略CHARACTER SET *charset_name*，结果的字符集和排序规则由确定默认连接字符集和排序规则的character_set_connection和collation_connection系统变量定义（参见第 12.4 节，“连接字符集和排序规则”）。
在CONVERT()或CAST()调用中不允许使用COLLATE子句，但可以将其应用于函数结果。例如，以下是合法的：
SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
但以下是不合法的：
SELECT CONVERT('test' USING utf8mb4 COLLATE utf8mb4_bin);
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
对于字符串字面值，指定字符集的另一种方法是使用字符集引导符。在前面的示例中，_latin1和_latin2是引导符的实例。与转换函数（如CAST()或CONVERT()）不同，这些函数将字符串从一个字符集转换为另一个字符集，引导符指定字符串字面值具有特定的字符集，不涉及转换。有关更多信息，请参见第 12.3.8 节，“字符集引导符”。
字符串比较的字符集转换
通常情况下，你无法以不区分大小写的方式比较BLOB值或其他二进制字符串，因为二进制字符串使用binary字符集，该字符集没有与大小写概念相关的排序规则。要执行不区分大小写的比较，首先使用CONVERT()或CAST()函数将值转换为非二进制字符串。对转换后的字符串进行比较时使用其排序规则。例如，如果转换结果的排序规则不区分大小写，则LIKE操作也不区分大小写。这对于以下操作是正确的，因为默认的utf8mb4排序规则（utf8mb4_0900_ai_ci）不区分大小写：
SELECT 'A' LIKE CONVERT(*blob_col* USING utf8mb4)
FROM *tbl_name*;
要为转换后的字符串指定特定的排序规则，请在CONVERT()调用后使用COLLATE子句：
SELECT 'A' LIKE CONVERT(*blob_col* USING utf8mb4) COLLATE utf8mb4_unicode_ci
FROM *tbl_name*;
要使用不同的字符集，请在前述语句中用其名称替换utf8mb4（类似地，要使用不同的排序规则也是如此）。
CONVERT()和CAST()可以更普遍地用于比较以不同字符集表示的字符串。例如，对这些字符串进行比较会导致错误，因为它们具有不同的字符集：
mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';
mysql> SELECT @s1 = @s2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (latin2_general_ci,IMPLICIT) for operation '='
将其中一个字符串转换为与另一个兼容的字符集，使比较可以顺利进行：
mysql> SELECT @s1 = CONVERT(@s2 USING latin1);
+---------------------------------+
| @s1 = CONVERT(@s2 USING latin1) |
+---------------------------------+
| 1 |
+---------------------------------+
字符集转换在对二进制字符串进行大小写转换之前也很有用。当直接应用于二进制字符串时，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 |
+-------------+------------------------------------+
请注意，如果将BINARY、CAST()或CONVERT()应用于索引列，MySQL 可能无法有效地使用索引。
对空间类型执行转换操作
从 MySQL 8.0.24 开始，CAST()和CONVERT()支持将几何值从一种空间类型转换为另一种空间类型，适用于某些空间类型的组合。以下列表显示了允许的类型组合，其中“MySQL 扩展”指的是 MySQL 中实现的超出 SQL/MM 标准定义的转换：
-
从
Point到：-
MultiPoint -
GeometryCollection
-
-
从
LineString到：-
Polygon（MySQL 扩展） -
MultiPoint（MySQL 扩展） -
MultiLineString -
GeometryCollection
-
-
从
Polygon到：-
LineString（MySQL 扩展） -
MultiLineString（MySQL 扩展） -
MultiPolygon -
GeometryCollection
-
-
从
MultiPoint到：-
Point -
LineString（MySQL 扩展） -
GeometryCollection
-
-
从
MultiLineString到：-
LineString -
Polygon（MySQL 扩展） -
MultiPolygon（MySQL 扩展） -
GeometryCollection
-
-
从
MultiPolygon到：-
Polygon -
MultiLineString（MySQL 扩展） -
GeometryCollection
-
-
从
GeometryCollection到：-
Point -
LineString -
Polygon -
MultiPoint -
MultiLineString -
MultiPolygon
-
在空间转换中，GeometryCollection和GeomCollection是相同结果类型的同义词。
一些条件适用于所有空间类型转换，而一些条件仅适用于转换结果为特定空间类型时。有关“格式良好的几何”等术语的信息，请参阅第 13.4.4 节，“几何格式良好性和有效性”。
-
空间转换的一般条件
-
转换为 Point 的条件
-
转换为 LineString 的条件
-
转换为 Polygon 的条件
-
转换为 MultiPoint 的条件
-
转换为 MultiLineString 的条件
-
转换为 MultiPolygon 的条件
-
转换为 GeometryCollection 的条件
空间转换的一般条件
这些条件适用于所有空间转换，无论结果类型如何：
-
转换的结果与要转换的表达式的 SRS 相同。
-
在空间类型之间进行转换不会改变坐标值或顺序。
-
如果要转换的表达式为
NULL，函数结果为NULL。 -
不允许使用带有指定空间类型的
RETURNING子句的JSON_VALUE()函数来转换空间类型。 -
不允许将空间类型转换为
ARRAY。 -
如果空间类型组合是允许的，但要转换的表达式不是一个语法上良好形成的几何图形，将会发生一个
ER_GIS_INVALID_DATA错误。 -
如果空间类型组合是允许的，但要转换的表达式是在未定义的空间参考系统（SRS）中语法上良好形成的几何图形，则会发生一个
ER_SRS_NOT_FOUND错误。 -
如果要转换的表达式具有地理 SRS 但经度或纬度超出范围，则会发生错误：
-
如果经度值不在范围（−180, 180]内，则会发生一个
ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误。 -
如果纬度值不在范围[−90, 90]内，则会发生一个
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误。
显示的范围以度为单位。如果 SRS 使用另一个单位，则范围使用其单位中的相应值。由于浮点运算，确切的范围限制略有偏差。
-
转换为点的条件
当转换结果类型为Point时，应用以下条件：
-
如果要转换的表达式是一个
Point类型的良好形成的几何图形，则函数结果为该Point。 -
如果要转换的表达式是一个只包含单个
Point的MultiPoint类型的良好形成的几何图形，则函数结果为该Point。如果表达式包含多个Point，则会发生一个ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是一个只包含单个
Point的GeometryCollection类型的良好形成的几何图形，则函数结果为该Point。如果表达式为空，包含多个Point或包含其他几何类型，则会发生一个ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是除
Point、MultiPoint、GeometryCollection之外的其他类型的良好形成的几何图形，则会发生一个ER_INVALID_CAST_TO_GEOMETRY错误。
转换为 LineString 的条件
当转换结果类型为LineString时，应用以下条件：
-
如果要转换的表达式是一个
LineString类型的良好形成的几何图形，则函数结果为该LineString。 -
如果要转换的表达式是类型为
Polygon且没有内环的形状，则函数结果是一个LineString，其中包含外环的点按相同顺序排列。如果表达式有内环，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是包含至少两个点的类型为
MultiPoint的形状，则函数结果是一个LineString，其中包含MultiPoint中点的顺序与表达式中出现的顺序相同。如果表达式只包含一个Point，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是包含单个
LineString的类型为MultiLineString的形状，则函数结果是该LineString。如果表达式包含多个LineString，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是包含单个
LineString的类型为GeometryCollection的形状，则函数结果是该LineString。如果表达式为空，包含多个LineString或包含其他几何类型，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是除
LineString、Polygon、MultiPoint、MultiLineString或GeometryCollection之外类型的形状，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。
转换为多边形的条件
当转换结果类型为Polygon时，应满足以下条件：
-
如果要转换的表达式是类型为
LineString的形状，且是一个环（即起点和终点相同），则函数结果是一个外环由LineString中的点按相同顺序组成的Polygon。如果表达式不是环，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。如果环的顺序不正确（外环必须是逆时针方向），则会出现ER_INVALID_CAST_POLYGON_RING_DIRECTION错误。 -
如果要转换的表达式是类型为
Polygon的形状，则函数结果是该Polygon。 -
如果要转换的表达式是类型为
MultiLineString且所有元素都是环的形状良好的几何图形，则函数结果是以第一个LineString作为外环，任何额外的LineString值作为内环的Polygon。如果表达式的任何元素不是环，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。如果任何环不按正确顺序排列（外环必须是逆时针，内环必须是顺时针），则会发生ER_INVALID_CAST_POLYGON_RING_DIRECTION错误。 -
如果要转换的表达式是类型为
MultiPolygon且包含单个Polygon的形状良好的几何图形，则函数结果是该Polygon。如果表达式包含多个Polygon，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是类型为
GeometryCollection且仅包含单个Polygon的形状良好的几何图形，则函数结果是该Polygon。如果表达式为空，包含多个Polygon或包含其他几何类型，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是类型为
LineString、Polygon、MultiLineString、MultiPolygon或GeometryCollection之外的形状良好的几何图形，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。
转换为 MultiPoint 的条件
当转换结果类型为MultiPoint时，应满足以下条件：
-
如果要转换的表达式是类型为
Point的形状良好的几何图形，则函数结果是包含该Point作为唯一元素的MultiPoint。 -
如果要转换的表达式是类型为
LineString的形状良好的几何图形，则函数结果是按相同顺序包含LineString的点的MultiPoint。 -
如果要转换的表达式是类型为
MultiPoint的形状良好的几何图形，则函数结果是该MultiPoint。 -
如果要转换的表达式是类型为
GeometryCollection且仅包含点的形状良好的几何图形，则函数结果是包含这些点的MultiPoint。如果GeometryCollection为空或包含其他几何类型，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是类型为
Point、LineString、MultiPoint或GeometryCollection之外的形状良好的几何图形，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。
转换为 MultiLineString 的条件
当转换结果类型为MultiLineString时，应满足以下条件：
-
如果要转换的表达式是类型为
LineString的格式良好的几何图形，则函数结果为包含该LineString作为唯一元素的MultiLineString。 -
如果要转换的表达式是类型为
Polygon的格式良好的几何图形，则函数结果为包含Polygon的外环作为第一个元素，任何内环作为额外元素按表达式中出现顺序排列的MultiLineString。 -
如果要转换的表达式是类型为
MultiLineString的格式良好的几何图形，则函数结果为该MultiLineString。 -
如果要转换的表达式是仅包含没有内环的多边形的
MultiPolygon的格式良好的几何图形，则函数结果为包含多边形环按表达式中出现顺序排列的MultiLineString。如果表达式包含任何带有内环的多边形，则会发生ER_WRONG_PARAMETERS_TO_STORED_FCT错误。 -
如果要转换的表达式是仅包含线串的
GeometryCollection的格式良好的几何图形，则函数结果为包含这些线串的MultiLineString。如果表达式为空或包含其他几何类型，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是类型为
LineString、Polygon、MultiLineString、MultiPolygon或GeometryCollection之外的格式良好的几何图形，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。
转换为 MultiPolygon 的条件
当转换结果类型为MultiPolygon时，应满足以下条件：
-
如果要转换的表达式是类型为
Polygon的格式良好的几何图形，则函数结果为包含该Polygon作为唯一元素的MultiPolygon。 -
如果要转换的表达式是所有元素均为环的类型为
MultiLineString的格式良好的几何图形，则函数结果为包含每个元素的仅具有外环的Polygon的MultiPolygon。如果任何元素不是环，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。如果任何环不按正确顺序（外环必须逆时针）排列，则会发生ER_INVALID_CAST_POLYGON_RING_DIRECTION错误。 -
如果要转换的表达式是类型为
MultiPolygon的格式良好的几何图形，则函数结果为该MultiPolygon。 -
如果要转换的表达式是一个只包含多边形的
GeometryCollection类型的形状，函数的结果是包含这些多边形的MultiPolygon。如果表达式为空或包含其他几何类型，则会出现ER_INVALID_CAST_TO_GEOMETRY错误。 -
如果要转换的表达式是除了
Polygon、MultiLineString、MultiPolygon或GeometryCollection之外的其他类型的形状，会出现ER_INVALID_CAST_TO_GEOMETRY错误。
转换为 GeometryCollection 的条件
当转换结果类型为GeometryCollection时，这些条件适用：
-
GeometryCollection和GeomCollection是相同结果类型的同义词。 -
如果要转换的表达式是一个只包含点的
Point类型的形状，函数的结果是包含该点作为唯一元素的GeometryCollection。 -
如果要转换的表达式是一个只包含线串的
LineString类型的形状，函数的结果是包含该线串作为唯一元素的GeometryCollection。 -
如果要转换的表达式是一个只包含多边形的
Polygon类型的形状，函数的结果是包含该多边形作为唯一元素的GeometryCollection。 -
如果要转换的表达式是一个只包含多点的
MultiPoint类型的形状，函数的结果是按照表达式中出现的顺序包含这些点的GeometryCollection。 -
如果要转换的表达式是一个只包含多线串的
MultiLineString类型的形状，函数的结果是按照表达式中出现的顺序包含这些线串的GeometryCollection。 -
如果要转换的表达式是一个只包含多边形的
MultiPolygon类型的形状，函数的结果是按照表达式中出现的顺序包含MultiPolygon的元素的GeometryCollection。 -
如果要转换的表达式是一个只包含 GeometryCollection 的
GeometryCollection类型的形状，函数的结果就是那个GeometryCollection。
转换操作的其他用途
转换函数在CREATE TABLE ... SELECT语句中创建具有特定类型的列时非常有用：
mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;
mysql> SHOW CREATE TABLE new_table\G
*************************** 1\. row ***************************
Table: new_table
Create Table: CREATE TABLE `new_table` (
`c1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
转换函数对于按字典顺序排序ENUM列非常有用。通常，对ENUM列的排序使用内部数值。将值转换为CHAR会导致字典排序：
SELECT *enum_col* FROM *tbl_name* ORDER BY CAST(*enum_col* AS CHAR);
CAST()也会改变结果，如果你将其作为更复杂表达式的一部分，比如CONCAT('Date: ',CAST(NOW() AS DATE))。
对于时间值，很少需要使用CAST()以不同格式提取数据。而是使用诸如EXTRACT()、DATE_FORMAT()或TIME_FORMAT()等函数。请参阅第 14.7 节，“日期和时间函数”。
要将字符串转换为数字，通常只需在数字上下文中使用字符串值：
mysql> SELECT 1+'1';
-> 2
十六进制和位字面值也是默认的二进制字符串。
mysql> SELECT X'41', X'41'+0;
-> 'A', 65
mysql> SELECT b'1100001', b'1100001'+0;
-> 'a', 97
在算术运算中使用的字符串在表达式评估期间被转换为浮点数。
在字符串上下文中使用的数字被转换为字符串：
mysql> SELECT CONCAT('hello you ',2);
-> 'hello you 2'
有关将数字隐式转换为字符串的信息，请参阅第 14.3 节，“表达式评估中的类型转换”。
MySQL 支持带符号和无符号 64 位值的算术运算。对于数值运算符（如+或-），其中一个操作数是无符号整数时，默认结果是无符号的（参见第 14.6.1 节，“算术运算符”）。要覆盖此行为，使用SIGNED或UNSIGNED强制转换运算符将值分别转换为有符号或无符号 64 位整数。
mysql> SELECT 1 - 2;
-> -1
mysql> SELECT CAST(1 - 2 AS UNSIGNED);
-> 18446744073709551615
mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);
-> -1
如果任一操作数是浮点值，则结果是浮点值，并且不受前述规则的影响。（在此上下文中，DECIMAL列值被视为浮点值。）
mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
-> -1.0
SQL 模式会影响转换操作的结果（请参阅第 7.1.11 节，“服务器 SQL 模式”）。示例：
-
对于将“零”日期字符串转换为日期，当启用
NO_ZERO_DATESQL 模式时，CONVERT()和CAST()返回NULL并产生警告。 -
对于整数减法，如果启用了
NO_UNSIGNED_SUBTRACTIONSQL 模式，则减法结果是有符号的，即使任何操作数都是无符号的。
14.11 XML 函数
表 14.16 XML 函数
| 名称 | 描述 |
|---|---|
ExtractValue() |
使用 XPath 表示法从 XML 字符串中提取值 |
UpdateXML() |
返回替换的 XML 片段 |
本节讨论了 MySQL 中的 XML 及相关功能。
注意
可以通过在 --xml 选项下调用 mysql 和 mysqldump 客户端来从 MySQL 中获取 XML 格式化输出。请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”，以及 Section 6.5.4, “mysqldump — A Database Backup Program”。
提供基本 XPath 1.0（XML Path Language，版本 1.0）功能的两个函数可用。关于 XPath 语法和用法的一些基本信息稍后在本节中提供；但是，关于这些主题的深入讨论超出了本手册的范围，您应参考 XML Path Language (XPath) 1.0 标准 获取确切信息。对于对 XPath 新手或希望复习基础知识的人来说，Zvon.org XPath 教程 是一个有用的资源，提供多种语言版本。
注意
这些函数仍在开发中。我们将继续改进 MySQL 8.0 及以后版本中的 XML 和 XPath 功能的其他方面。您可以在 MySQL XML 用户论坛 中讨论这些问题，提出问题，并从其他用户那里获得帮助。
与这些函数一起使用的 XPath 表达式支持用户变量和存储程序局部变量。用户变量进行弱类型检查；存储程序局部变量进行强类型检查（另请参见 Bug #26518）：
-
用户变量（弱类型检查）。 使用
$@*variable_name*（即用户变量）语法的变量不受检查。如果变量类型错误或之前未分配值，服务器不会发出警告或错误。这也意味着用户完全负责任何打字错误，因为如果（例如）使用$@myvairable而意图使用$@myvariable，服务器不会发出警告。示例：
mysql> SET @xml = '<a><b>X</b><b>Y</b></a>'; Query OK, 0 rows affected (0.00 sec) mysql> SET @i =1, @j = 2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @i, ExtractValue(@xml, '//b[$@i]'); +------+--------------------------------+ | @i | ExtractValue(@xml, '//b[$@i]') | +------+--------------------------------+ | 1 | X | +------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @j, ExtractValue(@xml, '//b[$@j]'); +------+--------------------------------+ | @j | ExtractValue(@xml, '//b[$@j]') | +------+--------------------------------+ | 2 | Y | +------+--------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @k, ExtractValue(@xml, '//b[$@k]'); +------+--------------------------------+ | @k | ExtractValue(@xml, '//b[$@k]') | +------+--------------------------------+ | NULL | | +------+--------------------------------+ 1 row in set (0.00 sec) -
存储程序中的变量（强类型检查）。 使用
$*variable_name*语法声明和使用这些函数时，当它们在存储程序内部调用时，这些变量是局部于定义它们的存储程序，并且对类型和值进行了强类型检查。示例：
mysql> DELIMITER | mysql> CREATE PROCEDURE myproc () -> BEGIN -> DECLARE i INT DEFAULT 1; -> DECLARE xml VARCHAR(25) DEFAULT '<a>X</a><a>Y</a><a>Z</a>'; -> -> WHILE i < 4 DO -> SELECT xml, i, ExtractValue(xml, '//a[$i]'); -> SET i = i+1; -> END WHILE; -> END | Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> CALL myproc(); +--------------------------+---+------------------------------+ | xml | i | ExtractValue(xml, '//a[$i]') | +--------------------------+---+------------------------------+ | <a>X</a><a>Y</a><a>Z</a> | 1 | X | +--------------------------+---+------------------------------+ 1 row in set (0.00 sec) +--------------------------+---+------------------------------+ | xml | i | ExtractValue(xml, '//a[$i]') | +--------------------------+---+------------------------------+ | <a>X</a><a>Y</a><a>Z</a> | 2 | Y | +--------------------------+---+------------------------------+ 1 row in set (0.01 sec) +--------------------------+---+------------------------------+ | xml | i | ExtractValue(xml, '//a[$i]') | +--------------------------+---+------------------------------+ | <a>X</a><a>Y</a><a>Z</a> | 3 | Z | +--------------------------+---+------------------------------+ 1 row in set (0.01 sec)参数。 在存储过程内部 XPath 表达式中使用的变量作为参数传递时也要进行严格检查。
包含用户变量或存储程序本地变量的表达式必须（除了符号）符合 XPath 1.0 规范中包含变量的 XPath 表达式的规则。
注意
用于存储 XPath 表达式的用户变量被视为空字符串。因此，不可能将 XPath 表达式存储为用户变量。（Bug #32911）
-
ExtractValue(*xml_frag*, *xpath_expr*)ExtractValue()接受两个字符串参数，一个是 XML 标记片段xml_frag，另一个是 XPath 表达式xpath_expr（也称为定位器）；它返回与 XPath 表达式匹配的元素的第一个文本节点的文本（CDATA）。使用此函数等同于在追加
/text()后使用xpath_expr进行匹配。换句话说，ExtractValue('<a><b>Sakila</b></a>', '/a/b')和ExtractValue('<a><b>Sakila</b></a>', '/a/b/text()')产生相同的结果。如果xml_frag或xpath_expr为NULL，函数将返回NULL。如果找到多个匹配项，则返回每个匹配元素的第一个子文本节点的内容（按匹配顺序）作为一个单独的、以空格分隔的字符串。
如果找不到与表达式匹配的文本节点（包括隐式的
/text()）——无论出于何种原因，只要xpath_expr有效，xml_frag由正确嵌套和关闭的元素组成——则返回空字符串。不区分匹配空元素和根本没有匹配。这是设计上的考虑。如果您需要确定
xml_frag中是否找不到匹配元素或找到这样的元素但不包含子文本节点，您应该测试使用 XPathcount()函数的表达式的结果。例如，这两个语句都返回空字符串，如下所示：mysql> SELECT ExtractValue('<a><b/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><b/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue('<a><c/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><c/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec)然而，您可以通过以下方式确定是否实际上存在匹配的元素：
mysql> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><b/></a>', 'count(/a/b)') | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><c/></a>', 'count(/a/b)') | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.01 sec)重要提示
ExtractValue()仅返回CDATA，不返回匹配标记内可能包含的任何标记，也不返回它们的内容（请参见以下示例中作为val1返回的结果）。mysql> SELECT -> ExtractValue('<a>ccc<b>ddd</b></a>', '/a') AS val1, -> ExtractValue('<a>ccc<b>ddd</b></a>', '/a/b') AS val2, -> ExtractValue('<a>ccc<b>ddd</b></a>', '//b') AS val3, -> ExtractValue('<a>ccc<b>ddd</b></a>', '/b') AS val4, -> ExtractValue('<a>ccc<b>ddd</b><b>eee</b></a>', '//b') AS val5; +------+------+------+------+---------+ | val1 | val2 | val3 | val4 | val5 | +------+------+------+------+---------+ | ccc | ddd | ddd | | ddd eee | +------+------+------+------+---------+此函数使用当前的 SQL 校对规则来与
contains()进行比较，执行与其他字符串函数（如CONCAT()）相同的校对聚合，考虑到它们的参数的校对可强制性；请参阅第 12.8.4 节，“表达式中的校对可强制性”，了解规定此行为的规则的解释。（以前，总是使用二进制—即，区分大小写—比较。）
如果
xml_frag包含未正确嵌套或关闭的元素，则返回NULL，并生成警告，如下例所示：mysql> SELECT ExtractValue('<a>c</a><b', '//a'); +-----------------------------------+ | ExtractValue('<a>c</a><b', '//a') | +-----------------------------------+ | NULL | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1\. row *************************** Level: Warning Code: 1525 Message: Incorrect XML value: 'parse error at line 1 pos 11: END-OF-INPUT unexpected ('>' wanted)' 1 row in set (0.00 sec) mysql> SELECT ExtractValue('<a>c</a><b/>', '//a'); +-------------------------------------+ | ExtractValue('<a>c</a><b/>', '//a') | +-------------------------------------+ | c | +-------------------------------------+ 1 row in set (0.00 sec) -
UpdateXML(*xml_target*, *xpath_expr*, *new_xml*)此函数用新的 XML 片段
new_xml替换给定 XML 标记片段xml_target的单个部分，然后返回更改后的 XML。被替换的xml_target部分与用户提供的 XPath 表达式xpath_expr匹配。如果找不到与
xpath_expr匹配的表达式，或者找到多个匹配项，则该函数返回原始的xml_targetXML 片段。所有三个参数都应为字符串。如果UpdateXML()的任何参数为NULL，则函数返回NULL。mysql> SELECT -> UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>') AS val1, -> UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>') AS val2, -> UpdateXML('<a><b>ccc</b><d></d></a>', '//b', '<e>fff</e>') AS val3, -> UpdateXML('<a><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val4, -> UpdateXML('<a><d></d><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val5 -> \G *************************** 1\. row *************************** val1: <e>fff</e> val2: <a><b>ccc</b><d></d></a> val3: <a><e>fff</e><d></d></a> val4: <a><b>ccc</b><e>fff</e></a> val5: <a><d></d><b>ccc</b><d></d></a>
注意
对 XPath 语法和用法的深入讨论超出了本手册的范围。请参阅XML Path Language (XPath) 1.0 规范以获取确切信息。对于那些对 XPath 新手或希望在基础知识方面进行复习的人来说，Zvon.org XPath 教程是一个有用的资源，提供多种语言版本。
以下是一些基本 XPath 表达式的描述和示例：
-
/*tag*仅当
<*tag*/>是根元素时，才匹配<*tag*/>。示例：
/a在<a><b/></a>中有匹配项，因为它匹配最外层（根）标记。它不匹配<b><a/></b>中的内部a元素，因为在这种情况下，它是另一个元素的子元素。 -
/*tag1*/*tag2*仅当
<*tag2*/>是<*tag1*/>的子元素，并且<*tag1*/>是根元素时，才匹配<*tag2*/>。示例：
/a/b匹配 XML 片段<a><b/></a>中的b元素，因为它是根元素a的子元素。它在<b><a/></b>中没有匹配项，因为在这种情况下，b是根元素（因此不是其他元素的子元素）。XPath 表达式在<a><c><b/></c></a>中也没有匹配项；这里，b是a的后代，但实际上不是a的子元素。此结构可扩展到三个或更多元素。例如，XPath 表达式
/a/b/c匹配片段<a><b><c/></b></a>中的c元素。 -
//*tag*匹配任何
<*tag*>的实例。示例：
//a匹配以下任意一个元素中的a元素：<a><b><c/></b></a>；<c><a><b/></a></b>；<c><b><a/></b></c>。//可以与/结合使用。例如，//a/b匹配片段<a><b/></a>或<c><a><b/></a></c>中的b元素。注意
//*tag*等同于/descendant-or-self::*/*tag*。一个常见错误是将其与/descendant-or-self::*tag*混淆，尽管后者的表达实际上可能导致非常不同的结果，如下所示：mysql> SET @xml = '<a><b><c>w</c><b>x</b><d>y</d>z</b></a>'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @xml; +-----------------------------------------+ | @xml | +-----------------------------------------+ | <a><b><c>w</c><b>x</b><d>y</d>z</b></a> | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue(@xml, '//b[1]'); +------------------------------+ | ExtractValue(@xml, '//b[1]') | +------------------------------+ | x z | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue(@xml, '//b[2]'); +------------------------------+ | ExtractValue(@xml, '//b[2]') | +------------------------------+ | | +------------------------------+ 1 row in set (0.01 sec) mysql> SELECT ExtractValue(@xml, '/descendant-or-self::*/b[1]'); +---------------------------------------------------+ | ExtractValue(@xml, '/descendant-or-self::*/b[1]') | +---------------------------------------------------+ | x z | +---------------------------------------------------+ 1 row in set (0.06 sec) mysql> SELECT ExtractValue(@xml, '/descendant-or-self::*/b[2]'); +---------------------------------------------------+ | ExtractValue(@xml, '/descendant-or-self::*/b[2]') | +---------------------------------------------------+ | | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue(@xml, '/descendant-or-self::b[1]'); +-------------------------------------------------+ | ExtractValue(@xml, '/descendant-or-self::b[1]') | +-------------------------------------------------+ | z | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ExtractValue(@xml, '/descendant-or-self::b[2]'); +-------------------------------------------------+ | ExtractValue(@xml, '/descendant-or-self::b[2]') | +-------------------------------------------------+ | x | +-------------------------------------------------+ 1 row in set (0.00 sec) -
*运算符充当“通配符”，匹配任何元素。例如，表达式/*/b匹配 XML 片段<a><b/></a>或<c><b/></c>中的b元素。然而，在片段<b><a/></b>中，该表达式不会产生匹配，因为b必须是其他元素的子元素。通配符可以在任何位置使用：表达式/*/b/*匹配b元素的任何子元素，该子元素本身不是根元素。 -
您可以使用
|（UNION）运算符匹配多个定位器中的任何一个。例如，表达式//b|//c匹配 XML 目标中的所有b和c元素。 -
还可以根据一个或多个属性的值匹配元素。使用语法
*tag*[@*attribute*="*value*"]。例如，表达式//b[@id="idB"]匹配片段<a><b id="idA"/><c/><b id="idB"/></a>中的第二个b元素。要匹配具有*attribute*="*value*"的任何元素，请使用 XPath 表达式//*[*attribute*="*value*"]。要过滤多个属性值，只需连续使用多个属性比较子句。例如，表达式
//b[@c="x"][@d="y"]匹配出现在给定 XML 片段中的任何位置的元素<b c="x" d="y"/>。要查找具有多个值中的任何一个匹配的元素，可以使用由
|运算符连接的多个定位器。例如，要匹配所有b元素，其c属性具有值 23 或 17 中的任何一个，请使用表达式//b[@c="23"]|//b[@c="17"]。您也可以使用逻辑or运算符来实现此目的：//b[@c="23" or @c="17"]。注意
or和|之间的区别在于or连接条件，而|连接结果集。
XPath 限制。 这些函数支持的 XPath 语法目前受到以下限制：
-
不支持节点集到节点集的比较（例如
'/a/b[@c=@d]'）。 -
所有标准的 XPath 比较运算符都受支持。（Bug #22823）
-
相对定位器表达式在根节点的上下文中解析。例如，考虑以下查询和结果：
mysql> SELECT ExtractValue( -> '<a><b c="1">X</b><b c="2">Y</b></a>', -> 'a/b' -> ) AS result; +--------+ | result | +--------+ | X Y | +--------+ 1 row in set (0.03 sec)在这种情况下，定位器
a/b解析为/a/b。在谓词内也支持相对定位器。在以下示例中，
d[../@c="1"]解析为/a/b[@c="1"]/d：mysql> SELECT ExtractValue( -> '<a> -> <b c="1"><d>X</d></b> -> <b c="2"><d>X</d></b> -> </a>', -> 'a/b/d[../@c="1"]') -> AS result; +--------+ | result | +--------+ | X | +--------+ 1 row in set (0.00 sec) -
不允许使用作为标量值评估的表达式前缀定位器，包括变量引用、文字、数字和标量函数调用，其使用会导致错误。
-
::运算符与以下节点类型的组合不受支持：-
*axis*::comment() -
*axis*::text() -
*axis*::processing-instructions() -
*axis*::node()
然而，名称测试（如
*axis*::*name*和*axis*::*）是受支持的，如下例所示：mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b'); +-------------------------------------------------------+ | ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b') | +-------------------------------------------------------+ | x | +-------------------------------------------------------+ 1 row in set (0.02 sec) mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*'); +-------------------------------------------------------+ | ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*') | +-------------------------------------------------------+ | x y | +-------------------------------------------------------+ 1 row in set (0.01 sec) -
-
在路径会导致“超出”根元素的情况下，不支持“上下”导航。也就是说，您不能使用在给定元素的祖先的后代上匹配的表达式，其中当前元素的一个或多个祖先也是根元素的祖先（请参见 Bug #16321）。
-
不支持以下 XPath 函数，或者存在已知问题，如下所示：
-
id() -
lang() -
local-name() -
name() -
namespace-uri() -
normalize-space() -
starts-with() -
string() -
substring-after() -
substring-before() -
translate()
-
-
不支持以下轴：
-
following-sibling -
following -
preceding-sibling -
preceding
-
作为参数传递给 ExtractValue() 和 UpdateXML() 的 XPath 表达式可能包含元素选择器中的冒号字符（:），这使它们可以与使用 XML 命名空间表示法的标记一起使用。例如：
mysql> SET @xml = '<a>111<b:c>222<d>333</d><e:f>444</e:f></b:c></a>';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT ExtractValue(@xml, '//e:f');
+-----------------------------+
| ExtractValue(@xml, '//e:f') |
+-----------------------------+
| 444 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT UpdateXML(@xml, '//b:c', '<g:h>555</g:h>');
+--------------------------------------------+
| UpdateXML(@xml, '//b:c', '<g:h>555</g:h>') |
+--------------------------------------------+
| <a>111<g:h>555</g:h></a> |
+--------------------------------------------+
1 row in set (0.00 sec)
这在某些方面类似于 Apache Xalan 和其他一些解析器允许的内容，比起要求命名空间声明或使用 namespace-uri() 和 local-name() 函数要简单得多。
错误处理。 对于 ExtractValue() 和 UpdateXML()，使用的 XPath 定位器必须有效，并且要搜索的 XML 必须由正确嵌套和关闭的元素组成。如果定位器无效，则会生成错误：
mysql> SELECT ExtractValue('<a>c</a><b/>', '/&a');
ERROR 1105 (HY000): XPATH syntax error: '&a'
如果 xml_frag 不包含正确嵌套和关闭的元素，则返回 NULL 并生成警告，如下例所示：
mysql> SELECT ExtractValue('<a>c</a><b', '//a');
+-----------------------------------+
| ExtractValue('<a>c</a><b', '//a') |
+-----------------------------------+
| NULL |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
Level: Warning
Code: 1525
Message: Incorrect XML value: 'parse error at line 1 pos 11:
END-OF-INPUT unexpected ('>' wanted)' 1 row in set (0.00 sec)
mysql> SELECT ExtractValue('<a>c</a><b/>', '//a');
+-------------------------------------+
| ExtractValue('<a>c</a><b/>', '//a') |
+-------------------------------------+
| c |
+-------------------------------------+
1 row in set (0.00 sec)
重要提示
作为传递给 UpdateXML() 的第三个参数的替换 XML 不会 被检查，以确定它是否仅由正确嵌套和关闭的元素组成。
XPath 注入。 代码注入发生在恶意代码被引入系统以获取未经授权的权限和数据时。它基于开发人员对用户输入数据类型和内容所做的假设。XPath 也不例外。
一个常见的情况是应用程序处理授权的情况，通过将登录名和密码的组合与 XML 文件中找到的内容进行匹配，使用类似于以下的 XPath 表达式：
//user[login/text()='neapolitan' and password/text()='1c3cr34m']/attribute::id
这是类似于 SQL 语句的 XPath 等效语句：
SELECT id FROM users WHERE login='neapolitan' AND password='1c3cr34m';
使用 XPath 的 PHP 应用程序可能会处理登录过程如下：
<?php
$file = "users.xml";
$login = $POST["login"];
$password = $POST["password"];
$xpath = "//user[login/text()=$login and password/text()=$password]/attribute::id";
if( file_exists($file) )
{
$xml = simplexml_load_file($file);
if($result = $xml->xpath($xpath))
echo "You are now logged in as user $result[0].";
else
echo "Invalid login name or password.";
}
else
exit("Failed to open $file.");
?>
输入上没有进行检查。这意味着恶意用户可以通过在登录名和密码中输入' or 1=1来“绕过”测试，导致$xpath被评估如下所示：
//user[login/text()='' or 1=1 and password/text()='' or 1=1]/attribute::id
由于方括号内的表达式始终评估为true，它实际上与这个表达式相同，该表达式匹配 XML 文档中每个user元素的id属性：
//user/attribute::id
可以简单地通过在$xpath的定义中引用要插入的变量名来规避这种特定攻击，强制从 Web 表单传递的值转换为字符串：
$xpath = "//user[login/text()='$login' and password/text()='$password']/attribute::id";
这与通常用于防止 SQL 注入攻击的策略相同。一般来说，用于防止 XPath 注入攻击的实践应该与防止 SQL 注入攻击的实践相同：
-
永远不要在应用程序中接受未经测试的用户数据。
-
检查所有用户提交的数据的类型；拒绝或转换错误类型的数据。
-
测试数值数据是否超出范围；截断、四舍五入或拒绝超出范围的值。测试字符串是否包含非法字符，要么将其剥离，要么拒绝包含这些字符的输入。
-
不要输出可能向未经授权的用户提供线索以危害系统的明确错误消息；而是将这些记录到文件或数据库表中。
就像 SQL 注入攻击可以用于获取有关数据库模式的信息一样，XPath 注入也可以用于遍历 XML 文件以揭示其结构，如 Amit Klein 的论文Blind XPath Injection（PDF 文件，46KB）中所讨论的。
重要的是检查发送回客户端的输出。考虑当我们使用 MySQL ExtractValue() 函数时可能发生的情况：
mysql> SELECT ExtractValue(
-> LOAD_FILE('users.xml'),
-> '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id'
-> ) AS id;
+-------------------------------+
| id |
+-------------------------------+
| 00327 13579 02403 42354 28570 |
+-------------------------------+
1 row in set (0.01 sec)
因为 ExtractValue() 将多个匹配项作为一个以空格分隔的字符串返回，这种注入攻击将 users.xml 中的每个有效 ID 作为单行输出提供给用户。作为额外的保障，你还应该在返回给用户之前测试输出。这里是一个简单的例子：
mysql> SELECT @id = ExtractValue(
-> LOAD_FILE('users.xml'),
-> '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT IF(
-> INSTR(@id, ' ') = 0,
-> @id,
-> 'Unable to retrieve user ID')
-> AS singleID;
+----------------------------+
| singleID |
+----------------------------+
| Unable to retrieve user ID |
+----------------------------+
1 row in set (0.00 sec)
一般来说，将数据安全返回给用户的指导原则与接受用户输入的原则相同。这可以总结为：
-
始终测试传出数据的类型和允许的值。
-
永远不要允许未经授权的用户查看可能提供有关应用程序信息的错误消息，这些信息可能被用于利用它。
14.12 位函数和运算符
表 14.17 位函数和运算符
| 名称 | 描述 |
|---|---|
& |
按位与 |
>> |
右移 |
<< |
左移 |
^ |
按位异或 |
BIT_COUNT() |
返回设置的位数 |
| |
按位或 |
~ |
按位取反 |
以下列表描述了可用的位函数和运算符：
-
|按位或。
结果类型取决于参数是作为二进制字符串还是数字进行评估：
-
当参数具有二进制字符串类型且至少有一个不是十六进制文字、位文字或
NULL文字时，进行二进制字符串运算。否则进行数值运算，必要时将参数转换为无符号 64 位整数。 -
二进制字符串运算会产生与参数相同长度的二进制字符串。如果参数长度不相等，则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE错误。数值运算会产生一个无符号 64 位整数。
更多信息，请参阅本节的介绍性讨论。
mysql> SELECT 29 | 15; -> 31 mysql> SELECT _binary X'40404040' | X'01020304'; -> 'ABCD'如果在mysql客户端中调用按位或运算，二进制字符串结果将以十六进制表示，具体取决于
--binary-as-hex的值。有关该选项的更多信息，请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
-
&按位与。
结果类型取决于参数是作为二进制字符串还是数字进行评估：
-
当参数具有二进制字符串类型且至少有一个不是十六进制文字、位文字或
NULL文字时，进行二进制字符串运算。否则进行数值运算，必要时将参数转换为无符号 64 位整数。 -
二进制字符串运算会产生与参数相同长度的二进制字符串。如果参数长度不相等，则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE错误。数值运算会产生一个无符号 64 位整数。
更多信息，请参阅本节的介绍性讨论。
mysql> SELECT 29 & 15; -> 13 mysql> SELECT HEX(_binary X'FF' & b'11110000'); -> 'F0'如果在mysql客户端中调用按位与，二进制字符串结果将以十六进制表示，具体取决于
--binary-as-hex的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。 -
-
^按位异或。
结果类型取决于参数是作为二进制字符串还是数字进行评估：
-
当参数具有二进制字符串类型且至少一个参数不是十六进制文字、比特文字或
NULL文字时，进行二进制字符串运算。否则进行数值运算，并根据需要将参数转换为无符号 64 位整数。 -
二进制字符串运算产生与参数长度相同的二进制字符串。如果参数长度不相等，则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE错误。数值运算产生一个无符号 64 位整数。
更多信息，请参阅本节的介绍性讨论。
mysql> SELECT 1 ^ 1; -> 0 mysql> SELECT 1 ^ 0; -> 1 mysql> SELECT 11 ^ 3; -> 8 mysql> SELECT HEX(_binary X'FEDC' ^ X'1111'); -> 'EFCD'如果在mysql客户端中调用按位异或，二进制字符串结果将以十六进制表示，具体取决于
--binary-as-hex的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。 -
-
<<将一个长整型（
BIGINT）数字或二进制字符串向左移位。结果类型取决于比特参数是作为二进制字符串还是数字进行评估：
-
当比特参数具有二进制字符串类型且不是十六进制文字、比特文字或
NULL文字时，进行二进制字符串运算。否则进行数值运算，并根据需要将参数转换为无符号 64 位整数。 -
二进制字符串运算产生与比特参数长度相同的二进制字符串。数值运算产生一个无符号 64 位整数。
超出值末尾的位将被丢弃，不会有警告，无论参数类型如何。特别是，如果移位计数大于或等于比特参数中的位数，则结果中的所有位都为 0。
更多信息，请参阅本节的介绍性讨论。
mysql> SELECT 1 << 2; -> 4 mysql> SELECT HEX(_binary X'00FF00FF00FF' << 8); -> 'FF00FF00FF00'如果在mysql客户端内调用位移操作，则根据
--binary-as-hex的值，二进制字符串结果将以十六进制表示。有关该选项的更多信息，请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
-
>>将一个长整型(
BIGINT- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"))数字或二进制字符串向右移动。结果类型取决于位参数是作为二进制字符串还是数字进行评估：
-
当位参数具有二进制字符串类型且不是十六进制文字、位文字或
NULL文字时，进行二进制字符串评估。否则进行数值评估，必要时将参数转换为无符号 64 位整数。 -
二进制字符串评估会产生与位参数相同长度的二进制字符串。数值评估会产生一个无符号 64 位整数。
无论参数类型如何，超出值末尾的位都会被丢弃而不发出警告。特别是，如果位移计数大于或等于位参数中的位数，则结果中的所有位都为 0。
有关更多信息，请参阅本节中的介绍性讨论。
mysql> SELECT 4 >> 2; -> 1 mysql> SELECT HEX(_binary X'00FF00FF00FF' >> 8); -> '0000FF00FF00'如果在mysql客户端内调用位移操作，则根据
--binary-as-hex的值，二进制字符串结果将以十六进制表示。有关该选项的更多信息，请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
-
~反转所有位。
结果类型取决于位参数是作为二进制字符串还是数字进行评估：
-
当位参数具有二进制字符串类型且不是十六进制文字、位文字或
NULL文字时，进行二进制字符串评估。否则进行数值评估，必要时将参数转换为无符号 64 位整数。 -
二进制字符串评估会产生与位参数相同长度的二进制字符串。数值评估会产生一个无符号 64 位整数。
有关更多信息，请参阅本节中的介绍性讨论。
mysql> SELECT 5 & ~1; -> 4 mysql> SELECT HEX(~X'0000FFFF1111EEEE'); -> 'FFFF0000EEEE1111'如果在mysql客户端内调用位求反操作，则根据
--binary-as-hex的值，二进制字符串结果将以十六进制表示。有关该选项的更多信息，请参阅 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
-
BIT_COUNT(*N*)返回参数
N中设置的位数作为无符号 64 位整数，如果参数为NULL则返回NULL。mysql> SELECT BIT_COUNT(64), BIT_COUNT(BINARY 64); -> 1, 7 mysql> SELECT BIT_COUNT('64'), BIT_COUNT(_binary '64'); -> 1, 7 mysql> SELECT BIT_COUNT(X'40'), BIT_COUNT(_binary X'40'); -> 1, 1
位函数和运算符包括 BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, 以及 >>。（BIT_AND(), BIT_OR(), 和 BIT_XOR() 聚合函数在 第 14.19.1 节，“聚合函数描述” 中有描述。）在 MySQL 8.0 之前，位函数和运算符需要 BIGINT（64 位整数）参数，并返回 BIGINT 值，因此它们的最大范围为 64 位。非 BIGINT 参数在执行操作之前被转换为 BIGINT，并且可能发生截断。
在 MySQL 8.0 中，位函数和运算符允许二进制字符串类型的参数（BINARY, VARBINARY, 以及 BLOB 类型），并返回相同类型的值，这使它们能够接受参数并生成大于 64 位的返回值。非二进制字符串参数被转换为 BIGINT 并按照此类处理，就像以前一样。
这种行为变化的一个影响是，在 MySQL 8.0 中对二进制字符串参数进行位操作可能会产生与 5.7 中不同的结果。有关如何在 MySQL 5.7 中准备可能的 MySQL 5.7 和 8.0 之间不兼容性的信息，请参阅 位函数和运算符，在 MySQL 5.7 参考手册 中。
-
MySQL 8.0 之前的位操作
-
MySQL 8.0 中的位操作
-
二进制字符串位操作示例
-
位与、或和异或操作
-
位取反和移位操作
-
BIT_COUNT() 操作 操作")
-
BIT_AND(), BIT_OR(), 和 BIT_XOR() 操作, BIT_OR(), 和 BIT_XOR() 操作")
-
十六进制文字面量、位文字面量和 NULL 文字面量的特殊处理
-
与 MySQL 5.7 不兼容的位操作
MySQL 8.0 之前的位操作
MySQL 8.0 之前的位操作仅处理无符号 64 位整数参数和结果值（即无符号BIGINT 值）。必要时将其他类型的参数转换为BIGINT。示例：
-
此语句操作数字文字面量，将其视为无符号 64 位整数：
mysql> SELECT 127 | 128, 128 << 2, BIT_COUNT(15); +-----------+----------+---------------+ | 127 | 128 | 128 << 2 | BIT_COUNT(15) | +-----------+----------+---------------+ | 255 | 512 | 4 | +-----------+----------+---------------+ -
在执行此语句之前，对字符串参数进行了数字转换（例如，
'127'转换为127），然后执行与第一个语句相同的操作并产生相同的结果：mysql> SELECT '127' | '128', '128' << 2, BIT_COUNT('15'); +---------------+------------+-----------------+ | '127' | '128' | '128' << 2 | BIT_COUNT('15') | +---------------+------------+-----------------+ | 255 | 512 | 4 | +---------------+------------+-----------------+ -
此语句使用十六进制文字面量作为位操作参数。MySQL 默认将十六进制文字面量视为二进制字符串，但在数字上下文中将其评估为数字（参见第 11.1.4 节，“十六进制文字面量”）。在 MySQL 8.0 之前，数字上下文包括位操作。示例：
mysql> SELECT X'7F' | X'80', X'80' << 2, BIT_COUNT(X'0F'); +---------------+------------+------------------+ | X'7F' | X'80' | X'80' << 2 | BIT_COUNT(X'0F') | +---------------+------------+------------------+ | 255 | 512 | 4 | +---------------+------------+------------------+在位操作中处理位值文字面量类似于十六进制文字面量（即作为数字）。
MySQL 8.0 中的位操作
MySQL 8.0 扩展了位操作，直接处理二进制字符串参数（无需转换）并产生二进制字符串结果。（不是整数或二进制字符串的参数仍然会像以前一样转换为整数。）此扩展以以下方式增强了位操作：
-
可以对超过 64 位的值执行位操作。
-
对于更自然地表示为二进制字符串而不是整数的值执行位操作更容易。
例如，考虑 UUID 值和 IPv6 地址，它们具有人类可读的文本格式，如下所示：
UUID: 6ccd780c-baba-1026-9564-5b8c656024db
IPv6: fe80::219:d1ff:fe91:1a72
在这些格式的文本字符串上操作是繁琐的。一个替代方法是将它们转换为没有分隔符的固定长度二进制字符串。UUID_TO_BIN()和INET6_ATON()分别产生数据类型为BINARY(16)的值，一个 16 字节（128 位）长的二进制字符串。以下语句说明了这一点（HEX()用于生成可显示的值）：
mysql> SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
+----------------------------------------------------------+
| HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db')) |
+----------------------------------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------------------------------+
mysql> SELECT HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72'));
+---------------------------------------------+
| HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72')) |
+---------------------------------------------+
| FE800000000000000219D1FFFE911A72 |
+---------------------------------------------+
那些二进制值可以通过位操作轻松操作，执行诸如从 UUID 值中提取时间戳或从 IPv6 地址中提取网络和主机部分等操作。（有关示例，请参见本讨论后面。）
计为二进制字符串的参数包括列值、例程参数、局部变量和具有二进制字符串类型的用户定义变量：BINARY、VARBINARY或BLOB类型之一。
那么十六进制文字和位文字呢？回想一下，在 MySQL 中，默认情况下这些是二进制字符串，但在数字上下文中是数字。在 MySQL 8.0 中，它们如何处理用于位操作？MySQL 是否继续在数字上下文中评估它们，就像在 MySQL 8.0 之前所做的那样？还是位操作现在将它们作为二进制字符串进行评估，因为二进制字符串可以“原生”处理而无需转换？
答案：通常会使用十六进制文字或位文字指定位操作的参数，以表示数字，因此当所有位参数都是十六进制或位文字时，MySQL 继续在数字上下文中评估位操作，以保持向后兼容性。如果您需要将其评估为二进制字符串，那很容易实现：至少使用一个文字的_binary引入者。
-
这些位操作将十六进制文字和位文字作为整数进行评估：
mysql> SELECT X'40' | X'01', b'11110001' & b'01001111'; +---------------+---------------------------+ | X'40' | X'01' | b'11110001' & b'01001111' | +---------------+---------------------------+ | 65 | 65 | +---------------+---------------------------+ -
这些位操作将十六进制文字和位文字作为二进制字符串进行评估，这是由
_binary引入者引起的：mysql> SELECT _binary X'40' | X'01', b'11110001' & _binary b'01001111'; +-----------------------+-----------------------------------+ | _binary X'40' | X'01' | b'11110001' & _binary b'01001111' | +-----------------------+-----------------------------------+ | A | A | +-----------------------+-----------------------------------+
尽管两个语句中的位操作都产生数值为 65 的结果，但第二个语句在二进制字符串上下文中运行，65 在 ASCII 中是A。
在数字评估上下文中，十六进制文字和位文字参数的允许值最多为 64 位，结果也是如此。相比之下，在二进制字符串评估上下文中，允许的参数（和结果）可以超过 64 位：
mysql> SELECT _binary X'4040404040404040' | X'0102030405060708';
+---------------------------------------------------+
| _binary X'4040404040404040' | X'0102030405060708' |
+---------------------------------------------------+
| ABCDEFGH |
+---------------------------------------------------+
有几种方法可以引用位操作中的十六进制文字或位文字，以导致二进制字符串评估：
_binary *literal*
BINARY *literal*
CAST(*literal* AS BINARY)
将十六进制文字或位文字分配给用户定义变量是产生二进制字符串评估的另一种方法，这将导致具有二进制字符串类型的变量：
mysql> SET @v1 = X'40', @v2 = X'01', @v3 = b'11110001', @v4 = b'01001111';
mysql> SELECT @v1 | @v2, @v3 & @v4;
+-----------+-----------+
| @v1 | @v2 | @v3 & @v4 |
+-----------+-----------+
| A | A |
+-----------+-----------+
在二进制字符串上下文中，位操作的参数必须具有相同的长度，否则会出现ER_INVALID_BITWISE_OPERANDS_SIZE错误：
mysql> SELECT _binary X'40' | X'0001';
ERROR 3513 (HY000): Binary operands of bitwise
operators must be of equal length
为满足等长要求，使用前导零位填充较短值，或者如果较长值以前导零位开始且可以接受较短结果值，则剥离它们：
mysql> SELECT _binary X'0040' | X'0001';
+---------------------------+
| _binary X'0040' | X'0001' |
+---------------------------+
| A |
+---------------------------+
mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A |
+-----------------------+
填充或剥离也可以使用函数来完成，例如LPAD()、RPAD()、SUBSTR()或CAST()。在这种情况下，表达式参数不再都是文字，并且_binary变得不必要。示例：
mysql> SELECT LPAD(X'40', 2, X'00') | X'0001';
+---------------------------------+
| LPAD(X'40', 2, X'00') | X'0001' |
+---------------------------------+
| A |
+---------------------------------+
mysql> SELECT X'40' | SUBSTR(X'0001', 2, 1);
+-------------------------------+
| X'40' | SUBSTR(X'0001', 2, 1) |
+-------------------------------+
| A |
+-------------------------------+
二进制字符串位操作示例
以下示例说明了使用位操作来提取 UUID 值的部分，例如时间戳和 IEEE 802 节点号。此技术需要为每个提取部分准备位掩码。
将文本 UUID 转换为相应的 16 字节二进制值，以便在二进制字符串上下文中使用位操作进行操作：
mysql> SET @uuid = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db');
mysql> SELECT HEX(@uuid);
+----------------------------------+
| HEX(@uuid) |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+
为值的时间戳和节点号部分构造位掩码。时间戳包括前三部分（64 位，位 0 到 63），节点号是最后一部分（48 位，位 80 到 127）：
mysql> SET @ts_mask = CAST(X'FFFFFFFFFFFFFFFF' AS BINARY(16));
mysql> SET @node_mask = CAST(X'FFFFFFFFFFFF' AS BINARY(16)) >> 80;
mysql> SELECT HEX(@ts_mask);
+----------------------------------+
| HEX(@ts_mask) |
+----------------------------------+
| FFFFFFFFFFFFFFFF0000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@node_mask);
+----------------------------------+
| HEX(@node_mask) |
+----------------------------------+
| 00000000000000000000FFFFFFFFFFFF |
+----------------------------------+
这里使用CAST(... AS BINARY(16))函数，因为掩码必须与其应用的 UUID 值长度相同。可以使用其他函数将掩码填充到所需长度以产生相同的结果：
SET @ts_mask= RPAD(X'FFFFFFFFFFFFFFFF' , 16, X'00');
SET @node_mask = LPAD(X'FFFFFFFFFFFF', 16, X'00') ;
使用掩码提取时间戳和节点号部分：
mysql> SELECT HEX(@uuid & @ts_mask) AS 'timestamp part';
+----------------------------------+
| timestamp part |
+----------------------------------+
| 6CCD780CBABA10260000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@uuid & @node_mask) AS 'node part';
+----------------------------------+
| node part |
+----------------------------------+
| 000000000000000000005B8C656024DB |
+----------------------------------+
前面的示例使用了这些位操作：右移（>>)和按位与（&)。
注意
UUID_TO_BIN()接受一个标志，导致生成的二进制 UUID 值中的一些位重新排列。如果使用该标志，请相应修改提取掩码。
下一个示例使用位操作来提取 IPv6 地址的网络和主机部分。假设网络部分长度为 80 位。那么主机部分长度为 128 − 80 = 48 位。要提取地址的网络和主机部分，将其转换为二进制字符串，然后在二进制字符串上下文中使用位操作。
将文本 IPv6 地址转换为相应的二进制字符串：
mysql> SET @ip = INET6_ATON('fe80::219:d1ff:fe91:1a72');
定义网络长度（以位为单位）：
mysql> SET @net_len = 80;
通过将全为 1 的地址左移或右移来构造网络和主机掩码。为此，从地址::开始，这是所有零的简写，可以通过将其转换为二进制字符串来查看：
mysql> SELECT HEX(INET6_ATON('::')) AS 'all zeros';
+----------------------------------+
| all zeros |
+----------------------------------+
| 00000000000000000000000000000000 |
+----------------------------------+
要生成补码值（全为 1），使用~运算符来反转位：
mysql> SELECT HEX(~INET6_ATON('::')) AS 'all ones';
+----------------------------------+
| all ones |
+----------------------------------+
| FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF |
+----------------------------------+
将全为 1 的值左移或右移以生成网络和主机掩码：
mysql> SET @net_mask = ~INET6_ATON('::') << (128 - @net_len);
mysql> SET @host_mask = ~INET6_ATON('::') >> @net_len;
显示掩码以验证其覆盖地址的正确部分：
mysql> SELECT INET6_NTOA(@net_mask) AS 'network mask';
+----------------------------+
| network mask |
+----------------------------+
| ffff:ffff:ffff:ffff:ffff:: |
+----------------------------+
mysql> SELECT INET6_NTOA(@host_mask) AS 'host mask';
+------------------------+
| host mask |
+------------------------+
| ::ffff:255.255.255.255 |
+------------------------+
提取并显示地址的网络部分和主机部分：
mysql> SET @net_part = @ip & @net_mask;
mysql> SET @host_part = @ip & @host_mask;
mysql> SELECT INET6_NTOA(@net_part) AS 'network part';
+-----------------+
| network part |
+-----------------+
| fe80::219:0:0:0 |
+-----------------+
mysql> SELECT INET6_NTOA(@host_part) AS 'host part';
+------------------+
| host part |
+------------------+
| ::d1ff:fe91:1a72 |
+------------------+
前面的示例使用了这些位操作：补码（~）、左移（<<）和按位与（&）。
剩余的讨论提供了每组位操作的参数处理细节，位操作中字面值处理的更多信息，以及 MySQL 8.0 与旧版 MySQL 之间的潜在不兼容性。
按位与、或和异或操作
对于&、|和^位操作，结果类型取决于参数是作为二进制字符串还是数字进行评估：
-
当参数具有二进制字符串类型且至少有一个参数不是十六进制字面值、位字面值或
NULL字面值时，进行二进制字符串评估。否则进行数值评估，必要时将参数转换为无符号 64 位整数。 -
二进制字符串评估产生与参数相同长度的二进制字符串。如果参数长度不相等，则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE错误。数值评估产生一个无符号 64 位整数。
数值评估的示例：
mysql> SELECT 64 | 1, X'40' | X'01';
+--------+---------------+
| 64 | 1 | X'40' | X'01' |
+--------+---------------+
| 65 | 65 |
+--------+---------------+
二进制字符串评估的示例：
mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A |
+-----------------------+
mysql> SET @var1 = X'40', @var2 = X'01';
mysql> SELECT @var1 | @var2;
+---------------+
| @var1 | @var2 |
+---------------+
| A |
+---------------+
按位补码和移位操作
对于~、<<和>>位操作，结果类型取决于位参数是作为二进制字符串还是数字进行评估：
-
当位参数具有二进制字符串类型且不是十六进制字面值、位字面值或
NULL字面值时，进行二进制字符串评估。否则进行数值评估，必要时将参数转换为无符号 64 位整数。 -
二进制字符串评估产生与位参数相同长度的二进制字符串。数值评估产生一个无符号 64 位整数。
对于移位操作，超出值末尾的位将被丢弃，而不会有警告，无论参数类型如何。特别是，如果移位计数大于或等于位参数中的位数，则结果中的所有位都为 0。
数值评估的示例：
mysql> SELECT ~0, 64 << 2, X'40' << 2;
+----------------------+---------+------------+
| ~0 | 64 << 2 | X'40' << 2 |
+----------------------+---------+------------+
| 18446744073709551615 | 256 | 256 |
+----------------------+---------+------------+
二进制字符串评估的示例：
mysql> SELECT HEX(_binary X'1111000022220000' >> 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' >> 16) |
+----------------------------------------+
| 0000111100002222 |
+----------------------------------------+
mysql> SELECT HEX(_binary X'1111000022220000' << 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' << 16) |
+----------------------------------------+
| 0000222200000000 |
+----------------------------------------+
mysql> SET @var1 = X'F0F0F0F0';
mysql> SELECT HEX(~@var1);
+-------------+
| HEX(~@var1) |
+-------------+
| 0F0F0F0F |
+-------------+
BIT_COUNT() 操作
BIT_COUNT() 函数始终返回一个无符号 64 位整数，如果参数为NULL，则返回NULL。
mysql> SELECT BIT_COUNT(127);
+----------------+
| BIT_COUNT(127) |
+----------------+
| 7 |
+----------------+
mysql> SELECT BIT_COUNT(b'010101'), BIT_COUNT(_binary b'010101');
+----------------------+------------------------------+
| BIT_COUNT(b'010101') | BIT_COUNT(_binary b'010101') |
+----------------------+------------------------------+
| 3 | 3 |
+----------------------+------------------------------+
BIT_AND()、BIT_OR() 和 BIT_XOR() 操作
对于BIT_AND()、BIT_OR()和BIT_XOR()位函数，结果类型取决于函数参数值是作为二进制字符串还是数字进行评估：
-
当参数值具有二进制字符串类型且参数不是十六进制文字、位文字或
NULL文字时，会发生二进制字符串评估。否则会发生数值评估，必要时将参数值转换为无符号 64 位整数。 -
二进制字符串评估会产生与参数值相同长度的二进制字符串。如果参数值长度不相等，则会出现
ER_INVALID_BITWISE_OPERANDS_SIZE错误。如果参数大小超过 511 字节，则会出现ER_INVALID_BITWISE_AGGREGATE_OPERANDS_SIZE错误。数值评估会产生一个无符号 64 位整数。
NULL值不会影响结果，除非所有值都是NULL。在这种情况下，结果是一个中性值，其长度与参数值的长度相同（对于BIT_AND()为所有位为 1，对于BIT_OR()为所有位为 0，以及BIT_XOR()）。
例子：
mysql> CREATE TABLE t (group_id INT, a VARBINARY(6));
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (2, NULL);
mysql> INSERT INTO t VALUES (2, X'1234');
mysql> INSERT INTO t VALUES (2, X'FF34');
mysql> SELECT HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a))
FROM t GROUP BY group_id;
+-----------------+----------------+-----------------+
| HEX(BIT_AND(a)) | HEX(BIT_OR(a)) | HEX(BIT_XOR(a)) |
+-----------------+----------------+-----------------+
| FFFFFFFFFFFF | 000000000000 | 000000000000 |
| 1234 | FF34 | ED00 |
+-----------------+----------------+-----------------+
十六进制文字、位文字和 NULL 文字的特殊处理
为了向后兼容，当所有位参数为十六进制文字、位文字或NULL文字时，MySQL 8.0 会在数值上评估位操作。也就是说，如果所有位参数都是未修饰的十六进制文字、位文字或NULL文字，则对二进制字符串位参数的位操作不会使用二进制字符串评估。（如果它们是用_binary引导符、BINARY运算符或其他明确指定为二进制字符串的方式写入的，则不适用于这些文字。）
刚才描述的文字处理与 MySQL 8.0 之前的版本相同。例如：
-
这些位操作在数值上评估文字并产生一个
BIGINT结果：b'0001' | b'0010' X'0008' << 8 -
这些位操作在数值上评估
NULL并产生一个具有NULL值的BIGINT结果：NULL & NULL NULL >> 4
在 MySQL 8.0 中，您可以通过明确指示至少一个参数是二进制字符串来导致这些操作在二进制字符串上下文中评估参数：
_binary b'0001' | b'0010'
_binary X'0008' << 8
BINARY NULL & NULL
BINARY NULL >> 4
最后两个表达式的结果是NULL，就像没有BINARY运算符一样，但结果的数据类型是二进制字符串类型而不是整数类型。
与 MySQL 5.7 不兼容的位操作
因为 MySQL 8.0 可以原生处理二进制字符串参数的位操作，一些表达式在 MySQL 8.0 中产生的结果与 5.7 中不同。需要注意的五种问题表达式类型是：
*nonliteral_binary* { & | ^ } *binary*
*binary* { & | ^ } *nonliteral_binary*
*nonliteral_binary* { << >> } *anything*
~ *nonliteral_binary*
*AGGR_BIT_FUNC*(*nonliteral_binary*)
这些表达式在 MySQL 5.7 中返回BIGINT，在 8.0 中返回二进制字符串。
符号说明：
-
{ *op1* *op2* ... }：适用于给定表达式类型的运算符列表。 -
binary：任何类型的二进制字符串参数，包括十六进制文字、位文字或NULL文字。 -
nonliteral_binary：一个不是十六进制文字、位文字或NULL文字的二进制字符串值的参数。 -
AGGR_BIT_FUNC：一个接受位值参数的聚合函数：BIT_AND()，BIT_OR()，BIT_XOR()。
有关如何在 MySQL 5.7 中准备可能的 MySQL 5.7 和 8.0 之间不兼容性的信息，请参阅位函数和运算符，在 MySQL 5.7 参考手册中。
14.13 加密和压缩函数
原文：
dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
表 14.18 加密函数
| 名称 | 描述 |
|---|---|
AES_DECRYPT() |
使用 AES 解密 |
AES_ENCRYPT() |
使用 AES 加密 |
COMPRESS() |
以二进制字符串形式返回结果 |
MD5() |
计算 MD5 校验和 |
RANDOM_BYTES() |
返回一个随机字节向量 |
SHA1(), SHA() |
计算 SHA-1 160 位校验和 |
SHA2() |
计算 SHA-2 校验和 |
STATEMENT_DIGEST() |
计算语句摘要哈希值 |
STATEMENT_DIGEST_TEXT() |
计算规范语句摘要 |
UNCOMPRESS() |
解压缩压缩的字符串 |
UNCOMPRESSED_LENGTH() |
返回压缩前字符串的长度 |
VALIDATE_PASSWORD_STRENGTH() |
确定密码强度 |
| 名称 | 描述 |
许多加密和压缩函数返回的字符串可能包含任意字节值。如果您想存储这些结果，请使用具有VARBINARY或BLOB二进制字符串数据类型的列。这样可以避免由于使用非二进制字符串数据类型（CHAR, VARCHAR, TEXT）可能导致的尾随空格删除或字符集转换等潜在问题，从而改变数据值。
一些加密函数返回 ASCII 字符的字符串：MD5(), SHA(), SHA1(), SHA2(), STATEMENT_DIGEST(), STATEMENT_DIGEST_TEXT()。它们的返回值是一个由character_set_connection和collation_connection系统变量确定的具有字符集和排序规则的字符串。这是一个非二进制字符串，除非字符集是binary。
如果应用程序存储来自诸如MD5()或SHA1()的函数返回十六进制数字字符串的值，可以通过使用UNHEX()将十六进制表示转换为二进制形式并将结果存储在BINARY(*N*)列中获得更有效的存储和比较。每对十六进制数字在二进制形式中需要一个字节，因此N的值取决于十六进制字符串的长度。对于MD5()值，N为 16，对于SHA1()值为 20。对于SHA2()，N的范围从 28 到 32，具体取决于指定结果所需位长度的参数。
在CHAR列中存储十六进制字符串的大小惩罚至少是两倍，如果该值存储在使用utf8mb4字符集的列中（其中每个字符使用 4 个字节），则最多是八倍。存储字符串还会导致比较速度变慢，因为数值更大，需要考虑字符集排序规则。
假设一个应用程序将MD5()字符串值存储在CHAR(32)列中：
CREATE TABLE md5_tbl (md5_val CHAR(32), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(MD5('abcdef'), ...);
要将十六进制字符串转换为更紧凑的形式，请修改应用程序以使用UNHEX()和BINARY(16)如下：
CREATE TABLE md5_tbl (md5_val BINARY(16), ...);
INSERT INTO md5_tbl (md5_val, ...) VALUES(UNHEX(MD5('abcdef')), ...);
应用程序应准备处理散列函数为两个不同输入值产生相同值的极为罕见的情况。使碰撞可检测的一种方法是将哈希列设为主键。
注意
MD5 和 SHA-1 算法的漏洞已经被发现。您可能希望考虑使用本节中描述的其他单向加密函数，例如SHA2()。
警告
作为加密函数参数提供的密码或其他敏感值在未使用 SSL 连接的情况下以明文形式发送到 MySQL 服务器。此外，这些值会出现在写入的任何 MySQL 日志中。为了避免这些类型的暴露，应用程序可以在将敏感值发送到服务器之前在客户端端对其进行加密。相同的考虑也适用于加密密钥。为了避免暴露这些，应用程序可以使用存储过程在服务器端对值进行加密和解密。
-
AES_DECRYPT(*crypt_str*,*key_str*[,*init_vector*][,*kdf_name*][,*salt*][,*info | iterations*])此函数使用官方 AES（高级加密标准）算法解密数据。更多信息，请参阅
AES_ENCRYPT()的描述。使用
AES_DECRYPT()的语句对基于语句的复制不安全。 -
AES_ENCRYPT(*str*,*key_str*[,*init_vector*][,*kdf_name*][,*salt*][,*info | iterations*])AES_ENCRYPT()和AES_DECRYPT()实现使用官方 AES（高级加密标准）算法加密和解密数据，此前被称为“Rijndael”。AES 标准允许使用各种密钥长度。默认情况下，这些函数使用 128 位密钥长度实现 AES。可以使用 196 位或 256 位的密钥长度，如后面所述。密钥长度是性能和安全性之间的权衡。AES_ENCRYPT()使用密钥字符串key_str对字符串str进行加密，并返回包含加密输出的二进制字符串。AES_DECRYPT()使用密钥字符串key_str对加密字符串crypt_str进行解密，并以十六进制格式返回原始（二进制）字符串。（要将字符串作为明文获取，将结果转换为CHAR。或者，使用--skip-binary-as-hex启动mysql客户端，以使所有二进制值显示为文本。）如果任一函数参数为NULL，函数将返回NULL。如果AES_DECRYPT()检测到无效数据或不正确的填充，它将返回NULL。但是，如果输入数据或密钥无效，AES_DECRYPT()可能会返回一个非NULL值（可能是垃圾）。截至 MySQL 8.0.30，这些函数支持使用密钥派生函数（KDF）从传递给
key_str的信息创建一个密码强度强的秘密密钥。派生密钥用于加密和解密数据，并保留在 MySQL 服务器实例中，用户无法访问。强烈建议使用 KDF，因为它提供比指定自己预制密钥或通过更简单的方法派生密钥更好的安全性。这些函数支持 HKDF（自 OpenSSL 1.1.0 起可用），您可以指定一个可选的盐和上下文特定信息以包含在密钥材料中，以及 PBKDF2（自 OpenSSL 1.0.2 起可用），您可以指定一个可选的盐并设置用于生成密钥的迭代次数。AES_ENCRYPT()和AES_DECRYPT()允许控制块加密模式。block_encryption_mode系统变量控制基于块的加密算法的模式。其默认值为aes-128-ecb，表示使用 128 位密钥长度和 ECB 模式进行加密。有关此变量允许的值的描述，请参见第 7.1.8 节，“服务器系统变量”。可选的init_vector参数用于为需要初始化向量的块加密模式提供初始化向量。使用
AES_ENCRYPT()或AES_DECRYPT()的语句对于基于语句的复制是不安全的。如果在mysql客户端内调用
AES_ENCRYPT()，二进制字符串将使用十六进制表示，取决于--binary-as-hex的值。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — The MySQL Command-Line Client”。AES_ENCRYPT()和AES_DECRYPT()函数的参数如下：str用于
AES_ENCRYPT()加密的字符串，使用密钥字符串key_str，或者（从 MySQL 8.0.30 开始）使用指定 KDF 派生的密钥。字符串可以是任意长度。根据块为单位的算法（如 AES）的要求，str会自动添加填充以使其成为块的倍数。此填充会被AES_DECRYPT()函数自动移除。crypt_str用于
AES_DECRYPT()解密的加密字符串，使用密钥字符串key_str，或者（从 MySQL 8.0.30 开始）使用指定 KDF 派生的密钥。字符串可以是任意长度。crypt_str的长度可以根据原始字符串的长度使用以下公式计算：16 * (trunc(*string_length* / 16) + 1)key_str加密密钥，或者作为基础使用密钥派生函数（KDF）派生密钥的输入密钥材料。对于相同的数据实例，使用相同的
key_str值进行使用AES_ENCRYPT()进行加密和使用AES_DECRYPT()进行解密。如果您使用 KDF，可以从 MySQL 8.0.30 开始，
key_str可以是任意信息，如密码或口令。在函数的进一步参数中，您指定 KDF 名称，然后添加进一步选项以根据 KDF 适当增加安全性。当您使用 KDF 时，函数会从
key_str中传递的信息以及您在其他参数中提供的盐或其他信息创建一个密码学强大的密钥。派生密钥用于加密和解密数据，并保留在 MySQL 服务器实例中，用户无法访问。强烈建议使用 KDF，因为它提供比指定自己的预制密钥或通过更简单的方法派生密钥更好的安全性。如果不使用 KDF，对于 128 位的密钥长度，将密钥传递给
key_str参数的最安全方式是创建一个真正随机的 128 位值并将其作为二进制值传递。例如：INSERT INTO t VALUES (1,AES_ENCRYPT('text',UNHEX('F3229A0B371ED2D9441B830D21A390C3')));可以使用口令通过哈希口令生成 AES 密钥。例如：
INSERT INTO t VALUES (1,AES_ENCRYPT('text', UNHEX(SHA2('My secret passphrase',512))));如果超过 128 位的最大密钥长度，将返回警告。如果不使用 KDF，请不要直接将密码或口令传递给
key_str，先对其进行哈希处理。本文档的早期版本建议采用前一种方法，但不再建议，因为这里显示的示例更安全。init_vector用于需要的块加密模式的初始化向量。
block_encryption_mode系统变量控制模式。对于相同的数据实例，使用相同的init_vector值进行AES_ENCRYPT()加密和AES_DECRYPT()解密。注意
如果使用 KDF，必须为此参数指定一个初始化向量或空字符串，以便访问后续参数以定义 KDF。
对于需要初始化向量的模式，它必须是 16 字节或更长（超过 16 字节的字节将被忽略）。如果缺少
init_vector，则会发生错误。对于不需要初始化向量的模式，如果指定了init_vector，则会被忽略并生成警告，除非您使用 KDF。block_encryption_mode系统变量的默认值为aes-128-ecb，或 ECB 模式，不需要初始化向量。允许的替代块加密模式 CBC、CFB1、CFB8、CFB128 和 OFB 都需要初始化向量。可以通过调用
RANDOM_BYTES(16)生成一个用于初始化向量的随机字节字符串。kdf_name创建密钥派生函数（KDF）的名称，用于从传递给KDF的输入密钥材料中创建密钥，并根据 KDF 的要求提供其他参数。此可选参数从 MySQL 8.0.30 起可用。
对于相同的数据实例，使用相同的
kdf_name值进行AES_ENCRYPT()加密和AES_DECRYPT()解密。当指定kdf_name时，必须指定init_vector，使用有效的初始化向量或如果加密模式不需要初始化向量则使用空字符串。支持以下值：
hkdfHKDF，从 OpenSSL 1.1.0 起可用。HKDF 从密钥材料中提取一个伪随机密钥，然后将其扩展为其他密钥。使用 HKDF，您可以指定一个可选的盐（
salt）和上下文特定信息，如应用程序细节（info）以包含在密钥材料中。pbkdf2_hmacPBKDF2，从 OpenSSL 1.0.2 起可用。PBKDF2 将一个伪随机函数应用于密钥材料，并重复这个过程多次以生成密钥。使用 PBKDF2，您可以指定一个可选的盐（
salt）以包含在密钥材料中，并设置用于生成密钥的迭代次数（iterations）。在此示例中，HKDF 被指定为密钥派生函数，提供了盐和上下文信息。初始化向量的参数被包含在内，但为空字符串：
SELECT AES_ENCRYPT('mytext','mykeystring', '', 'hkdf', 'salt', 'info');在此示例中，PBKDF2 被指定为密钥派生函数，提供了盐，并且迭代次数是推荐最小值的两倍。
SELECT AES_ENCRYPT('mytext','mykeystring', '', 'pbkdf2_hmac','salt', '2000');salt传递给密钥派生函数（KDF）的盐。此可选参数从 MySQL 8.0.30 起可用。HKDF 和 PBKDF2 都可以使用盐，建议使用盐以帮助防止基于常见密码字典或彩虹表的攻击。
盐由随机数据组成，为了安全起见，每次加密操作必须使用不同的盐。可以通过调用
RANDOM_BYTES()生成一个随机字节字符串作为盐。以下示例生成一个 64 位盐：SET @salt = RANDOM_BYTES(8);对于相同的数据实例，使用相同的
salt值进行AES_ENCRYPT()加密和AES_DECRYPT()解密。盐可以安全地与加密数据一起存储。info用于在密钥材料中包含 HKDF 的上下文特定信息，例如有关应用程序的信息。当您将
hkdf指定为 KDF 名称时，此可选参数从 MySQL 8.0.30 起可用。HKDF 将此信息添加到key_str中指定的密钥材料和salt中指定的盐中以生成密钥。对于相同的数据实例，使用相同的
info值进行使用AES_ENCRYPT()进行加密和使用AES_DECRYPT()进行解密。迭代生成密钥时 PBKDF2 使用的迭代次数。当您将
pbkdf2_hmac作为 KDF 名称指定时，此可选参数从 MySQL 8.0.30 开始可用。较高的计数值使得对抗暴力破解攻击更加困难，因为对于攻击者来说，计算成本更高，但对于密钥派生过程也是如此。如果不指定此参数，则默认值为 1000，这是 OpenSSL 标准推荐的最低值。对于相同的数据实例，使用相同的
迭代值进行使用AES_ENCRYPT()进行加密和使用AES_DECRYPT()进行解密。mysql> SET block_encryption_mode = 'aes-256-cbc'; mysql> SET @key_str = SHA2('My secret passphrase',512); mysql> SET @init_vector = RANDOM_BYTES(16); mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector); mysql> SELECT CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR); +-------------------------------------------------------------+ | CAST(AES_DECRYPT(@crypt_str,@key_str,@init_vector) AS CHAR) | +-------------------------------------------------------------+ | text | +-------------------------------------------------------------+ -
COMPRESS(*string_to_compress*)压缩字符串并将结果作为二进制字符串返回。此函数要求 MySQL 已经使用诸如
zlib之类的压缩库进行编译。否则，返回值始终为NULL。如果string_to_compress为NULL，返回值也为NULL。压缩后的字符串可以使用UNCOMPRESS()进行解压缩。mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000))); -> 21 mysql> SELECT LENGTH(COMPRESS('')); -> 0 mysql> SELECT LENGTH(COMPRESS('a')); -> 13 mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16))); -> 15压缩后的字符串内容存储如下：
-
空字符串存储为空字符串。
-
非空字符串存储为未压缩字符串的 4 字节长度（低字节在前），后跟压缩字符串。如果字符串以空格结尾，则会添加额外的
.字符，以避免在将结果存储在CHAR或VARCHAR列中时出现末尾空格修剪问题。（但是，不建议使用诸如CHAR或VARCHAR之类的非二进制字符串数据类型来存储压缩字符串，因为可能会发生字符集转换。请改用VARBINARY或BLOB二进制字符串列。)
如果从mysql客户端中调用
COMPRESS()，二进制字符串将使用十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参见 Section 6.5.1, “mysql — The MySQL Command-Line Client”。 -
-
MD5(*str*)为字符串计算 MD5 128 位校验和。返回值是一个包含 32 个十六进制数字的字符串，如果参数为
NULL则返回NULL。返回值可以用作哈希键。有关有效存储哈希值的注意事项，请参阅本节开头的注释。返回值是连接字符集中的字符串。
如果启用了 FIPS 模式，
MD5()将返回NULL。请参阅第 8.8 节，“FIPS 支持”。mysql> SELECT MD5('testing'); -> 'ae2b1fca515949e5d54fb22b8ed95575'这是“RSA 数据安全公司 MD5 消息摘要算法”。
请参阅本节开头关于 MD5 算法的注意事项。
-
RANDOM_BYTES(*len*)此函数返回使用 SSL 库的随机数生成器生成的
len个随机字节的二进制字符串。允许的len值范围从 1 到 1024。对于超出该范围的值，将发生错误。如果len为NULL，则返回NULL。RANDOM_BYTES()可用于为AES_DECRYPT()和AES_ENCRYPT()函数提供初始化向量。在该上下文中使用时，len必须至少为 16。允许使用更大的值，但超过 16 的字节将被忽略。RANDOM_BYTES()生成一个随机值，使其结果是不确定的。因此，使用此函数的语句对于基于语句的复制是不安全的。如果从mysql客户端调用
RANDOM_BYTES()，则二进制字符串将使用十六进制表示，具体取决于--binary-as-hex的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。 -
SHA1(*str*),SHA(*str*)为字符串计算 SHA-1 160 位校验和，如 RFC 3174（安全哈希算法）中所述。返回值是一个包含 40 个十六进制数字的字符串，如果参数为
NULL则返回NULL。此函数的一个可能用途是作为哈希键。有关有效存储哈希值的注意事项，请参阅本节开头的注释。SHA()与SHA1()是同义词。返回值是连接字符集中的字符串。
mysql> SELECT SHA1('abc'); -> 'a9993e364706816aba3e25717850c26c9cd0d89d'SHA1()可以被视为MD5()的密码学上更安全的等价物。然而，请参阅本节开头关于 MD5 和 SHA-1 算法的注意事项。 -
SHA2(*str*, *hash_length*)计算 SHA-2 哈希函数族（SHA-224、SHA-256、SHA-384 和 SHA-512）。第一个参数是要进行哈希处理的明文字符串。第二个参数表示所需结果的位长度，必须为 224、256、384、512 或 0（等同于 256）。如果任一参数为
NULL或哈希长度不是允许的值之一，则返回值为NULL。否则，函数结果是包含所需位数的哈希值。请参阅本节开头关于高效存储哈希值的注意事项。返回值是连接字符集中的字符串。
mysql> SELECT SHA2('abc', 224); -> '23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7'仅当 MySQL 配置了 SSL 支持时，此函数才有效。请参阅 Section 8.3, “Using Encrypted Connections”。
SHA2()在密码学上比MD5()或SHA1()更安全。 -
STATEMENT_DIGEST(*statement*)给定一个作为字符串的 SQL 语句，返回连接字符集中的语句摘要哈希值作为字符串，如果参数为
NULL则返回NULL。相关的STATEMENT_DIGEST_TEXT()函数返回规范化语句摘要。有关语句摘要的信息，请参阅 Section 29.10, “Performance Schema Statement Digests and Sampling”。两个函数都使用 MySQL 解析器来解析语句。如果解析失败，将会出现错误。如果语句以文字字符串形式提供，则错误消息仅包含解析错误。
max_digest_length系统变量确定这些函数用于计算规范化语句摘要的最大字节数。mysql> SET @stmt = 'SELECT * FROM mytable WHERE cola = 10 AND colb = 20'; mysql> SELECT STATEMENT_DIGEST(@stmt); +------------------------------------------------------------------+ | STATEMENT_DIGEST(@stmt) | +------------------------------------------------------------------+ | 3bb95eeade896657c4526e74ff2a2862039d0a0fe8a9e7155b5fe492cbd78387 | +------------------------------------------------------------------+ mysql> SELECT STATEMENT_DIGEST_TEXT(@stmt); +----------------------------------------------------------+ | STATEMENT_DIGEST_TEXT(@stmt) | +----------------------------------------------------------+ | SELECT * FROM `mytable` WHERE `cola` = ? AND `colb` = ? | +----------------------------------------------------------+ -
STATEMENT_DIGEST_TEXT(*statement*)给定一个作为字符串的 SQL 语句，返回连接字符集中的规范化语句摘要作为字符串，如果参数为
NULL则返回NULL。有关更多讨论和示例，请参阅相关STATEMENT_DIGEST()函数的描述。 -
UNCOMPRESS(*string_to_uncompress*)解压由
COMPRESS()函数压缩的字符串。如果参数不是压缩值，则结果为NULL；如果string_to_uncompress为NULL，则结果也为NULL。此函数要求 MySQL 已经使用诸如zlib之类的压缩库进行编译。否则，返回值始终为NULL。mysql> SELECT UNCOMPRESS(COMPRESS('any string')); -> 'any string' mysql> SELECT UNCOMPRESS('any string'); -> NULL -
UNCOMPRESSED_LENGTH(*compressed_string*)返回压缩前的字符串长度。如果
compressed_string为NULL，则返回NULL。mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30))); -> 30 -
VALIDATE_PASSWORD_STRENGTH(*str*)给定表示明文密码的参数，此函数返回一个整数，指示密码的强度，如果参数为
NULL，则返回NULL。返回值范围从 0（弱）到 100（强）。由
VALIDATE_PASSWORD_STRENGTH()进行密码评估，由validate_password组件执行。如果未安装该组件，函数始终返回 0。有关安装validate_password的信息，请参见第 8.4.3 节，“密码验证组件”。要检查或配置影响密码测试的参数，请查看或设置validate_password实现的系统变量。请参见第 8.4.3.2 节，“密码验证选项和变量”。密码将经过越来越严格的测试，返回值反映了满足的测试，如下表所示。此外，如果启用了
validate_password.check_user_name系统变量，并且密码与用户名匹配，VALIDATE_PASSWORD_STRENGTH()无论如何返回 0，不管其他validate_password系统变量如何设置。密码测试 返回值 长度 < 4 0 长度 ≥ 4 且 < validate_password.length25 符合策略 1（ LOW）50 符合策略 2（ MEDIUM）75 符合策略 3（ STRONG）100
14.14 锁定函数
本节描述用于操作用户级别锁的函数。
表 14.19 锁定函数
| 名称 | 描述 |
|---|---|
GET_LOCK() |
获取命名锁 |
IS_FREE_LOCK() |
命名锁是否空闲 |
IS_USED_LOCK() |
命名锁是否正在使用；如果是，则返回连接标识符 |
RELEASE_ALL_LOCKS() |
释放所有当前命名锁 |
RELEASE_LOCK() |
释放命名锁 |
-
GET_LOCK(*str*,*timeout*)尝试使用字符串
str给出的名称以及timeout秒的超时获取锁。负的timeout值表示无限超时。该锁是排他的。当一个会话持有锁时，其他会话无法获取相同名称的锁。如果成功获取锁，则返回
1，如果尝试超时（例如，因为另一个客户端先前锁定了名称），则返回0，如果发生错误（例如，内存耗尽或线程被mysqladmin kill杀死），则返回NULL。使用
GET_LOCK()获取的锁通过执行RELEASE_LOCK()显式释放，或者在会话终止时（正常或异常）隐式释放。使用GET_LOCK()获取的锁在事务提交或回滚时不会释放。GET_LOCK()是使用元数据锁定（MDL）子系统实现的。可以获取多个同时锁，并且GET_LOCK()不会释放任何现有锁。例如，假设您执行以下语句：SELECT GET_LOCK('lock1',10); SELECT GET_LOCK('lock2',10); SELECT RELEASE_LOCK('lock2'); SELECT RELEASE_LOCK('lock1');第二个
GET_LOCK()获取第二个锁，两个RELEASE_LOCK()调用都返回 1（成功）。甚至可以让给定会话为同一名称获取多个锁。其他会话在获取会话释放该名称的所有锁之前无法获取具有该名称的锁。
使用
GET_LOCK()获取的具有唯一名称的锁会出现在性能模式metadata_locks表中。OBJECT_TYPE列显示USER LEVEL LOCK，OBJECT_NAME列指示锁名称。在为相同名称获取多个锁的情况下，只有第一个名称的锁会在metadata_locks表中注册一行。对于该名称的后续锁将在锁中递增计数器，但不会获取额外的元数据锁。当名称上的最后一个锁实例被释放时，锁的metadata_locks行将被删除。获得多个锁的能力意味着客户端之间可能发生死锁。当这种情况发生时，服务器会选择一个调用者，并以
ER_USER_LOCK_DEADLOCK错误终止其锁获取请求。此错误不会导致事务回滚。MySQL 对锁名称的最大长度强制为 64 个字符。
GET_LOCK()可用于实现应用程序锁或模拟记录锁。名称在整个服务器范围内被锁定。如果一个名称在一个会话中被锁定，那么GET_LOCK()会阻止另一个会话对具有相同名称的锁发出的任何请求。这使得同意给定锁名称的客户端可以使用该名称执行协作性咨询锁定。但请注意，这也使得不属于协作客户端集的客户端可以锁定一个名称，无论是无意还是故意，从而阻止任何协作客户端锁定该名称。减少这种可能性的一种方法是使用特定于数据库或应用程序的锁名称。例如，使用形式为db_name.str或app_name.str的锁名称。如果多个客户端正在等待一个锁，它们获取锁的顺序是不确定的。应用程序不应假设客户端按照发出锁请求的顺序获取锁。
当
binlog_format设置为STATEMENT时，GET_LOCK()对基于语句的复制是不安全的。如果在此函数在此情况下使用，将记录一个警告。由于
GET_LOCK()仅在单个mysqld上建立锁，因此不适用于 NDB Cluster，后者无法强制执行跨多个 MySQL 服务器的 SQL 锁。有关更多信息，请参见第 25.2.7.10 节，“与多个 NDB Cluster 节点相关的限制”。注意
具有获取多个命名锁的能力，一个语句可以获取大量的锁。例如：
INSERT INTO ... SELECT GET_LOCK(t1.col_name) FROM t1;这些类型的语句可能会产生某些不良影响。例如，如果语句在中途失败并回滚，则在失败点之前获取的锁仍然存在。如果意图是要求插入的行与获取的锁对应，那么这个意图就无法满足。此外，如果重要的是按特定顺序授予锁，请注意结果集顺序可能会因优化器选择的执行计划而有所不同。因此，最好限制每个语句对单个锁获取调用。
另一种不同的锁定接口可作为插件服务或一组可加载函数提供。该接口提供锁定命名空间和不同的读写锁，与
GET_LOCK()及相关函数提供的接口不同。有关详细信息，请参见第 7.6.9.1 节，“锁定服务”。 -
IS_FREE_LOCK(*str*)检查名为
str的锁是否可用（即未被锁定）。如果锁可用（没有人在使用锁），则返回1，如果锁正在使用，则返回0，如果发生错误（例如参数不正确），则返回NULL。此函数对基于语句的复制不安全。如果在
binlog_format设置为STATEMENT时使用此函数，将记录警告。 -
IS_USED_LOCK(*str*)检查名为
str的锁是否正在使用（即已锁定）。如果是，则返回持有锁的客户会话的连接标识符。否则，返回NULL。此函数对基于语句的复制不安全。如果在
binlog_format设置为STATEMENT时使用此函数，将记录警告。 -
RELEASE_ALL_LOCKS()释放当前会话持有的所有命名锁，并返回释放的锁数（如果没有锁则返回 0）
此函数对基于语句的复制不安全。如果在
binlog_format设置为STATEMENT时使用此函数，将记录警告。 -
RELEASE_LOCK(*str*)释放由字符串
str命名的锁，该锁是使用GET_LOCK()获取的。如果释放了锁，则返回1，如果该线程未建立锁（在这种情况下不会释放锁），则返回0，如果命名的锁不存在，则返回NULL。如果从未通过调用GET_LOCK()获取过锁，或者之前已释放锁，则该锁不存在。DO语句与RELEASE_LOCK()一起使用非常方便。请参阅第 15.2.3 节，“DO 语句”。当
binlog_format设置为STATEMENT时，此函数对基于语句的复制不安全。如果在此函数中使用binlog_format设置为STATEMENT时，将记录警告。
14.15 信息函数
原文：
dev.mysql.com/doc/refman/8.0/en/information-functions.html
表 14.20 信息函数
| 名称 | 描述 |
|---|---|
BENCHMARK() |
反复执行一个表达式 |
CHARSET() |
返回参数的字符集 |
COERCIBILITY() |
返回字符串参数的排序强制性值 |
COLLATION() |
返回字符串参数的排序规则 |
CONNECTION_ID() |
返回连接的连接 ID（线程 ID） |
CURRENT_ROLE() |
返回当前活动角色 |
CURRENT_USER(), CURRENT_USER |
认证用户的用户名和主机名 |
DATABASE() |
返回默认（当前）数据库名称 |
FOUND_ROWS() |
对于带有 LIMIT 子句的 SELECT，如果没有 LIMIT 子句，将返回的行数 |
ICU_VERSION() |
ICU 库版本 |
LAST_INSERT_ID() |
最后一次 INSERT 的 AUTOINCREMENT 列的值 |
ROLES_GRAPHML() |
返回表示内存角色子图的 GraphML 文档 |
ROW_COUNT() |
更新的行数 |
SCHEMA() |
DATABASE() 的同义词 |
SESSION_USER() |
USER() 的同义词 |
SYSTEM_USER() |
USER() 的同义词 |
USER() |
客户端提供的用户名和主机名 |
VERSION() |
返回指示 MySQL 服务器版本的字符串 |
| 名称 | 描述 |
-
BENCHMARK(*count*,*expr*)BENCHMARK()函数重复执行表达式exprcount次。它可用于计算 MySQL 处理表达式的速度。结果值为0，对于不合适的参数（如NULL或负重复计数）为NULL。预期的用法是在mysql客户端内部，报告查询执行时间：
mysql> SELECT BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye')); +---------------------------------------------------+ | BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye')) | +---------------------------------------------------+ | 0 | +---------------------------------------------------+ 1 row in set (4.74 sec)报告的时间是客户端端的经过时间，而不是服务器端的 CPU 时间。建议多次执行
BENCHMARK()，并根据服务器机器的负载情况解释结果。BENCHMARK()旨在衡量标量表达式的运行时性能，这对于您使用它和解释结果有一些重要影响：-
只能使用标量表达式。虽然表达式可以是子查询，但必须返回单列且最多一行。例如，如果表
t有多于一列或多于一行，则BENCHMARK(10, (SELECT * FROM t))会失败。 -
执行
SELECT *expr*语句N次与执行SELECT BENCHMARK(*N*, *expr*)在涉及的开销量方面有所不同。两者具有非常不同的执行概况，您不应该期望它们花费相同的时间。前者涉及解析器、优化器、表锁定和运行时评估各执行N次。后者仅涉及运行时评估N次，而所有其他组件仅执行一次。已分配的内存结构将被重用，并且运行时优化，例如对已为聚合函数评估的结果进行本地缓存，可能会改变结果。因此，使用BENCHMARK()通过给予该组件更多权重来衡量运行时组件的性能，并消除网络、解析器、优化器等引入的“噪音”。
-
-
CHARSET(*str*)返回字符串参数的字符集，如果参数为
NULL，则返回NULL。mysql> SELECT CHARSET('abc'); -> 'utf8mb3' mysql> SELECT CHARSET(CONVERT('abc' USING latin1)); -> 'latin1' mysql> SELECT CHARSET(USER()); -> 'utf8mb3' -
COERCIBILITY(*str*)返回字符串参数的排序强制性值。
mysql> SELECT COERCIBILITY('abc' COLLATE utf8mb4_swedish_ci); -> 0 mysql> SELECT COERCIBILITY(USER()); -> 3 mysql> SELECT COERCIBILITY('abc'); -> 4 mysql> SELECT COERCIBILITY(1000); -> 5返回值的含义如下表所示。较低的值具有更高的优先级。
强制性 含义 示例 0显式排序 带有 COLLATE子句的值1无排序 具有不同排序的字符串连接 2隐式排序 列值、存储过程参数或本地变量 3系统常量 USER()返回值4可强制 字面字符串 5数值 数值或时间值 6可忽略 NULL或从NULL派生的表达式更多信息，请参见第 12.8.4 节，“表达式中的排序强制性”。
-
COLLATION(*str*)返回字符串参数的排序。
mysql> SELECT COLLATION('abc'); -> 'utf8mb4_0900_ai_ci' mysql> SELECT COLLATION(_utf8mb4'abc'); -> 'utf8mb4_0900_ai_ci' mysql> SELECT COLLATION(_latin1'abc'); -> 'latin1_swedish_ci' -
CONNECTION_ID()返回连接的连接 ID（线程 ID）。每个连接都有一个在当前连接的客户端集合中唯一的 ID。
CONNECTION_ID()返回的值与信息模式PROCESSLIST表的ID列、SHOW PROCESSLIST输出的Id列以及性能模式threads表的PROCESSLIST_ID列中显示的值类型相同。mysql> SELECT CONNECTION_ID(); -> 23786警告
更改
pseudo_thread_id系统变量的会话值会更改CONNECTION_ID()函数返回的值。 -
CURRENT_ROLE()返回一个包含当前会话中当前活动角色的
utf8mb3字符串，用逗号分隔，如果没有则返回NONE。该值反映了sql_quote_show_create系统变量的设置。假设一个账户被授予以下角色：
GRANT 'r1', 'r2' TO 'u1'@'localhost'; SET DEFAULT ROLE ALL TO 'u1'@'localhost';在
u1的会话中，初始CURRENT_ROLE()值命名默认账户角色。使用SET ROLE更改该值：mysql> SELECT CURRENT_ROLE(); +-------------------+ | CURRENT_ROLE() | +-------------------+ | `r1`@`%`,`r2`@`%` | +-------------------+ mysql> SET ROLE 'r1'; SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | `r1`@`%` | +----------------+ -
CURRENT_USER,CURRENT_USER()返回 MySQL 服务器用于验证当前客户端的用户名称和主机名组合的账户。此账户确定您的访问权限。返回值是
utf8mb3字符集中的字符串。CURRENT_USER()的值可能与USER()的值不同。mysql> SELECT USER(); -> 'davida@localhost' mysql> SELECT * FROM mysql.user; ERROR 1044: Access denied for user ''@'localhost' to database 'mysql' mysql> SELECT CURRENT_USER(); -> '@localhost'该示例说明，尽管客户端指定了用户名为
davida（如USER()函数的值所示），但服务器却使用匿名用户账户对客户端进行了身份验证（如CURRENT_USER()值的空用户部分所示）。这种情况可能发生的一种方式是在授权表中没有列出davida的账户。在存储过程或视图中，
CURRENT_USER()返回定义对象的用户账户（由其DEFINER值给出），除非使用SQL SECURITY INVOKER特性定义。在后一种情况下，CURRENT_USER()返回对象的调用者。触发器和事件没有选项来定义
SQL SECURITY特性，因此对于这些对象，CURRENT_USER()返回定义对象的用户账户。要返回调用者，请使用USER()或SESSION_USER()。以下语句支持使用
CURRENT_USER()函数来代替受影响用户或定义者的名称（可能还有主机）；在这种情况下，CURRENT_USER()会根据需要进行扩展：-
DROP USER -
RENAME USER -
GRANT -
REVOKE -
CREATE FUNCTION -
CREATE PROCEDURE -
CREATE TRIGGER -
CREATE EVENT -
CREATE VIEW -
ALTER EVENT -
ALTER VIEW -
SET PASSWORD
对于这种扩展
CURRENT_USER()的含义的信息，参见 Section 19.5.1.8, “CURRENT_USER()的复制”的复制")。从 MySQL 8.0.34 开始，此函数可用于
VARCHAR或TEXT列的默认值，如下所示的CREATE TABLE语句：CREATE TABLE t (c VARCHAR(288) DEFAULT (CURRENT_USER())); -
-
DATABASE()返回以
utf8mb3字符集中的字符串形式的默认（当前）数据库名称。如果没有默认数据库，DATABASE()返回NULL。在存储过程中，默认数据库是与存储过程关联的数据库，并不一定与调用上下文中的默认数据库相同。mysql> SELECT DATABASE(); -> 'test'如果没有默认数据库，
DATABASE()返回NULL。 -
FOUND_ROWS()注意
自 MySQL 8.0.17 起，
SQL_CALC_FOUND_ROWS查询修饰符和相应的FOUND_ROWS()函数已被弃用；预计它们将在未来的 MySQL 版本中被移除。作为替代方案，考虑使用带有LIMIT的查询，然后再执行一个不带LIMIT但带有COUNT(*)的第二个查询，以确定是否有额外的行。例如，不要使用这些查询：SELECT SQL_CALC_FOUND_ROWS * FROM *tbl_name* WHERE id > 100 LIMIT 10; SELECT FOUND_ROWS();改用以下查询：
SELECT * FROM *tbl_name* WHERE id > 100 LIMIT 10; SELECT COUNT(*) FROM *tbl_name* WHERE id > 100;COUNT(*)受到某些优化的影响。SQL_CALC_FOUND_ROWS会导致某些优化被禁用。一个
SELECT语句可以包括一个LIMIT子句，以限制服务器返回给客户端的行数。在某些情况下，希望知道没有LIMIT时语句会返回多少行，但又不想再次运行该语句。要获取这个行数，需要在SELECT语句中包含一个SQL_CALC_FOUND_ROWS选项，然后在之后调用FOUND_ROWS()：mysql> SELECT SQL_CALC_FOUND_ROWS * FROM *tbl_name* -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();第二个
SELECT返回一个数字，指示第一个不带LIMIT子句的SELECT将返回多少行。在最近成功的不带
SQL_CALC_FOUND_ROWS选项的SELECT语句中，FOUND_ROWS()返回该语句返回的结果集中的行数。如果语句包含LIMIT子句，FOUND_ROWS()返回限制之前的行数。例如，如果语句包含LIMIT 10或LIMIT 50, 10，则FOUND_ROWS()分别返回 10 或 60。通过
FOUND_ROWS()获取的行数是瞬时的，不打算在SELECT SQL_CALC_FOUND_ROWS语句后的语句中使用。如果需要稍后引用该值，请保存它：mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ; mysql> SET @rows = FOUND_ROWS();如果你使用
SELECT SQL_CALC_FOUND_ROWS，MySQL 必须计算完整结果集中有多少行。然而，这比再次运行不带LIMIT的查询要快，因为不需要将结果集发送给客户端。SQL_CALC_FOUND_ROWS和FOUND_ROWS()在需要限制查询返回行数的情况下非常有用，同时又要确定完整结果集中的行数而不需要重新运行查询时。例如，一个 Web 脚本显示分页显示，包含指向显示搜索结果其他部分页面的链接。使用FOUND_ROWS()可以帮助确定还需要多少其他页面来显示剩余的结果。对于
UNION语句，使用SQL_CALC_FOUND_ROWS和FOUND_ROWS()比简单的SELECT语句更复杂，因为LIMIT可能出现在UNION中的多个位置。它可以应用于UNION中的各个SELECT语句，或者全局应用于整个UNION结果。SQL_CALC_FOUND_ROWS用于UNION的目的是应返回在没有全局LIMIT的情况下将返回的行数。使用SQL_CALC_FOUND_ROWS与UNION的条件是：-
SQL_CALC_FOUND_ROWS关键字必须出现在UNION的第一个SELECT中。 -
FOUND_ROWS()的值仅在使用UNION ALL时是精确的。如果使用不带ALL的UNION，会发生重复移除，并且FOUND_ROWS()的值只是近似值。 -
如果
UNION中没有LIMIT，则会忽略SQL_CALC_FOUND_ROWS并返回用于处理UNION的临时表中的行数。
除了这里描述的情况外，
FOUND_ROWS()的行为是未定义的（例如，在出现错误的SELECT语句后其值是多少）。重要
使用基于语句的复制时，
FOUND_ROWS()无法可靠地复制。此函数会在基于行的复制中自动复制。 -
-
ICU_VERSION()用于支持正则表达式操作的国际组件库（ICU）的版本（参见 Section 14.8.2, “Regular Expressions”）。此函数主要用于测试案例。
-
LAST_INSERT_ID(),LAST_INSERT_ID(*expr*)没有参数时，
LAST_INSERT_ID()返回一个BIGINT UNSIGNED（64 位）值，表示作为最近执行的INSERT语句的结果成功插入的第一个自动生成值，用于AUTO_INCREMENT列。如果没有成功插入行，则LAST_INSERT_ID()的值保持不变。有参数时，
LAST_INSERT_ID()返回一个无符号整数，如果参数为NULL，则返回NULL。例如，在插入生成
AUTO_INCREMENT值的行之后，您可以像这样获取该值：mysql> SELECT LAST_INSERT_ID(); -> 195当前执行的语句不会影响
LAST_INSERT_ID()的值。假设您使用一个语句生成AUTO_INCREMENT值，然后在一个多行INSERT语句中引用LAST_INSERT_ID()，该语句将行插入到具有自己的AUTO_INCREMENT列的表中。LAST_INSERT_ID()的值在第二个语句中保持稳定；其值对第二行及后续行不受先前行插入的影响。（您应该注意，如果混合引用LAST_INSERT_ID()和LAST_INSERT_ID(*expr*)，效果是未定义的。）如果前一个语句返回错误，则
LAST_INSERT_ID()的值是未定义的。对于事务表，如果由于错误而回滚语句，则LAST_INSERT_ID()的值将保持未定义。对于手动ROLLBACK，LAST_INSERT_ID()的值不会恢复到事务之前的值；它将保持在ROLLBACK点时的值。在存储过程（procedure）或触发器的主体内，
LAST_INSERT_ID()的值与在这些对象的主体外执行语句时的方式相同。存储过程或触发器对后续语句看到的LAST_INSERT_ID()的值的影响取决于存储过程的类型：-
如果存储过程执行改变
LAST_INSERT_ID()值的语句，那么这个改变的值会被调用存储过程后的语句所看到。 -
对于改变值的存储函数和触发器，在函数或触发器结束时值会被恢复，因此在其后的语句不会看到改变的值。
生成的 ID 在服务器上以每个连接为基础进行维护。这意味着函数返回给特定客户端的值是该客户端最近影响
AUTO_INCREMENT列的大多数最新语句生成的第一个AUTO_INCREMENT值。即使其他客户端生成了自己的AUTO_INCREMENT值，这个值也不会受到影响。这种行为确保每个客户端可以检索自己的 ID，而不必担心其他客户端的活动，也不需要锁定或事务。如果将行的
AUTO_INCREMENT列设置为非“魔术”值（即不是NULL且不是0），则LAST_INSERT_ID()的值不会改变。重要提示
如果使用单个
INSERT语句插入多行，LAST_INSERT_ID()仅返回第一插入行生成的值。这样做的原因是为了能够轻松地在其他服务器上重现相同的INSERT语句。例如：
mysql> USE test; mysql> CREATE TABLE t ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL ); mysql> INSERT INTO t VALUES (NULL, 'Bob'); mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ mysql> INSERT INTO t VALUES (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa'); mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | +----+------+ mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+尽管第二个
INSERT语句向t插入了三行新记录，但为这些行中的第一行生成的 ID 是2，并且这个值会被后续的SELECT语句中的LAST_INSERT_ID()返回。如果使用
INSERT IGNORE并且行被忽略，LAST_INSERT_ID()保持不变（或者如果连接尚未执行成功的INSERT，则返回 0），对于非事务表，AUTO_INCREMENT计数器不会增加。对于InnoDB表，如果innodb_autoinc_lock_mode设置为1或2，AUTO_INCREMENT计数器会增加，如下例所示：mysql> USE test; mysql> SELECT @@innodb_autoinc_lock_mode; +----------------------------+ | @@innodb_autoinc_lock_mode | +----------------------------+ | 1 | +----------------------------+ mysql> CREATE TABLE `t` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `val` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `i1` (`val`) ) ENGINE=InnoDB; # Insert two rows mysql> INSERT INTO t (val) VALUES (1),(2); # With auto_increment_offset=1, the inserted rows # result in an AUTO_INCREMENT value of 3 mysql> SHOW CREATE TABLE t\G *************************** 1\. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `i1` (`val`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # LAST_INSERT_ID() returns the first automatically generated # value that is successfully inserted for the AUTO_INCREMENT column mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ # The attempted insertion of duplicate rows fail but errors are ignored mysql> INSERT IGNORE INTO t (val) VALUES (1),(2); Query OK, 0 rows affected (0.00 sec) Records: 2 Duplicates: 2 Warnings: 0 # With innodb_autoinc_lock_mode=1, the AUTO_INCREMENT counter # is incremented for the ignored rows mysql> SHOW CREATE TABLE t\G *************************** 1\. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `i1` (`val`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # The LAST_INSERT_ID is unchanged because the previous insert was unsuccessful mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+有关更多信息，请参阅 Section 17.6.1.6, “InnoDB 中的 AUTO_INCREMENT 处理”。
如果将
expr作为参数传递给LAST_INSERT_ID()，则函数将返回参数的值，并记住作为下一个由LAST_INSERT_ID()返回的值。这可以用来模拟序列：-
创建一个表来保存序列计数器并初始化它：
mysql> CREATE TABLE sequence (id INT NOT NULL); mysql> INSERT INTO sequence VALUES (0); -
使用表来生成类似这样的序列号：
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql> SELECT LAST_INSERT_ID();UPDATE语句递增序列计数器，并导致下一次调用LAST_INSERT_ID()返回更新后的值。SELECT语句检索该值。mysql_insert_id()C API 函数也可用于获取该值。请参阅 mysql_insert_id()。
您可以在不调用
LAST_INSERT_ID()的情况下生成序列，但以这种方式使用函数的实用性在于 ID 值在服务器中作为最后一个自动生成的值保持。它是多用户安全的，因为多个客户端可以发出UPDATE语句并使用SELECT语句（或mysql_insert_id()）获取自己的序列值，而不会影响或受其他生成自己序列值的客户端的影响。请注意，只有在执行
INSERT和UPDATE语句后，mysql_insert_id()才会更新，因此您不能在执行其他 SQL 语句（如SELECT或SET）后使用 C API 函数检索LAST_INSERT_ID(*expr*)的值。 -
-
ROLES_GRAPHML()返回一个包含表示内存角色子图的 GraphML 文档的
utf8mb3字符串。需要ROLE_ADMIN权限（或已弃用的SUPER权限）才能查看<graphml>元素中的内容。否则，结果只显示一个空元素：mysql> SELECT ROLES_GRAPHML(); +---------------------------------------------------+ | ROLES_GRAPHML() | +---------------------------------------------------+ | <?xml version="1.0" encoding="UTF-8"?><graphml /> | +---------------------------------------------------+ -
ROW_COUNT()ROW_COUNT()返回如下值：-
DDL 语句：0。这适用于诸如
CREATE TABLE或DROP TABLE之类的语句。 -
除了
SELECT之外的 DML 语句：受影响的行数。这适用于诸如UPDATE、INSERT或DELETE（如前所述）的语句，但现在也适用于诸如ALTER TABLE和LOAD DATA的语句。 -
SELECT：如果语句返回结果集，则返回 -1，否则返回“受影响”的行数。例如，对于SELECT * FROM t1，ROW_COUNT()返回 -1。对于SELECT * FROM t1 INTO OUTFILE '*file_name*'，ROW_COUNT()返回写入文件的行数。 -
SIGNAL语句：0。
对于
UPDATE语句，默认情况下受影响的行数是实际更改的行数。如果在连接到 mysqld 时使用CLIENT_FOUND_ROWS标志到mysql_real_connect()，受影响的行数是“找到”的行数；也就是，被WHERE子句匹配的行数。对于
REPLACE语句，如果新行替换了旧行，则受影响的行数为 2，因为在这种情况下，删除重复项后插入了一行。对于
INSERT ... ON DUPLICATE KEY UPDATE语句，每行的受影响行数为 1（如果将行插入为新行）、2（如果更新现有行）或 0（如果将现有行设置为当前值）。如果指定了CLIENT_FOUND_ROWS标志，则如果将现有行设置为当前值，则受影响的行数为 1（而不是 0）。ROW_COUNT()的值类似于mysql_affected_rows()C API 函数的值以及 mysql 客户端在语句执行后显示的行数。mysql> INSERT INTO t VALUES(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT ROW_COUNT(); +-------------+ | ROW_COUNT() | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql> DELETE FROM t WHERE i IN(1,2); Query OK, 2 rows affected (0.00 sec) mysql> SELECT ROW_COUNT(); +-------------+ | ROW_COUNT() | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)重要
ROW_COUNT()在基于语句的复制中无法可靠地复制。此函数会自动使用基于行的复制进行复制。 -
-
SCHEMA()此函数是
DATABASE()的同义词。 -
SESSION_USER()SESSION_USER()是USER()的同义词。从 MySQL 8.0.34 开始，类似于
USER()，这个函数可以用作VARCHAR或TEXT列的默认值，如下所示的CREATE TABLE语句：CREATE TABLE t (c VARCHAR(288) DEFAULT (SESSION_USER())); -
SYSTEM_USER()SYSTEM_USER()是USER()的同义词。注意
SYSTEM_USER()函数与SYSTEM_USER权限是不同的。前者返回当前的 MySQL 账户名。后者区分系统用户和普通用户账户类别（参见第 8.2.11 节，“账户类别”）。从 MySQL 8.0.34 开始，类似于
USER()，这个函数可以用作VARCHAR或TEXT列的默认值，如下所示的CREATE TABLE语句：CREATE TABLE t (c VARCHAR(288) DEFAULT (SYSTEM_USER())); -
USER()返回当前的 MySQL 用户名和主机名作为一个字符串，使用
utf8mb3字符集。mysql> SELECT USER(); -> 'davida@localhost'该值指示您连接到服务器时指定的用户名，以及您连接的客户端主机。该值可能与
CURRENT_USER()的值不同。从 MySQL 8.0.34 开始，类似于
USER()，这个函数可以用作VARCHAR或TEXT列的默认值，如下所示的CREATE TABLE语句：CREATE TABLE t (c VARCHAR(288) DEFAULT (USER())); -
VERSION()返回一个指示 MySQL 服务器版本的字符串。该字符串使用
utf8mb3字符集。该值可能除了版本号外还有后缀。请参阅第 7.1.8 节，“服务器系统变量”中的version系统变量的描述。此函数在基于语句的复制中是不安全的。如果在
binlog_format设置为STATEMENT时使用此函数，将记录警告。mysql> SELECT VERSION(); -> '8.0.36-standard'
14.16 空间分析函数
原文：
dev.mysql.com/doc/refman/8.0/en/spatial-analysis-functions.html
14.16.1 空间函数参考
14.16.2 空间函数参数处理
14.16.3 从 WKT 值创建几何值的函数
14.16.4 从 WKB 值创建几何值的函数
14.16.5 MySQL 特定函数创建几何值
14.16.6 几何格式转换函数
14.16.7 几何属性函数
14.16.8 空间操作函数
14.16.9 测试几何对象之间空间关系的函数
14.16.10 空间 Geohash 函数
14.16.11 空间 GeoJSON 函数
14.16.12 空间聚合函数
14.16.13 空间便利函数
MySQL 提供了执行空间数据各种操作的函数。这些函数可以根据它们执行的操作类型分为几个主要类别：
-
以各种格式（WKT、WKB、内部）创建几何体的函数
-
在不同格式之间转换几何体的函数
-
访问几何体的定性或定量属性的函数
-
描述两个几何体之间关系的函数
-
从现有几何体创建新几何体的函数
有关 MySQL 支持使用空间数据的一般背景，请参见第 13.4 节，“空间数据类型”。
14.16.1 空间函数参考
原文：
dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html
以下表格列出了每个空间函数并提供了每个函数的简短描述。
表格 14.21 空间函数
| 名称 | 描述 | 引入版本 |
|---|---|---|
GeomCollection() |
从几何图形构造几何集合 | |
GeometryCollection() |
从几何图形构造几何集合 | |
LineString() |
从 Point 值构造 LineString | |
MBRContains() |
判断一个几何图形的 MBR 是否包含另一个几何图形的 MBR | |
MBRCoveredBy() |
判断一个 MBR 是否被另一个 MBR 覆盖 | |
MBRCovers() |
判断一个 MBR 是否覆盖另一个 MBR | |
MBRDisjoint() |
判断两个几何图形的 MBR 是否不相交 | |
MBREquals() |
判断两个几何图形的 MBR 是否相等 | |
MBRIntersects() |
判断两个几何图形的 MBR 是否相交 | |
MBROverlaps() |
判断两个几何图形的 MBR 是否重叠 | |
MBRTouches() |
判断两个几何图形的 MBR 是否相接触 | |
MBRWithin() |
判断一个几何图形的 MBR 是否在另一个几何图形的 MBR 内部 | |
MultiLineString() |
从 LineString 值构造 MultiLineString | |
MultiPoint() |
从 Point 值构造 MultiPoint | |
MultiPolygon() |
从多边形值构造 MultiPolygon | |
Point() |
从坐标构造点 | |
Polygon() |
从 LineString 参数构造多边形 | |
ST_Area() |
返回多边形或 MultiPolygon 的面积 | |
ST_AsBinary(), ST_AsWKB() |
将内部几何格式转换为 WKB | |
ST_AsGeoJSON() |
从几何图形生成 GeoJSON 对象 | |
ST_AsText(), ST_AsWKT() |
从内部几何格式转换为 WKT | |
ST_Buffer() |
返回距离给定距离内的几何体的几何体 | |
ST_Buffer_Strategy() |
为 ST_Buffer()生成策略选项 | |
ST_Centroid() |
返回几何体的质心点 | |
ST_Collect() |
将空间值聚合成集合 | 8.0.24 |
ST_Contains() |
一个几何体是否包含另一个 | |
ST_ConvexHull() |
返回几何体的凸包 | |
ST_Crosses() |
一个几何体是否与另一个相交 | |
ST_Difference() |
两个几何体的点集差 | |
ST_Dimension() |
几何体的维度 | |
ST_Disjoint() |
一个几何体是否与另一个不相交 | |
ST_Distance() |
一个几何体到另一个的距离 | |
ST_Distance_Sphere() |
两个几何体在地球上的最小距离 | |
ST_EndPoint() |
线串的终点 | |
ST_Envelope() |
返回几何体的最小边界矩形 | |
ST_Equals() |
一个几何体是否等于另一个 | |
ST_ExteriorRing() |
返回多边形的外环 | |
ST_FrechetDistance() |
一个几何体到另一个的离散弗雷歇距离 | 8.0.23 |
ST_GeoHash() |
生成地理哈希值 | |
ST_GeomCollFromText(), ST_GeometryCollectionFromText(), ST_GeomCollFromTxt() |
从 WKT 返回几何体集合 | |
ST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB() |
从 WKB 返回几何体集合 | |
ST_GeometryN() |
从几何体集合返回第 N 个几何体 | |
ST_GeometryType() |
返回几何类型的名称 | |
ST_GeomFromGeoJSON() |
从 GeoJSON 对象生成几何图形 | |
ST_GeomFromText(), ST_GeometryFromText() |
从 WKT 返回几何图形 | |
ST_GeomFromWKB(), ST_GeometryFromWKB() |
从 WKB 返回几何图形 | |
ST_HausdorffDistance() |
一个几何图形到另一个的离散豪斯多夫距离 | 8.0.23 |
ST_InteriorRingN() |
返回多边形的第 N 个内环 | |
ST_Intersection() |
返回两个几何图形的点集交集 | |
ST_Intersects() |
一个几何图形是否与另一个相交 | |
ST_IsClosed() |
几何图形是否闭合且简单 | |
ST_IsEmpty() |
几何图形是否为空 | |
ST_IsSimple() |
几何图形是否简单 | |
ST_IsValid() |
几何图形是否有效 | |
ST_LatFromGeoHash() |
从 geohash 值返回纬度 | |
ST_Latitude() |
返回 Point 的纬度 | 8.0.12 |
ST_Length() |
返回 LineString 的长度 | |
ST_LineFromText(), ST_LineStringFromText() |
从 WKT 构造 LineString | |
ST_LineFromWKB(), ST_LineStringFromWKB() |
从 WKB 构造 LineString | |
ST_LineInterpolatePoint() |
沿着 LineString 给定百分比的点 | 8.0.24 |
ST_LineInterpolatePoints() |
沿着 LineString 给定百分比的点 | 8.0.24 |
ST_LongFromGeoHash() |
从 geohash 值返回经度 | |
ST_Longitude() |
返回 Point 的经度 | 8.0.12 |
ST_MakeEnvelope() |
两点周围的矩形 | |
ST_MLineFromText(), ST_MultiLineStringFromText() |
从 WKT 构建 MultiLineString | |
ST_MLineFromWKB(), ST_MultiLineStringFromWKB() |
从 WKB 构建 MultiLineString | |
ST_MPointFromText(), ST_MultiPointFromText() |
从 WKT 构建 MultiPoint | |
ST_MPointFromWKB(), ST_MultiPointFromWKB() |
从 WKB 构建 MultiPoint | |
ST_MPolyFromText(), ST_MultiPolygonFromText() |
从 WKT 构建 MultiPolygon | |
ST_MPolyFromWKB(), ST_MultiPolygonFromWKB() |
从 WKB 构建 MultiPolygon | |
ST_NumGeometries() |
返回几何集合中的几何对象数量 | |
ST_NumInteriorRing(), ST_NumInteriorRings() |
返回多边形中内部环的数量 | |
ST_NumPoints() |
返回 LineString 中的点数 | |
ST_Overlaps() |
一个几何对象是否与另一个重叠 | |
ST_PointAtDistance() |
沿着 LineString 给定距离的点 | 8.0.24 |
ST_PointFromGeoHash() |
将 geohash 值转换为 POINT 值 | |
ST_PointFromText() |
从 WKT 构建 Point | |
ST_PointFromWKB() |
从 WKB 构建 Point | |
ST_PointN() |
返回 LineString 中的第 N 个点 | |
ST_PolyFromText(), ST_PolygonFromText() |
从 WKT 构建 Polygon | |
ST_PolyFromWKB(), ST_PolygonFromWKB() |
从 WKB 构建 Polygon | |
ST_Simplify() |
返回简化的几何对象 | |
ST_SRID() |
返回几何对象的空间参考系统 ID | |
ST_StartPoint() |
LineString 的起始点 | |
ST_SwapXY() |
返回交换 X/Y 坐标的参数 | |
ST_SymDifference() |
返回两个几何对象的点集对称差 | |
ST_Touches() |
一个几何体是否与另一个几何体相接触 | |
ST_Transform() |
转换几何体的坐标 | 8.0.13 |
ST_Union() |
返回两个几何体的点集并集 | |
ST_Validate() |
返回经过验证的几何体 | |
ST_Within() |
一个几何体是否在另一个几何体内部 | |
ST_X() |
返回点的 X 坐标 | |
ST_Y() |
返回点的 Y 坐标 | |
| 名称 | 描述 | 引入版本 |
14.16.2 空间函数的参数处理
原文：
dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.html
空间值或几何图形具有第 13.4.2.2 节，“几何类”中描述的属性。以下讨论列出了一般空间函数参数处理特性。特定函数或函数组可能具有额外或不同的参数处理特性，如在这些函数描述出现的部分中所讨论的那样。在这种情况下，这些描述优先于此处的一般讨论。
空间函数仅针对有效的几何值定义。请参阅第 13.4.4 节，“几何形态和有效性”。
每个几何值都与空间参考系统（SRS）相关联，这是用于地理位置的基于坐标的系统。请参阅第 13.4.5 节，“空间参考系统支持”。
几何的空间参考标识符（SRID）标识定义几何的 SRS。在 MySQL 中，SRID 值是与几何值关联的整数。可用的最大 SRID 值为 2³²−1。如果给出一个更大的值，则只使用低 32 位。
SRID 0 表示一个无限的平面笛卡尔平面，其轴没有分配单位。要确保 SRID 0 行为，使用 SRID 0 创建几何值。如果未指定 SRID，则 SRID 0 是新几何值的默认值。
对于多个几何值的计算，所有值必须在相同的 SRS 中，否则会出错。因此，需要多个几何参数的空间函数要求这些参数在相同的 SRS 中。如果空间函数返回ER_GIS_DIFFERENT_SRIDS，这意味着几何参数不都在相同的 SRS 中。您必须修改它们以具有相同的 SRS。
空间函数返回的几何图形位于几何参数的 SRS 中，因为任何空间函数产生的几何值都继承了几何参数的 SRID。
开放地理空间联盟的指南要求输入多边形必须已经封闭，因此未封闭的多边形将被拒绝为无效而不是被封闭。
在 MySQL 中，唯一有效的空几何图形以空几何集合的形式表示。空几何集合的处理如下：可以将空的 WKT 输入几何集合指定为'GEOMETRYCOLLECTION()'。这也是产生空几何集合的空间操作的输出 WKT。
在解析嵌套几何集合时，集合被展平，并且其基本组件被用于各种 GIS 操作以计算结果。这为用户提供了额外的灵活性，因为不必担心几何数据的唯一性。嵌套几何集合可以从嵌套的 GIS 函数调用中产生，而无需事先显式展平。
14.16.3 从 WKT 值创建几何值的函数
这些函数的参数是一个 Well-Known Text（WKT）表示，可选地，一个空间参考系统标识符（SRID）。它们返回相应的几何。有关 WKT 格式的描述，请参阅 Well-Known Text (WKT) Format Format")。
本节中的函数检测笛卡尔或地理空间参考系统（SRS）中的参数，并返回适合 SRS 的结果。
ST_GeomFromText()接受任何几何类型的 WKT 值作为其第一个参数。其他函数提供了针对每种几何类型构造几何值的特定类型构造函数。
诸如ST_MPointFromText()和ST_GeomFromText()这样接受MultiPoint值的 WKT 格式表示的函数允许值内的各个点被括在括号中。例如，以下两个函数调用都是有效的：
ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')
诸如ST_GeomFromText()这样接受 WKT 几何集合参数的函数理解 OpenGIS 'GEOMETRYCOLLECTION EMPTY'标准语法和 MySQL 'GEOMETRYCOLLECTION()'非标准语法。诸如ST_AsWKT()这样产生 WKT 值的函数产生'GEOMETRYCOLLECTION EMPTY'标准语法：
mysql> SET @s1 = ST_GeomFromText('GEOMETRYCOLLECTION()');
mysql> SET @s2 = ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
mysql> SELECT ST_AsWKT(@s1), ST_AsWKT(@s2);
+--------------------------+--------------------------+
| ST_AsWKT(@s1) | ST_AsWKT(@s2) |
+--------------------------+--------------------------+
| GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY |
+--------------------------+--------------------------+
除非另有说明，本节中的函数处理其几何参数如下：
-
如果任何几何参数为
NULL或不是语法上良好形式的几何，或者 SRID 参数为NULL，则返回值为NULL。 -
默认情况下，地理坐标（纬度、经度）按照几何参数的空间参考系统指定的顺序进行解释。可以提供一个可选的
options参数来覆盖默认的轴顺序。options由逗号分隔的*key*=*value*列表组成。唯一允许的*key*值是axis-order，允许的值为lat-long、long-lat和srid-defined`（默认值）。如果
options参数为NULL，则返回值为NULL。如果options参数无效，则会发生错误以指示原因。 -
如果 SRID 参数引用未定义的空间参考系统（SRS），则会发生
ER_SRS_NOT_FOUND错误。 -
对于地理 SRS 几何参数，如果任何参数的经度或纬度超出范围，则会发生错误：
-
如果经度值不在范围（−180, 180]内，则会发生
ER_LONGITUDE_OUT_OF_RANGE错误。 -
如果纬度值不在范围[−90, 90]内，则会发生
ER_LATITUDE_OUT_OF_RANGE错误。
显示的范围以度为单位。如果 SRS 使用另一个单位，则范围使用其单位中的相应值。由于浮点运算，确切的范围限制略有偏差。
-
可用于从 WKT 值创建几何体的这些函数：
-
ST_GeomCollFromText(*wkt* [, *srid* [, *options*]]),ST_GeometryCollectionFromText(*wkt* [, *srid* [, *options*]]),ST_GeomCollFromTxt(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造
GeometryCollection值。这些函数处理它们的参数如本节介绍的那样。
mysql> SET @g = "MULTILINESTRING((10 10, 11 11), (9 9, 10 10))"; mysql> SELECT ST_AsText(ST_GeomCollFromText(@g)); +--------------------------------------------+ | ST_AsText(ST_GeomCollFromText(@g)) | +--------------------------------------------+ | MULTILINESTRING((10 10,11 11),(9 9,10 10)) | +--------------------------------------------+ -
ST_GeomFromText(*wkt* [, *srid* [, *options*]]),ST_GeometryFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造任何类型的几何值。
这些函数处理它们的参数如本节介绍的那样。
-
ST_LineFromText(*wkt* [, *srid* [, *options*]]),ST_LineStringFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造
LineString值。这些函数处理它们的参数如本节介绍的那样。
-
ST_MLineFromText(*wkt* [, *srid* [, *options*]]),ST_MultiLineStringFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造
MultiLineString值。这些函数处理它们的参数如本节介绍的那样。
-
ST_MPointFromText(*wkt* [, *srid* [, *options*]]),ST_MultiPointFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造
MultiPoint值。这些函数处理它们的参数如本节介绍的那样。
-
ST_MPolyFromText(*wkt* [, *srid* [, *options*]]),ST_MultiPolygonFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构造
MultiPolygon值。这些函数处理它们的参数，如本节介绍中所述。
-
ST_PointFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构建一个
Point值。ST_PointFromText()处理它的参数，如本节介绍中所述。 -
ST_PolyFromText(*wkt* [, *srid* [, *options*]]),ST_PolygonFromText(*wkt* [, *srid* [, *options*]])使用其 WKT 表示和 SRID 构建一个
Polygon值。这些函数处理它们的参数，如本节介绍中所述。
14.16.4 从 WKB 值创建几何值的函数
这些函数的参数是包含 Well-Known Binary (WKB) 表示的 BLOB，可选地还有空间参考系统标识符（SRID）。它们返回相应的几何图形。有关 WKB 格式的描述，请参阅 Well-Known Binary (WKB) 格式 格式")。
本节中的函数检测笛卡尔或地理空间参考系统（SRS）中的参数，并返回适合 SRS 的结果。
ST_GeomFromWKB() 接受任何几何类型的 WKB 值作为其第一个参数。其他函数为每种几何类型的几何值构造提供类型特定的构造函数。
在 MySQL 8.0 之前，这些函数还接受由 第 14.16.5 节，“MySQL 特定函数创建几何值” 中的函数返回的几何对象。不再允许几何参数，并产生错误。要将调用从使用几何参数迁移到使用 WKB 参数，请遵循以下准则：
-
重写类似
ST_GeomFromWKB(Point(0, 0))的结构为Point(0, 0)。 -
重写类似
ST_GeomFromWKB(Point(0, 0), 4326)的结构为ST_SRID(Point(0, 0), 4326)或ST_GeomFromWKB(ST_AsWKB(Point(0, 0)), 4326)。
除非另有说明，本节中的函数处理其几何参数如下：
-
如果 WKB 或 SRID 参数为
NULL，则返回值为NULL。 -
默认情况下，地理坐标（纬度、经度）按照几何参数的空间参考系统指定的顺序解释。可以提供一个可选的
options参数来覆盖默认轴顺序。options包括一个逗号分隔的*key*=*value*列表。唯一允许的key值是axis-order，其允许的值为lat-long、long-lat和srid-defined（默认值）。如果
options参数为NULL，则返回值为NULL。如果options参数无效，则会发生错误以指示原因。 -
如果 SRID 参数引用未定义的空间参考系统（SRS），则会发生
ER_SRS_NOT_FOUND错误。 -
对于地理 SRS 几何参数，如果任何参数的经度或纬度超出范围，则会发生错误：
-
如果经度值不在范围 (−180, 180] 内，则会发生
ER_LONGITUDE_OUT_OF_RANGE错误。 -
如果纬度值不在范围[−90, 90]内，则会发生
ER_LATITUDE_OUT_OF_RANGE错误。
显示的范围是以度为单位。如果 SRS 使用另一个单位，则范围使用其单位中的相应值。由于浮点运算，确切的范围限制略有偏差。
-
这些函数可用于从 WKB 值创建几何图形：
-
ST_GeomCollFromWKB(*wkb* [, *srid* [, *options*]]),ST_GeometryCollectionFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
GeometryCollection值。这些函数处理它们的参数，就像本节介绍的那样。
-
ST_GeomFromWKB(*wkb* [, *srid* [, *options*]]),ST_GeometryFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造任何类型的几何值。
这些函数处理它们的参数，就像本节介绍的那样。
-
ST_LineFromWKB(*wkb* [, *srid* [, *options*]]),ST_LineStringFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
LineString值。这些函数处理它们的参数，就像本节介绍的那样。
-
ST_MLineFromWKB(*wkb* [, *srid* [, *options*]]),ST_MultiLineStringFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
MultiLineString值。这些函数处理它们的参数，就像本节介绍的那样。
-
ST_MPointFromWKB(*wkb* [, *srid* [, *options*]]),ST_MultiPointFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
MultiPoint值。这些函数处理它们的参数，就像本节介绍的那样。
-
ST_MPolyFromWKB(*wkb* [, *srid* [, *options*]]),ST_MultiPolygonFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
MultiPolygon值。这些函数处理它们的参数，就像本节介绍的那样。
-
ST_PointFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
Point值。ST_PointFromWKB()按照本节介绍的方式处理它的参数。 -
ST_PolyFromWKB(*wkb* [, *srid* [, *options*]]),ST_PolygonFromWKB(*wkb* [, *srid* [, *options*]])使用其 WKB 表示和 SRID 构造一个
Polygon值。这些函数按照本节介绍的方式处理它们的参数。
14.16.5 创建几何值的 MySQL 特定函数
原文：
dev.mysql.com/doc/refman/8.0/en/gis-mysql-specific-functions.html
MySQL 提供了一组有用的非标准函数来创建几何值。本节中描述的函数是 MySQL 对 OpenGIS 规范的扩展。
这些函数从 WKB 值或几何对象作为参数生成几何对象。如果任何参数不是适当的 WKB 或几何表示适当对象类型的表示，则返回值为NULL。
例如，您可以直接将Point()的几何返回值插入到POINT列中：
INSERT INTO t1 (pt_col) VALUES(Point(1,2));
-
GeomCollection(*g* [, *g*] ...)从几何参数构造
GeomCollection值。GeomCollection()返回所有包含在参数中的适当几何体，即使存在不受支持的几何体也是如此。GeomCollection()允许不带参数作为创建空几何体的一种方式。此外，接受 WKT 几何集参数的函数，如ST_GeomFromText()，理解 OpenGIS 的'GEOMETRYCOLLECTION EMPTY'标准语法和 MySQL 的'GEOMETRYCOLLECTION()'非标准语法。GeomCollection()和GeometryCollection()是同义词���首选函数为GeomCollection()。 -
GeometryCollection(*g* [, *g*] ...)从几何参数构造
GeomCollection值。GeometryCollection()返回所有包含在参数中的适当几何体，即使存在不受支持的几何体也是如此。GeometryCollection()允许不带参数作为创建空几何体的一种方式。此外，接受 WKT 几何集参数的函数，如ST_GeomFromText()，理解 OpenGIS 的'GEOMETRYCOLLECTION EMPTY'标准语法和 MySQL 的'GEOMETRYCOLLECTION()'非标准语法。GeomCollection()和GeometryCollection()是同义词，首选函数为GeomCollection()。 -
从一些
Point或 WKBPoint参数构建一个LineString值。如果参数数量少于两个，返回值为NULL。 -
MultiLineString(*ls* [, *ls*] ...)使用
LineString或 WKBLineString参数构建一个MultiLineString值。 -
MultiPoint(*pt* [, *pt2*] ...)使用
Point或 WKBPoint参数构建一个MultiPoint值。 -
MultiPolygon(*poly* [, *poly*] ...)从一组
Polygon或 WKBPolygon参数构建一个MultiPolygon值。 -
Point(*x*, *y*)使用其坐标构建一个
Point。 -
从一些
LineString或 WKBLineString参数构建一个Polygon值。如果任何参数不代表一个LinearRing（即不是一个封闭且简单的LineString），返回值为NULL。
14.16.6 几何格式转换函数
原文：
dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html
MySQL 支持本节列出的函数，用于将几何值从内部几何格式转换为 WKT 或 WKB 格式，或交换 X 和 Y 坐标的顺序。
还有函数用于将字符串从 WKT 或 WKB 格式转换为内部几何格式。参见第 14.16.3 节，“从 WKT 值创建几何值的函数”和第 14.16.4 节，“从 WKB 值创建几何值的函数”。
函数，比如ST_GeomFromText()，接受 WKT 几何集合参数，理解 OpenGIS 标准语法'GEOMETRYCOLLECTION EMPTY'和 MySQL 非标准语法'GEOMETRYCOLLECTION()'。另一种生成空几何集合的方法是调用不带参数的GeometryCollection()。生成 WKT 值的函数，比如ST_AsWKT()，生成'GEOMETRYCOLLECTION EMPTY'标准语法：
mysql> SET @s1 = ST_GeomFromText('GEOMETRYCOLLECTION()');
mysql> SET @s2 = ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');
mysql> SELECT ST_AsWKT(@s1), ST_AsWKT(@s2);
+--------------------------+--------------------------+
| ST_AsWKT(@s1) | ST_AsWKT(@s2) |
+--------------------------+--------------------------+
| GEOMETRYCOLLECTION EMPTY | GEOMETRYCOLLECTION EMPTY |
+--------------------------+--------------------------+
mysql> SELECT ST_AsWKT(GeomCollection());
+----------------------------+
| ST_AsWKT(GeomCollection()) |
+----------------------------+
| GEOMETRYCOLLECTION EMPTY |
+----------------------------+
除非另有说明，本节中的函数处理其几何参数如下：
-
如果任何参数为
NULL，返回值为NULL。 -
如果任何几何参数不是语法上良好形式的几何，则会发生
ER_GIS_INVALID_DATA错误。 -
如果任何几何参数处于未定义的空间参考系统中，轴将按照几何中出现的顺序输出，并发生
ER_WARN_SRS_NOT_FOUND_AXIS_ORDER警告。 -
默认情况下，地理坐标（纬度、经度）按照几何参数的空间参考系统指定的顺序解释。可提供一个可选的
options参数来覆盖默认轴顺序。options由逗号分隔的*key*=*value*列表组成。唯一允许的key值是axis-order，允许的值为lat-long、long-lat和srid-defined（默认值）。如果
options参数为NULL，返回值为NULL。如果options参数无效，将出现错误指示原因。 -
否则，返回值为非
NULL。
这些函数可用于格式转换或坐标交换：
-
ST_AsBinary(*g* [, *options*])，ST_AsWKB(*g* [, *options*])将内部几何格式的值转换为其 WKB 表示形式，并返回二进制结果。
函数返回值的地理坐标（纬度、经度）顺序由适用于几何参数的空间参考系统指定。可提供一个可选的
options参数来覆盖默认轴顺序。ST_AsBinary()和ST_AsWKB()处理其参数的方式如本节介绍的那样。mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326); mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g))); +-----------------------------------------+ | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g))) | +-----------------------------------------+ | LINESTRING(5 0,10 5,15 10) | +-----------------------------------------+ mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat'))); +----------------------------------------------------------------+ | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat'))) | +----------------------------------------------------------------+ | LINESTRING(0 5,5 10,10 15) | +----------------------------------------------------------------+ mysql> SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long'))); +----------------------------------------------------------------+ | ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=lat-long'))) | +----------------------------------------------------------------+ | LINESTRING(5 0,10 5,15 10) | +----------------------------------------------------------------+ -
ST_AsText(*g* [, *options*]),ST_AsWKT(*g* [, *options*])将内部几何格式的值转换为其 WKT 表示形式，并返回字符串结果。
函数返回值的地理坐标（纬度、经度）顺序由适用于几何参数的空间参考系统指定。可提供一个可选的
options参数来覆盖默认轴顺序。ST_AsText()和ST_AsWKT()处理其参数的方式如本节介绍的那样。mysql> SET @g = 'LineString(1 1,2 2,3 3)'; mysql> SELECT ST_AsText(ST_GeomFromText(@g)); +--------------------------------+ | ST_AsText(ST_GeomFromText(@g)) | +--------------------------------+ | LINESTRING(1 1,2 2,3 3) | +--------------------------------+MultiPoint值的输出包括每个点周围的括号。例如：mysql> SELECT ST_AsText(ST_GeomFromText(@mp)); +---------------------------------+ | ST_AsText(ST_GeomFromText(@mp)) | +---------------------------------+ | MULTIPOINT((1 1),(2 2),(3 3)) | +---------------------------------+ -
ST_SwapXY(*g*)接受内部几何格式的参数，交换几何内每个坐标对的 X 和 Y 值，并返回结果。
ST_SwapXY()处理其参数的方式如本节介绍的那样。mysql> SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)'); mysql> SELECT ST_AsText(@g); +----------------------------+ | ST_AsText(@g) | +----------------------------+ | LINESTRING(0 5,5 10,10 15) | +----------------------------+ mysql> SELECT ST_AsText(ST_SwapXY(@g)); +----------------------------+ | ST_AsText(ST_SwapXY(@g)) | +----------------------------+ | LINESTRING(5 0,10 5,15 10) | +----------------------------+
14.16.7 几何属性函数
原文：
dev.mysql.com/doc/refman/8.0/en/gis-property-functions.html
14.16.7.1 通用几何属性函数
14.16.7.2 点属性函数
14.16.7.3 线串和多线串属性函数
14.16.7.4 多边形和多多边形属性函数
14.16.7.5 几何集合属性函数
每个属于这个组的函数都以几何值作为其参数，并返回几何的一些定量或定性属性。一些函数限制其参数类型。如果参数的几何类型不正确，则这些函数返回NULL。例如，ST_Area() 多边形函数在对象类型既不是Polygon也不是MultiPolygon时返回NULL。
原文：
dev.mysql.com/doc/refman/8.0/en/gis-general-property-functions.html
14.16.7.1 通用几何属性函数
本节列出的函数不限制其参数，并接受任何类型的几何值。
除非另有说明，本节中的函数处理其几何参数如下：
-
如果任何参数为
NULL，则返回值为NULL。 -
如果任何几何参数不是语法上良好形式的几何图形，则会发生
ER_GIS_INVALID_DATA错误。 -
如果任何几何参数是在未定义的空间参考系统（SRS）中语法上良好形式的几何，则会发生
ER_SRS_NOT_FOUND错误。 -
如果任何 SRID 参数不在 32 位无符号整数的范围内，则会发生
ER_DATA_OUT_OF_RANGE错误。 -
如果任何 SRID 参数引用未定义的 SRS，则会发生
ER_SRS_NOT_FOUND错误。 -
否则，返回值为非
NULL。
这些函数可用于获取几何属性：
-
ST_Dimension(*g*)返回几何值g的固有维度。维度可以是−1、0、1 或 2。这些值的含义在第 13.4.2.2 节“几何类”中给出。
ST_Dimension()处理其参数如本节介绍的那样。mysql> SELECT ST_Dimension(ST_GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------------+ | ST_Dimension(ST_GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------------+ | 1 | +------------------------------------------------------+ -
ST_Envelope(*g*)返回几何值g的最小边界矩形（MBR）。结果以由边界框的角点定义的
Polygon值返回：POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))mysql> SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)'))); +----------------------------------------------------------------+ | ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)'))) | +----------------------------------------------------------------+ | POLYGON((1 1,2 1,2 2,1 2,1 1)) | +----------------------------------------------------------------+如果参数是点或垂直或水平线段，则
ST_Envelope()返回点或线段作为其 MBR，而不是返回无效多边形：mysql> SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,1 2)'))); +----------------------------------------------------------------+ | ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,1 2)'))) | +----------------------------------------------------------------+ | LINESTRING(1 1,1 2) | +----------------------------------------------------------------+ST_Envelope()处理其参数如本节介绍的那样，但有一个例外：- 如果几何图形具有地理空间参考系统（SRS）的 SRID 值，则会发生
ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS错误。
- 如果几何图形具有地理空间参考系统（SRS）的 SRID 值，则会发生
-
ST_GeometryType(*g*)返回一个二进制字符串，指示几何实例g是其成员的几何类型的名称。该名称对应于可实例化的
Geometry子类之一。ST_GeometryType()处理其参数如本节介绍的那样。mysql> SELECT ST_GeometryType(ST_GeomFromText('POINT(1 1)')); +------------------------------------------------+ | ST_GeometryType(ST_GeomFromText('POINT(1 1)')) | +------------------------------------------------+ | POINT | +------------------------------------------------+ -
ST_IsEmpty(*g*)此函数是一个占位符，对于空几何体集合值返回 1，否则返回 0。
唯一有效的空几何体以空几何体集合值的形式表示。MySQL 不支持 GIS
EMPTY值，如POINT EMPTY。ST_IsEmpty()处理其参数如本节介绍的那样。 -
ST_IsSimple(*g*)如果几何值
g根据 ISO SQL/MM Part 3: Spatial 标准是简单的，则返回 1。如果参数不简单，则ST_IsSimple()返回 0。在 Section 13.4.2, “The OpenGIS Geometry Model” 下给出的可实例化几何类的描述包括导致类实例被分类为非简单的特定条件。
ST_IsSimple()处理其参数如本节介绍的那样，除了：-
如果几何体具有地理 SRS，其经度或纬度超出范围，则会出现错误：
-
如果经度值不在范围内（−180, 180]，则会出现
ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LONGITUDE_OUT_OF_RANGE）。 -
如果纬度值不在范围内 [−90, 90]，则会出现
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误（在 MySQL 8.0.12 之前为ER_LATITUDE_OUT_OF_RANGE）。
显示的范围以度为单位。由于浮点运算，确切的范围限制略有偏差。
-
-
-
使用表示有效几何对象
g的单个参数，ST_SRID()返回一个整数，指示与g关联的空间参考系统（SRS）的 ID。带有可选的第二个参数表示有效的 SRID 值，
ST_SRID()返回一个与其第一个参数类型相同的对象，其 SRID 值等于第二个参数。这仅设置对象的 SRID 值；不执行任何坐标值的转换。ST_SRID()处理其参数的方式如本节介绍中所述，但有一个例外：- 对于单参数语法，
ST_SRID()即使引用未定义的 SRS 也会返回几何图形的 SRID。不会发生ER_SRS_NOT_FOUND错误。
ST_SRID(*g*, *target_srid*)和ST_Transform(*g*, *target_srid*)的区别如下：-
ST_SRID()改变几何图形的 SRID 值，而不改变其坐标。 -
ST_Transform()会在改变 SRID 值的同时转换几何图形的坐标。
mysql> SET @g = ST_GeomFromText('LineString(1 1,2 2)', 0); mysql> SELECT ST_SRID(@g); +-------------+ | ST_SRID(@g) | +-------------+ | 0 | +-------------+ mysql> SET @g = ST_SRID(@g, 4326); mysql> SELECT ST_SRID(@g); +-------------+ | ST_SRID(@g) | +-------------+ | 4326 | +-------------+通过将一个 MySQL 特定函数创建空间值的结果和一个 SRID 值传递给
ST_SRID()，可以在特定的 SRID 中创建几何图形。例如：SET @g1 = ST_SRID(Point(1, 1), 4326);然而，该方法会在 SRID 0 中创建几何图形，然后将其转换为 SRID 4326（WGS 84）。更好的选择是一开始就使用正确的空间参考系统创建几何图形。例如：
SET @g1 = ST_PointFromText('POINT(1 1)', 4326); SET @g1 = ST_GeomFromText('POINT(1 1)', 4326);ST_SRID()的两个参数形式对于纠正或更改具有不正确 SRID 的几何图形非常有用。 - 对于单参数语法，
