前言
最近在学习和了解 MySQL 8 自带的全文搜索功能。自己在看资料的时候顺便翻译了一下，希望对大家有用。（章节序号可能有所差异，因为官方文档会不定期更新）
官方原文链接如下：《Boolean Full-Text Searches》
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 索引才能执行布尔查询。即使没有 FULLTEXT 索引，针对 MyISAM 搜索索引的布尔查询也可以工作，尽管以这种方式执行的搜索会很慢。
最小和最大字长全文参数适用于使用内置的 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邻近搜索运算符使用。
InnoDB 全文搜索不使用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”，则将其评分低于 row 不包含单词的评分。这比搜索“+apple -macintosh”更“柔和”，因为“macintosh”的存在会导致根本不返回该行。'+apple +(>turnover <strudel)'
查找包含单词“apple”和“turnover”或“apple”和“strudel”（按任意顺序）但将“apple turnover”排在“apple strudel”之上的行。'apple*'
查找包含“apple”、“apples”、“applesauce”或“applet”等字词的行。'"some 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: 0mysql> 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 条与“数据库”搜索词匹配。第一条记录 （id 6） 包含搜索词 6 次，相关性排名为 1.0886961221694946。此排名值是使用 TF 值 6（“数据库”搜索词在记录 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命令行客户端或其他类型的计算器中。
多词搜索的相关性排名
此示例演示了基于上一示例中使用的文章表和数据的多字全文搜索的相关性排名计算。
如果搜索多个单词，则相关性排名值是每个单词的相关性排名值的总和，如下公式所示：
${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}对两个术语执行搜索（“mysql tutorial”）将返回以下结果：
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)
Note
关于MySQL命令行和SELECT ... MATCH ... AGAINST语法返回的值的差异，已经在前文说明。