布尔全文搜索（相关性排名排序）

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 will show ...   	|
|  4 	| 1001 MySQL Tricks     	| 1. Never run mysqld as root. 2. ... 	|
|  6 	| MySQL Security        	| When configured properly, MySQL ... 	|
+----	+-----------------------	+-------------------------------------	+

注意

在实现此功能时，MySQL使用了有时称为隐式布尔逻辑的方法，其中

• +代表AND
• -代表NOT
• [没有运算符]暗示OR

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

• 它们不会自动按照相关性递减的顺序对行进行排序。
• InnoDB表需要FULLTEXT在MATCH()表达式的所有列上都有索引才能执行布尔查询。MyISAM即使没有FULLTEXT索引，针对搜索索引的布尔查询也可以工作，尽管以这种方式执行的搜索会非常慢。

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

  最小和最大字长全文参数不适用于FULLTEXT使用ngram解析器创建的索引。ngram令牌大小由该ngram_token_size选项定义。

• 停止字适用，通过控制innodb_ft_enable_stopword，innodb_ft_server_stopword_table以及innodb_ft_user_stopword_table对InnoDB搜索索引，并ft_stopword_file为MyISAM那些。
• InnoDB全文搜索不支持在单个搜索词上使用多个运算符，例如本示例：'++apple'。在单个搜索词上使用多个运算符可将语法错误返回标准输出。MyISAM全文搜索将成功处理相同的搜索，而忽略除紧邻搜索词的运算符之外的所有运算符。
• InnoDB全文搜索仅支持加号或减号。例如，InnoDB支持'+apple'但不支持'apple+'。指定尾随的加号或减号会导致InnoDB报告语法错误。
• InnoDB全文搜索不支持使用带通配符的前导加号（'+*'），加号和减号组合（'+-'）或加号和减号组合的前导（'+-apple'）。这些无效的查询返回语法错误。
• InnoDB全文搜索不支持@在布尔全文搜索中使用该符号。该@符号保留供@distance邻近搜索运营商使用。
• 他们没有使用适用于MyISAM搜索索引的50％阈值。

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

• +

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

• -

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

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

• （无操作员）

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

• @distance

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

• ><

  这两个运算符用于更改单词对分配给行的相关性值的贡献。该>运营商的贡献增加以及<运营商降低它。请参阅此列表后面的示例。

• ()

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

• ~

  前导代字号充当否定运算符，使单词对行的相关性的贡献为负。这对于标记“噪音”字很有用。包含此类单词的行的评分低于其他单词，但不完全排除，与-操作员一样。

• *

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

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

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

• "

  包含在双引号（"）字符中的短语仅与按字面意义包含该短语的行匹配。全文引擎将短语分解为单词，并在FULLTEXT索引中搜索单词。非单词字符不必完全匹配：短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同。例如，"test phrase"matchs "test, phrase"。

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

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

• 'apple banana'

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

• '+apple +juice'

  查找包含两个单词的行。

• '+apple macintosh'

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

• '+apple -macintosh'

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

• '+apple ~macintosh'

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

• '+apple +(>turnover <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条与“ database ”搜索词匹配。第一个记录（id 6）包含搜索词6次，相关性排名为1.0886961221694946。使用TF值6（“ database ”搜索词在记录中出现6次id 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命令行客户端返回的排名值的细微差异。