MySQL 函数和运算符：全文搜索功能

关于

“MATCH (col1,col2,...) AGAINST (expr [search_modifier])”：

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

MySQL 支持全文索引和搜索：

MySQL 中的全文本索引是 FULLTEXT 类型的索引。
全文索引只能与 InnoDB 或 MyISAM 表一起使用，并且只能为“CHAR”，“VARCHAR”或“TEXT”（都是非二进制字符）列创建。
MySQL 提供了一个内置的全文 ngram 解析器，该解析器支持中文，日文和韩文（CJK），以及一个用于日语的可安装的 MeCab 全文解析器插件。【？】
创建表时，可以在“CREATE TABLE”语句中给出 FULLTEXT 索引定义，或者稍后使用“ALTER TABLE”或“CREATE INDEX”添加。
对于大型数据集，将数据加载到不具有 FULLTEXT 索引的表中，然后再创建索引比将数据加载到具有 FULLTEXT 索引的表中要快得多。

使用“MATCH() ... AGAINST”语法执行全文搜索：

“MATCH()”：取一个用逗号分隔的列表，该列表为要搜索的列名。
“AGAINST”：包含要搜索的字符串和可选的修饰符，该修饰符指示要执行的搜索类型。
- 搜索字符串必须是在查询评估期间恒定的字符串值。

全文搜索分为三种类型：

自然语言搜索：将搜索字符串解释为自然人类语言中的短语（自由文本中的短语）。除双引号（“）字符外，没有特殊的运算符。适用停用词列 table 。
- 如果指定了“IN NATURAL LANGUAGE MODE”修饰符或未给出修饰符，则全文搜索是自然语言搜索。
布尔搜索：使用特殊查询语言的规则解释搜索字符串。该字符串包含要搜索的单词。它还可以包含指定要求的运算符，例如，匹配行中必须存在或不存在一个单词，或者其权重应高于或低于平常。某些常见单词（停用词）将从搜索索引中省略，并且如果出现在搜索字符串中则不匹配。
- “IN BOOLEAN MODE”修饰符指定布尔搜索。
查询扩展搜索：是对自然语言搜索的修改：搜索字符串用于执行自然语言搜索，然后，将搜索返回的最相关行中的单词添加到搜索字符串中，然后再次执行搜索。该查询返回第二个搜索中的行。【！！！】
- “IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION”或“WITH QUERY EXPANSION”修饰符指定查询扩展搜索。

myisam_ftdump 工具转储MyISAM全文索引的内容。这对于调试全文查询可能会有所帮助。【？】

自然语言全文搜索

自然语言搜索：默认，或使用“IN NATURAL LANGUAGE MODE”修饰符：

“MATCH()”函数针对文本集合对字符串进行搜索。
集合是 FULLTEXT 索引中包含的一组一个或多个列。
搜索字符串作为“AGAINST()”的参数给出。
对于表中的每一行，“MATCH()”返回相关性值；
也就是说，搜索字符串与“MATCH()”列表中命名的列中该行中的文本之间的相似性度量。

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()”时，返回的行将自动按照相关性最高的顺序进行排序。【！！！而无需使用“ORDER BY”语句指定】

相关性是根据：

“行（文档）中单词的数量”，
“行中唯一单词的数量”，
“集合中单词的总数”，
以及“包含特定单词的行数量”

来计算的。【“文档”、“行”均指行的索引部分。“集合”是指索引的列，并且包含所有行。】

相关性值是非负浮点数。零相关性意味着没有相似性。

示例1：
要简单地计算匹配数，您可以使用如下查询：

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”子句的索引查找。

如果搜索匹配几行，则索引查找可能会使第一个查询更快。

第二个查询执行全表扫描。

如果大多数行中都存在搜索词，第二个查询可能比索引查找要快。

对于自然语言全文搜索，“MATCH()”函数中命名的列必须与表中某些 FULLTEXT 索引中包含的列相同。对于前面的查询，请注意“MATCH()”函数（title 和 body）中命名的列与 articlet 表的 FULLTEXT 索引的定义中命名的列相同。要分别搜索 title 或 body，您将为每列创建单独的 FULLTEXT 索引。
使用索引的全文搜索只能在“MATCH()”子句中命名单个表中的列，因为 FULLTEXT 索引不能跨越多个表。对于 MyISAM 表，可以在没有索引的情况下进行布尔搜索（尽管速度较慢），在这种情况下，可以从多个表中命名列。

示例2：
前面的示例是一个基本说明，显示了如何使用“MATCH()”函数，其中按相关性递减的顺序返回行。下一个示例显示如何显式检索相关性值。返回的行没有排序，因为“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)

示例3：
以下示例更为复杂。查询返回相关性值，并且还按相关性递减顺序对行进行排序。

要获得此结果，请两次指定“MATCH()”：一次在“SELECT”列表中，一次在“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 索引中搜索单词。

非单词字符不必完全匹配：

短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同。例如，"test phrase"匹配"test, phrase"。
如果该短语不包含索引中的单词，则结果为空。例如，如果所有单词都是停用词或比索引单词的最小长度短，则结果为空。

FULLTEXT 字符解析：

MySQL FULLTEXT 实现将任何真单词字符（字母，数字和下划线）序列视为一个单词。该序列也可以包含撇号（'），但连续不超过一个。
这意味着 aaa'bbb 被视为一个单词，但 aaa''bbb 被视为两个单词。
FULLTEXT 解析器去除单词开头或结尾的撇号；
'aaa'bbb' 将被解析为 aaa'bbb。

内置的 FULLTEXT 解析器通过查找某些定界符来确定单词的开头和结尾。例如：``（空格），,（逗号）和.（句点）。

如果单词之间没有用定界符分隔（例如，中文），则内置 FULLTEXT 解析器无法确定单词的开始或结束位置。

为了能够将此类语言中的单词或其他索引术语添加到使用内置 FULLTEXT 解析器的 FULLTEXT 索引中，您必须对其进行预处理，以使它们由某个任意定界符分隔。或者，您可以使用 ngram 解析器插件（对于中文，日语或韩语）或 MeCab 解析器插件（对于日语）来创建 FULLTEXT 索引。

在全文搜索中，某些单词会被忽略：

任何太短的单词都会被忽略。
全文搜索找到的默认最小单词长度是 InnoDB 搜索索引的三个字符，或 MyISAM 的四个字符。您可以通过在创建索引之前设置配置选项来控制截止：InnoDB 搜索索引的“innodb_ft_min_token_size”配置选项，或 MyISAM 的“ft_min_word_len”。
- 此行为不适用于使用 ngram 解析器的FULLTEXT索引。对于 ngram 解析器，单词长度由“ngram_token_size”选项定义。
停用词列表中的单词将被忽略。
停用词是一个非常普遍的词，例如“ the”或“ some”，以至于它被认为具有零语义值。
- 有一个内置的停用词列表，但是可以被用户定义的列表覆盖。
- InnoDB 搜索索引和 MyISAM 索引的停用词列表和相关配置选项不同。停用词处理由 InnoDB 搜索索引的配置选项“innodb_ft_enable_stopword”，“innodb_ft_server_stopword_table”和“innodb_ft_user_stopword_table”和 MyISAM 的“ft_stopword_file”控制。

集合中和查询中的每个正确单词都会根据其在集合或查询中的重要性进行加权。因此，存在于许多文档中的单词具有较低的权重，因为它在此特定集合中具有较低的语义值。相反，如果单词很少见，则其权重较高。单词的权重被组合以计算行的相关性。这项技术最适合大型收藏。

Boolean 全文搜索

MySQL 函数和运算符：全文搜索功能

关于

自然语言全文搜索

Boolean 全文搜索

导航菜单

搜索