Documentation Home
MySQL 8.4 Reference Manual
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  /  ...  /  Boolean Full-Text Searches

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 ... |
+----+-----------------------+-------------------------------------+
Note

在实现这个特性时，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 token size由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 全文搜索只支持前导加号或减号。例如，'+apple' 是支持的，但不支持 'apple+'。指定尾部加号或减号导致 InnoDB 报告语法错误。

  • InnoDB 全文搜索不支持使用前导加号与通配符 ('+*')，加号和减号组合 ('+-'），或前导加号和减号组合 ('+-apple')。这些无效查询返回语法错误。

  • InnoDB 全文搜索不支持在布尔全文搜索中使用 @ 符号。@ 符号保留给 @distance proximity 搜索操作符。

  • 它们不使用适用于 MyISAM 搜索索引的50%阈值。

布尔全文搜索能力支持以下操作符：

  • +

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

  • -

    leading 或 trailing 的减号表示该词语不能出现在返回的任何行中。InnoDB 只支持 leading 减号。

    注意：- 运算符只用于排除其他搜索条件匹配的行。因此，包含仅由 - 前缀的 boolean 模式搜索将返回空结果，不会返回“除了包含被排除词语的所有行”。

  • (无操作符)

    默认情况下（既不指定 + 也没有指定 -），该词语是可选的，但包含它的行被评分更高。这类似于MATCH() AGAINST() 没有 IN BOOLEAN MODE 修饰符时的行为。

  • @distance

    这个操作符只在 InnoDB 表中工作。它测试两个或多个词语是否都在指定距离内开始， measured in words。将搜索词语置于双引号字符串中，紧接着@distance 运算符，例如 MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)

  • > <

    这两个操作符用于改变词语对行的相关值。> 运算符增加相关值，< 运算符减少相关值。请看下面的示例。

  • ( )

    括号将表达式组合成子表达式。嵌套的括号组可以被使用。

  • ~

    前导波浪号作为否定操作符，导致该词语对行的相关性为负值。这对于标记“噪音”词语有用。包含这种词语的行被评估低于其他行，但不被完全排除，因为它将被使用-操作符排除。

  • *

    星号作为截断（或通配）操作符。与其他操作符不同，它附加到要影响的词语中。词语匹配如果以前导星号操作符开始。

    如果指定了截断操作符，词语即使太短或是停用词，也不会从布尔查询中删除。是否太短由innodb_ft_min_token_size设置决定，对于InnoDB表，或者ft_min_word_len对MyISAM表。这些选项不适用于使用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'

    找到包含单词“苹果”的行，但如果该行也包含单词“苹果电脑”“苹果-苹果电脑”不同，因为存在“苹果电脑”的行将不会被返回。

  • '+苹果+(turnover<strudel)'

    找到包含单词“苹果”和“翻转”，或“苹果”和“STRUDEL”(顺序不限），并将“苹果翻转”评分高于“苹果STRUDEL”.

  • '苹果*'

    找到包含单词如“苹果”、“苹果们”、“苹果酱”或“苹果树”的行。

  • '一些词语'

    找到包含exact短语“一些词语”(例如，包含“一些智慧词语”但不包含“一些噪音词语”).注意，"字符将短语括起来，是操作符字符，不是搜索字符串本身的引号。

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计算的，该IDF值是根据总共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)
Note

您可能会注意到SELECT ... MATCH ... AGAINST语句和MySQL命令行客户端(1.0886961221694946 versus 1.088696164686938)返回的排名值之间有一些差异。这是因为InnoDB内部对整数和浮点/双精度类型的转换方式，以及相关的精度和四舍五入决策，而在其他地方，例如MySQL命令行客户端或其他计算器中进行的转换方式不同。

这个示例演示了基于articles表和前一个示例中的数据进行多词全文搜索排名计算。

如果您搜索多个单词，排名值是每个单词的排名值之和，如下公式所示：

${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

前一个示例中SELECT ... MATCH ... AGAINST语句和MySQL命令行客户端返回的排名值差异解释见上文。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb