【MySQL精通之路】全文搜索-布尔型全文搜索

1.使用方法

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

2.特征

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

• 它们不会自动按相关性递减的顺序对行进行排序。
• InnoDB表需要在MATCH（）表达式的所有列上使用FULLTEXT索引来执行布尔查询。即使没有FULLTEXT索引，针对MyISAM搜索索引的布尔查询也可以工作，尽管以这种方式执行搜索会非常慢。
• 最小和最大字长全文参数适用于使用内置FULLTEXT解析器和MeCab解析器插件创建的FULLTEXT索引。innob_ft_min_token_size和innob_ft_max_token_ize用于innodb搜索索引。ft_min_word_len和ft_max_word_len用于MyISAM搜索索引。
• 最小和最大字长全文参数不适用于使用ngram分析器创建的FULLTEXT索引。ngram令牌大小由ngram_token_size选项定义。
• 停止字列表适用于innodb搜索索引，由innodb_ft_enable_stopword、innodb_vt_server_stopword_table和innodb_pt_user_stopword_table控制，而MyISAM搜索索引由ft_stopword_file控制。
• InnoDB全文搜索不支持在单个搜索词上使用多个运算符，如本例所示：“++apple”。在单个搜索词上使用多个运算符会将语法错误返回到标准输出。MyISAM全文搜索成功地处理了相同的搜索，忽略了除紧邻搜索词的运算符之外的所有运算符。
• InnoDB全文搜索仅支持前导加号或减号。例如，InnoDB支持“+apple”，但不支持“apple+”。指定尾随加号或减号会导致InnoDB报告语法错误。
• InnoDB全文搜索不支持使用带通配符的前导加号（“+*”）、加号和减号组合（“+-”）或前导加号和减码组合（“+/-apple”）。这些无效查询返回语法错误。
• InnoDB全文搜索不支持在布尔全文搜索中使用@符号。@符号是为@distance proximity搜索运算符保留的。
• 它们不使用适用于MyISAM搜索索引的50%阈值。

3.支持的运算符

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

+

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

-

前导或尾随减号表示该单词不得出现在返回的任何行中。InnoDB只支持前导减号。

注意：-运算符仅用于排除其他搜索项匹配的行。因此，仅包含以-开头的项的布尔模式搜索返回一个空结果。它不会返回“除包含任何排除项的行之外的所有行”

(no operator)

默认情况下（既没有指定+也没有指定-），该单词是可选的，但包含该单词的行的级别更高。这模拟了不使用IN BOOLEAN MODE修饰符的MATCH（）AGAINST（）的行为。

@distance

此运算符仅适用于InnoDB表。它测试两个或多个单词是否都以单词为单位在指定的距离内开始。在@distance运算符前的双引号字符串中指定搜索词，例如，MATCH（col1）ACTION（'“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”返回更少的行，因为第二个查询会忽略太短的搜索词。

"

包含在双引号（“）字符中的短语只与键入时包含短语的行匹配。全文引擎将短语拆分为多个单词，并在FULLTEXT索引中搜索这些单词。非单词字符不必完全匹配：短语搜索只要求匹配项包含与短语完全相同的单词，且顺序相同。例如，“测试短语”与“测试，短语”匹配。

如果短语中不包含索引中的单词，则结果为空。这些单词可能不在索引中，这是由于多种因素造成的：如果它们不存在于文本中，是停止语，或者短于索引单词的最小长度。

4.演示

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

'apple banana'

查找至少包含这两个单词之一的行。

'+apple +juice'

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

'+apple macintosh'

查找包含单词“apple”的行，但如果行中也包含“macintosh”，则将行排名靠前。

'+apple -macintosh'

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

'+apple ~macintosh'

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

'+apple +(>turnover <strudel)'

查找包含单词“apple”和“overflow”，或“apple”和“strudel”（按任何顺序）的行，但“apple overflow”的排名高于“apple strudel”。

'apple*'

查找包含单词（如“apple”、“apples”、“苹果酱”或“applet”）的行。

'"some words"'

查找包含确切短语“一些单词”的行（例如，包含“一些智慧单词”但不包含“一些噪音单词”的行将）。请注意，包含短语的“字符”是分隔短语的运算符字符。它们不是包含搜索字符串本身的引号。

5.相关性排序

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

InnoDB全文搜索以Sphinx全文搜索引擎为模型，使用的算法基于BM25和TF-IDF排名算法。

由于这些原因，InnoDB布尔全文搜索的相关性排名可能与MyISAM相关性排名不同。

InnoDB使用“term频率逆文档频率”（TF-IDF）加权系统的变体来对给定全文搜索查询的文档相关性进行排名。

TF-IDF权重基于单词在文档中出现的频率，并由该单词在集合中所有文档中的出现频率偏移。

换句话说，一个单词在文档中出现的频率越高，而该单词在文档集合中出现的次数越少，则该文档的排名就越高。

5.2 相关性排名的计算方法

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)

term频率（TF）值是单词在文档中出现的次数。

单词的逆文档频率（IDF）值使用以下公式计算

total_records是集合中的记录数，

matching_records是搜索词出现的记录数。

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

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

$｛TF｝*$｛IDF｝

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

$｛rank｝=$｛TF｝*$｛IDF｝*$｛IDV｝

以下示例演示了该公式。

5.3 单个单词搜索的相关性排名

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

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命令行客户端或其他类型的计算器中。

5.4 多词搜索的相关性排序

此示例演示了基于上一示例中使用的文章表和数据的多词全文搜索的相关性排名计算。
如果搜索多个单词，则相关性排名值是每个单词的相关性排名值的总和，如下公式所示：

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

对两个term执行搜索（'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)