12.9.1 自然语言全文搜索
默认情况下或使用IN NATURAL LANGUAGE MODE
修饰符,MATCH()函数针对文本集合对字符串进行自然语言搜索。集合是FULLTEXT
索引中包含的一组一个或多个列。搜索字符串作为AGAINST()
的参数给出。对于 table 中的每一行,MATCH()返回相关性值;也就是说,搜索字符串与MATCH()列 table 中命名的列中该行中的文本之间的相似性度量。
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 (0.08 sec)
mysql> INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
默认情况下,搜索以不区分大小写的方式执行。若要执行区分大小写的全文本搜索,请对索引列使用二进制排序规则。例如,可以将使用latin1
字符集的列分配为latin1_bin
的排序规则,以使其对全文搜索区分大小写。
如前面的示例所示,在WHERE
子句中使用MATCH()时,返回的行将自动按照相关性最高的 Sequences 进行排序。相关性值是非负浮点数。零相关性意味着没有相似性。相关性是根据行(文档)中单词的数量,行中唯一单词的数量,集合中单词的总数以及包含特定单词的行数量来计算的。
Note
术语“文档”可以与术语“行”互换使用,并且两个术语均指行的索引部分。术语“集合”是指索引的列,并且包含所有行。
要简单地计算匹配数,您可以使用如下查询:
mysql> SELECT COUNT(*) FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
您可能会发现按以下方式更快地重写查询:
mysql> SELECT
COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
AS count
FROM articles;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.03 sec)
第一个查询会做一些额外的工作(按相关性对结果进行排序),但也可以使用基于WHERE
子句的索引查找。如果搜索匹配几行,则索引查找可能会使第一个查询更快。第二个查询执行全 table 扫描,如果大多数行中都存在搜索词,它可能比索引查找要快。
对于自然语言全文搜索,MATCH()函数中命名的列必须与 table 中某些FULLTEXT
索引中包含的列相同。对于前面的查询,请注意MATCH()函数(title
和body
)中命名的列与article
table 的FULLTEXT
索引的定义中命名的列相同。要分别搜索title
或body
,您将为每列创建单独的FULLTEXT
索引。
您还可以执行布尔搜索或带有查询扩展的搜索。这些搜索类型在第 12.9.2 节“Boolean 全文本搜索”和第 12.9.3 节“具有查询扩展的全文本搜索”中描述。
使用索引的全文搜索只能在MATCH()子句中的单个 table 中命名列,因为索引不能跨越多个 table。对于MyISAM
table,可以在没有索引的情况下进行布尔搜索(尽管速度较慢),在这种情况下,可以从多个 table 中命名列。
前面的示例是一个基本说明,显示了如何使用MATCH()函数,其中按相关性递减的 Sequences 返回行。下一个示例显示如何显式检索相关性值。返回的行没有排序,因为SELECT语句既不包含WHERE
也不包含ORDER BY
子句:
mysql> SELECT id, MATCH (title,body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
FROM articles;
+----+---------------------+
| id | score |
+----+---------------------+
| 1 | 0.22764469683170319 |
| 2 | 0 |
| 3 | 0.22764469683170319 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+---------------------+
6 rows in set (0.00 sec)
以下示例更为复杂。查询返回相关性值,并且还按相关性递减的 Sequences 对行进行排序。要获得此结果,请两次指定MATCH():一次在SELECT列 table 中,一次在WHERE
子句中。这不会造成额外的开销,因为 MySQL 优化器注意到两个MATCH()调用是相同的,并且只调用一次全文搜索代码。
mysql> SELECT id, body, MATCH (title,body) AGAINST
('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE) AS score
FROM articles WHERE MATCH (title,body) AGAINST
('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
| 6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)
包含在双引号("
)字符中的短语仅匹配包含单词* literally(按其键入*)的行。全文引擎将短语分解为单词,并在FULLTEXT
索引中搜索单词。非单词字符不必完全匹配:短语搜索仅要求匹配项包含与短语完全相同的单词,并且 Sequences 相同。例如,"test phrase"
匹配"test, phrase"
。如果该短语不包含索引中的单词,则结果为空。例如,如果所有单词都是停用词或比索引单词的最小长度短,则结果为空。
MySQL FULLTEXT
实现将任何真单词字符(字母,数字和下划线)序列视为一个单词。该序列也可以包含撇号('
),但连续不超过一个。这意味着aaa'bbb
被视为一个单词,但aaa''bbb
被视为两个单词。 FULLTEXT
解析器去除单词开头或结尾的撇号; 'aaa'bbb'
将被解析为aaa'bbb
。
内置的FULLTEXT
解析器通过查找某些定界符来确定单词的开头和结尾。例如``(空格),,
(逗号)和.
(句点)。如果单词之间没有用定界符分隔(例如,中文),则内置FULLTEXT
解析器无法确定单词的开始或结束位置。为了能够将此类语言中的单词或其他索引术语添加到使用内置FULLTEXT
解析器的FULLTEXT
索引中,您必须对其进行预处理,以使它们由某个任意定界符分隔。或者,您可以使用 ngram 解析器插件(对于中文,日语或韩语)或 MeCab 解析器插件(对于日语)来创建FULLTEXT
索引。
可以编写一个替换内置全文分析器的插件。有关详细信息,请参见第 28.2 节“ MySQL 插件 API”。有关解析器插件源代码的示例,请参见 MySQL 源发行版的plugin/fulltext
目录。
在全文搜索中,某些单词会被忽略:
- 任何太短的单词都会被忽略。全文搜索找到的默认最小单词长度是
InnoDB
搜索索引的三个字符,或MyISAM
的四个字符。您可以通过在创建索引之前设置配置选项来控制截止:InnoDB
搜索索引的innodb_ft_min_token_size配置选项,或MyISAM
的ft_min_word_len。
Note
此行为不适用于使用 ngram 解析器的FULLTEXT
索引。对于 ngram 解析器,令牌长度由ngram_token_size选项定义。
- 停用词列 table 中的单词将被忽略。停用词是一个非常普遍的词,例如“ the”或“ some”,以至于它被认为具有零语义值。有一个内置的停用词列 table,但是可以被用户定义的列 table 覆盖。
InnoDB
搜索索引和MyISAM
索引的停用词列 table 和相关配置选项不同。停用词处理由InnoDB
搜索索引的配置选项innodb_ft_enable_stopword,innodb_ft_server_stopword_table和innodb_ft_user_stopword_table和MyISAM
的ft_stopword_file控制。
请参阅第 12.9.4 节“全文停用词”以查看默认停用词列 table 以及如何更改它们。默认的最小字长可以按照第 12.9.6 节“微调 MySQL 全文搜索”中所述进行更改。
集合中和查询中的每个正确单词都会根据其在集合或查询中的重要性进行加权。因此,存在于许多文档中的单词具有较低的权重,因为它在此特定集合中具有较低的语义值。相反,如果单词很少见,则其权重较高。单词的权重被组合以计算行的相关性。这项技术最适合大型收藏。
MyISAM Limitation
对于很小的 table,单词分布不能充分反映其语义值,并且此模型有时可能会对MyISAM
table 上的搜索索引产生奇怪的结果。例如,尽管在前面显示的articles
table 的每一行中都有单词“ MySQL”,但是在MyISAM
搜索索引中搜索该单词不会产生任何结果:
mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)
搜索结果为空,因为单词“ MySQL”出现在至少 50%的行中,因此实际上被视为停用词。这种过滤技术更适用于大型数据集,在小型数据集中,您可能不希望结果集从 1GBtable 返回第二行,而在小型数据集中,它可能导致流行术语的结果不佳。
当您首次尝试全文搜索以查看其工作原理时,50%的阈值可能会让您感到惊讶,并使InnoDB
table 更适合尝试全文搜索。如果创建MyISAM
table 并仅在其中插入一两行文本,则文本中的每个单词至少出现在 50%的行中。结果,在 table 包含更多行之前,没有搜索返回任何结果。需要绕过 50%限制的用户可以在InnoDB
table 上构建搜索索引,或使用第 12.9.2 节“Boolean 全文本搜索”中说明的布尔搜索模式。