MySQL的函数和运算符 - 全文搜索 - 布尔全文搜索

数据库杂货铺 2021-06-30

464

布尔全文搜索

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 索引来执行布尔查询。即使没有 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 选项定义。

● 应用于 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% 阈值。

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

● +

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

● -

前导或尾随的减号表示该词不能出现在返回的任何行中。InnoDB 只支持前导减号。

● (没有操作符)

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

● @distance

这个操作符只适用于 InnoDB 表。它测试两个或多个单词是否都在彼此指定的距离内，以单词为单位。指定紧挨着 @distance 操作符的双引号字符串内的搜索词，例如 MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE) 。

● > <

这两个操作符用于更改单词对分配给一行的相关性值的贡献。> 操作符增加了贡献，而 < 操作符减少了贡献。请参阅此列表后面的示例。

● ( )

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

● ~

前导波浪号充当一个否定运算符，使单词对行相关性的贡献为负。这对于标记“噪声”单词很有用。包含这样一个单词的行被评价为比其他行更低，但并没有完全排除，例如像使用 - 操作符那样。

● ＊

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

如果用截断操作符指定了一个单词，则不会从布尔查询中删除它，即使它太短或是个终止词。一个单词是否太短，取决于 InnoDB 表的 innodb_ft_min_token_size 设置，或者 MyISAM 表的 ft_min_word_len 设置。这些选项不适用于使用 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” 更“温和”，因为在 '+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（term frequency-inverse document frequency）加权系统的变体形式来对给定的全文搜索查询排序文档的相关性。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 vs 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 命令行客户端略有不同，前面的示例中进行了解释。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 全文搜索 - 布尔全文搜索

评论