12.9.2 Boolean 全文本搜索

MySQL 可以使用IN BOOLEAN MODE修饰符执行 Boolean 全文本搜索。使用此修饰符，某些字符在搜索字符串中单词的开头或结尾具有特殊含义。在下面的查询中，+和-运算符指示必须存在或不存在一个词，匹配才会发生。因此，查询将检索包含单词“ 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 will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

布尔全文搜索具有以下 Feature：

• 它们不会按照相关性递减的 Sequences 自动对行进行排序。

• InnoDBtable 在MATCH()table 达式的所有列上都需要一个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选项定义。

• 停用词列 table 适用，由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％阈值。

Boolean 全文本搜索功能支持以下运算符：

• +

前导或尾随加号 table 示在返回的每一行中必须*显示该词。 InnoDB仅支持前导加号。

• -

前导或尾随减号 table 示该单词在返回的任何行中都不得存在。 InnoDB仅支持减号。

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

• (no operator)

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

• @distance

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

• > <

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

• ( )

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

• ~

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

• *

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

如果使用截断运算符指定了单词，则即使布尔值太短或停用词，也不会从布尔查询中删除该单词。单词是否太短取决于InnoDBtable 的innodb_ft_min_token_size设置或MyISAMtable 的ft_min_word_len。这些选项不适用于使用 ngram 解析器的FULLTEXT索引。

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

• "

包含在双引号(")字符中的短语仅匹配包含单词* literally(按其键入*)的行。全文引擎将短语分解为单词，并在FULLTEXT索引中搜索单词。非单词字符不必完全匹配：短语搜索仅要求匹配项包含与短语完全相同的单词，并且 Sequences 相同。例如，"test phrase"匹配"test, phrase"。

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

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

• 'apple banana'

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

• '+apple +juice'

查找包含两个单词的行。

• '+apple macintosh'

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

• '+apple -macintosh'

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

• '+apple ~macintosh'

查找包含单词“ apple”的行，但是如果该行还包含单词“ macintosh”，则将其评分低于不包含单词“ macintosh”的行。这比对'+apple -macintosh'的搜索“软”，对于'+apple -macintosh'的搜索，“ macintosh”的存在将导致根本不返回该行。

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

查找包含单词“ apple”和“ turnover”，或“ apple”和“ strudel”(以任何 Sequences)，但将“ apple 营业额”排名高于“ 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: 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 条与“数据库”搜索词匹配。第一记录(id 6)包含 6 次搜索词，并且相关性排名为1.0886961221694946。使用TF值 6(“数据库”搜索词在记录id 6中出现 6 次)和IDF值 0.42596873216370745 来计算此排名值，其计算方法如下(其中 8 是记录总数，3 是记录总数.搜索词出现在其中的记录数)：

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

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

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

在 MySQL 命令行 Client 端中执行计算返回的排名值为 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)

多词搜索的相关性排名

本示例演示了基于articlestable 和上一示例中使用的数据的多字全文搜索的相关性排名计算。

如果搜索多个单词，则相关性排名值是每个单词的相关性排名值的总和，如以下公式所示：

${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 命令行 Client 端将返回预期的排名值：

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)