文档首页
MySQL 8.4 参考手册
相关文档
MySQL 8.4 发行说明
下载本手册
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
Man Pages (TGZ) - 258.5Kb
Man Pages (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
MySQL 8.4 参考手册  /  ...  /  布尔全文搜索

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 ... |
+----+-----------------------+-------------------------------------+
注意

在实现此功能时，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” 的行。

  • '+apple ~macintosh'

    查找包含词 “apple” 的行，但如果该行也包含词 “macintosh”，则对其排名低于不包含该词的行。这比搜索 '+apple -macintosh' 更 “柔和”，因为 “macintosh” 的存在会导致该行根本不会返回。

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

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

  • 'apple*'

    查找包含以下词的行：“apple”、“apples”、“applesauce” 或 “applet”。

  • '"some words"'

    查找包含精确短语 “some words” 的行（例如，包含 “some words of wisdom” 但不包含 “some noise 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” 搜索词在记录 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 命令行客户端或其他类型的计算器）中执行的方式造成的。

多词搜索的相关性排名

此示例演示了基于上一个示例中使用的 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' 有 6 条匹配记录，'tutorial' 有 2 条匹配记录。当将这些值插入多词搜索的排名公式时，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 命令行客户端返回的排名值略有不同的原因。


PREV   HOME   UP   NEXT
相关文档
MySQL 8.4 发行说明
下载本手册
PDF (US Ltr) - 39.9Mb
PDF (A4) - 40.0Mb
Man Pages (TGZ) - 258.5Kb
Man Pages (Zip) - 365.5Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb