Documentation Home
MySQL 8.3 Reference Manual
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows


MySQL 8.3 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 令牌大小由 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 表。它测试两个或多个单词是否在指定的距离内相互靠近，测量单位为单词。例如，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” 的行。

  • '+苹果 ~麦金托什'

    查找包含单词 “苹果” 的行，但如果行也包含单词 “麦金托什”，则将其评级降低。如果行不包含 “麦金托什”，则评级较高。这比搜索 '+苹果 -麦金托什' 软一些，因为在后一种情况下，存在 “麦金托什” 将导致行不被返回。

  • '+苹果 +(>翻转 <施特鲁德尔)'

    查找包含单词 “苹果” 和 “翻转”，或 “苹果” 和 “施特鲁德尔”（任意顺序），但将 “苹果翻转” 排名高于 “苹果施特鲁德尔”。

  • '苹果*'

    查找包含单词 “苹果”、“苹果子”、“苹果酱” 或 “苹果小程序” 等单词。

  • '某些单词'

    查找包含确切短语 “某些单词” 的行（例如，包含 “某些智慧单词” 但不包含 “某些噪音单词”）。注意，" 字符是操作符字符，用于分隔短语，而不是搜索字符串本身的引号。

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 值输入排名公式：

${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 vs 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)
Note

前一个示例中解释了 SELECT ... MATCH ... AGAINST 语句和 MySQL 命令行客户端返回的排名值之间的轻微差异。


PREV   HOME   UP   NEXT
Related Documentation
MySQL 8.3 Release Notes
Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual
MySQL Backup and Recovery
MySQL NDB Cluster 8.3
MySQL Globalization
MySQL Information Schema
MySQL Installation Guide
MySQL and Linux/Unix
MySQL and macOS
MySQL Partitioning
MySQL Performance Schema
MySQL Replication
Using the MySQL Yum Repository
MySQL Restrictions and Limitations
Security in MySQL
MySQL and Solaris
Building MySQL from Source
Starting and Stopping MySQL
MySQL Tutorial
MySQL and Windows