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 可以使用“IN BOOLEAN MODE”修饰符执行 Boolean 全文本搜索。
- 使用此修饰符，某些字符在搜索字符串中单词的开头或结尾具有特殊含义。
在下面的查询中，“+”和“-”运算符指示必须存在或不存在一个词，匹配才会发生。因此，查询将检索包含单词“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 will 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 仅支持前导减号。
- 注意：“-”运算符仅用于排除其他搜索词匹配的行。因此，仅包含以“-”开头的术语的布尔模式搜索将返回空结果。它不会返回“除包含任何排除的术语的所有行之外的所有行”。【！！！】
- “(no operator)”：
- 默认情况下（当未指定 + 或 - 时），该单词是可选的，但包含该单词的行的评分较高。这模仿没有“IN BOOLEAN MODE”修饰符的“MATCH()... AGAINST()”的行为。
- “@distance”：【？？？】
- 该运算符仅适用于 InnoDB table 。它测试两个或多个单词是否都以单词为单位在彼此指定的距离内开始。在 @distance 运算符之前的双引号字符串中指定搜索词，例如“MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)”。
- “> <”：
- 这两个运算符用于更改单词对分配给行的相关性值的贡献。“>”运算符增加贡献，而“<”运算符减小贡献。
- “( )”：
- 括号将单词分组为子表达式。带括号的组可以嵌套。
- “~”：
- 前导波浪号充当否定运算符，使单词对行相关性的贡献为负。这对于标记干扰词很有用。包含此类单词的行的评分低于其他单词，但并未像“-”运算符一样完全排除在外。
- “*”：
- 星号用作截断（或通配符）运算符。与其他运算符不同，它被附加到受影响的单词上。如果单词以“*”运算符之前的单词开头，则匹配。
- 如果使用截断运算符指定了单词，则即使布尔值太短或停用词，也不会从布尔查询中删除该单词。单词是否太短取决于 InnoDB table 的“innodb_ft_min_token_size”设置或 MyISAM table 的“ft_min_word_len”。这些选项不适用于使用 ngram 解析器的 FULLTEXT 索引。
- 通配符被视为必须出现在一个或多个单词开头的前缀。如果最小字长为 4，则搜索“'+word +the*'”可能比搜索“'+word +the'”返回更少的行，因为第二个查询会忽略搜索词“the”太短。
- “"”：
- 包含在双引号（"）字符中的短语仅匹配包含单词 literally（按其键入）的行。全文引擎将短语分解为单词，并在 FULLTEXT 索引中搜索单词。非单词字符不必完全匹配：短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同。例如，"test phrase" 匹配 "test, phrase"。
- 如果该短语不包含索引中的单词，则结果为空。由于多种因素，这些单词可能不在索引中：如果它们在文本中不存在，是停用词，或者比索引单词的最小长度短。
下面的示例演示一些使用布尔全文运算符的搜索字符串：
- “'apple banana'”：
- 查找至少包含两个单词之一的行。
- “'+apple +juice'”：
- 查找包含两个单词的行。
- “'+apple macintosh'”：
- 查找包含单词“apple”的行，但如果行中也包含“macintosh”，则将行排名更高。
- “'+apple -macintosh'”：
- 查找包含单词“apple”但不包含“macintosh”的行。
- “'+apple ~macintosh'”：
- 查找包含单词“apple”的行，但是如果该行还包含单词“ macintosh”，则将其评分低于不包含单词“macintosh”的行。这比对'+apple -macintosh'的搜索“软”，对于'+apple -macintosh'的搜索，“ macintosh”的存在将导致根本不返回该行。
- “'+apple +(>turnover <strudel)'”：
查找包含单词“apple”和“turnover”，或“apple”和“strudel”（以任何顺序），但将“apple turnover”排名高于“apple strudel”的行。
- “'apple*'”：
- 查找包含诸如“apple”，“apples”，“applesauce”或“applet”之类的单词的行。
- “'"some 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 条与“数据库”搜索词匹配。第一记录(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 命令行 Client 端中执行计算返回的排名值为 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 命令行 Client 端返回的排名值略有不同：（1.0886961221694946 与 1.088696164686938）。
- 差异是由于整数和浮点数/双精度数之间的转换是由 InnoDB 在内部执行的（以及相关的精度和舍入决策），以及它们如何在其他地方（例如，在 MySQL 命令行 Client 端或其他类型的计算器中）执行。
多词搜索的相关性排名
本示例演示了基于 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 命令行 Client 端将返回预期的排名值：
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)
具有查询扩展功能的全文本搜索
全文搜索支持查询扩展（尤其是其变体“盲查询扩展”）。当搜索短语太短时，这通常很有用，这通常意味着用户依赖于全文搜索引擎缺乏的隐含知识。
- 例如，搜索“database”的用户实际上可能意味着“MySQL”，“Oracle”，“DB2”和“RDBMS”都是应与“database”匹配且也应返回的短语。
这就是隐含的知识。
通过在搜索词组后面添加“WITH QUERY EXPANSION”或“IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION”，可以启用盲查询扩展（也称为“自动相关性反馈”）。
- 它的工作原理是执行两次搜索，其中第二次搜索的搜索词组是【“原始搜索词组”跟“与第一次搜索高度相关的文档”相结合】。因此，如果其中一个文档包含单词“databases”和单词“MySQL”，则第二次搜索将查找包含单词“MySQL”的文档，即使它们不包含单词“database”。
以下示例显示了这种差异：
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)
mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title | body |
+----+-----------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 6 | MySQL Security | When configured properly, MySQL ... |
| 2 | How To Use MySQL Well | After you went through a ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)
另一个示例是：
- 当用户搜索 Georges Simenon 的有关 Maigret 的书，但不确定如何拼写“Maigret”时。
- 没有查询扩展，在搜索“Megre and the reluctant witnesses”时，仅找到“Maigret and the Reluctant Witnesses”。
- 带有查询扩展的搜索，将在第二遍查找所有带有单词“Maigret”的书籍。
注意：由于“盲查询扩展”往往会返回不相关的文档而显著增加干扰，因此仅在搜索短语较短时才使用它。
全文停用词
使用服务器字符集和排序规则（“character_set_server”和“collation_server”系统变量的值），加载停用词列表并搜索全文查询。
- 如果用于全文索引或搜索的停用词文件或列的字符集或排序规则不同于 character_set_server 或 collation_server，则对于停用词查找可能会出现错误的命中或遗漏。
- 停用词查找是否区分大小写取决于服务器排序规则。例如，如果排序规则为 latin1_swedish_ci，则查找不区分大小写；如果排序规则为 latin1_general_cs 或 latin1_bin，则查找区分大小写。
InnoDB 搜索索引的停用词
InnoDB 的默认停用词列表相对较短，因为来自技术，文学和其他来源的文档经常使用简短词作为关键字或重要短语。例如，您可能搜索“存在或不存在”并期望得到明智的结果，而不是忽略所有这些词。
要查看默认的 InnoDB 停用词列表，请查询“INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD”。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.00 sec)
要为所有 InnoDB 表定义自己的停用词列表，请定义与“INNODB_FT_DEFAULT_STOPWORD”表具有相同结构的表，并使用停用词填充该表，并将“innodb_ft_server_stopword_table”选项的值设置为“db_name/table_name”形式的值，然后再创建全文索引。
- 停用词表必须具有一个名为“value”的VARCHAR列。
- 以下示例演示了为 InnoDB 创建和配置新的全局停用词表：
-- Create a new stopword table mysql> CREATE表my_stopwords(value VARCHAR(30)) ENGINE = INNODB; Query OK, 0 rows affected (0.01 sec) -- Insert stopwords (for simplicity, a single stopword is used in this example) mysql> INSERT INTO my_stopwords(value) VALUES ('Ishmael'); Query OK, 1 row affected (0.00 sec) -- Create the table mysql> CREATE表opening_lines ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200) ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) -- Insert data into the table mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES ('Call me Ishmael.','Herman Melville','Moby-Dick'), ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'), ('I am an invisible man.','Ralph Ellison','Invisible Man'), ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'), ('It was love at first sight.','Joseph Heller','Catch-22'), ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'), ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'), ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 -- Set the innodb_ft_server_stopword_table option to the new stopword table mysql> SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords'; Query OK, 0 rows affected (0.00 sec) -- Create the full-text index (which rebuilds the表if no FTS_DOC_ID column is defined) mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line); Query OK, 0 rows affected, 1 warning (1.17 sec) Records: 0 Duplicates: 0 Warnings: 1
通过查询“INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE”中的单词，验证是否未显示指定的停用词（'Ishmael'）。
- 默认情况下，长度少于 3 个字符或长度大于 84 个字符的单词不会出现在 InnoDB 全文搜索索引中。可以使用“innodb_ft_max_token_size”和“innodb_ft_min_token_size”变量配置最大和最小字长值。此默认行为不适用于 ngram 解析器插件。 ngram 令牌大小由“ngram_token_size”选项定义。
mysql> SET GLOBAL innodb_ft_aux_table='test/opening_lines'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 15; +-----------+ | word | +-----------+ | across | | all | | burn | | buy | | call | | comes | | dalloway | | first | | flowers | | happened | | herself | | invisible | | less | | love | | man | +-----------+ 15 rows in set (0.00 sec)
若要逐表创建停用词列表，请创建其他停用词表，并使用“innodb_ft_user_stopword_table”选项指定在创建全文索引之前要使用的停用词表。
MyISAM 搜索索引的停用词
如果 character_set_server 是 ucs2，utf16，utf16le 或 utf32，则使用 latin1 加载和搜索停用词文件。
要覆盖 MyISAM 表的默认停用词列表，请设置 ft_stopword_file 系统变量。变量值应该是包含停用词列表的文件的路径名，或者是用于禁用停用词过滤的空字符串。除非给出绝对路径名以指定其他目录，否则服务器将在数据目录中查找文件。更改此变量的值或停用词文件的内容之后，请重新启动服务器并重建FULLTEXT索引。
停用词列表是自由格式的，使用任何非字母数字字符（例如换行符，空格或逗号）分隔停用词。但例外是，下划线字符（_）和单个撇号（'）被视为单词的一部分。停用词列表的字符集是服务器的默认字符集。
以下列表显示了MyISAM 搜索索引的默认停用词：
- 在 MySQL 源代码发行版中，您可以在“storage/myisam/ft_static.c”文件中找到此列表。
a's able about above according
accordingly across actually after afterwards
again against ain't all allow
allows almost alone along already
also although always am among
amongst an and another any
anybody anyhow anyone anything anyway
anyways anywhere apart appear appreciate
appropriate are aren't around as
aside ask asking associated at
available away awfully be became
because become becomes becoming been
before beforehand behind being believe
below beside besides best better
between beyond both brief but
by c'mon c's came can
can't cannot cant cause causes
certain certainly changes clearly co
com come comes concerning consequently
consider considering contain containing contains
corresponding could couldn't course currently
definitely described despite did didn't
different do does doesn't doing
don't done down downwards during
each edu eg eight either
else elsewhere enough entirely especially
et etc even ever every
everybody everyone everything everywhere ex
exactly example except far few
fifth first five followed following
follows for former formerly forth
four from further furthermore get
gets getting given gives go
goes going gone got gotten
greetings had hadn't happens hardly
has hasn't have haven't having
he he's hello help hence
her here here's hereafter hereby
herein hereupon hers herself hi
him himself his hither hopefully
how howbeit however i'd i'll
i'm i've ie if ignored
immediate in inasmuch inc indeed
indicate indicated indicates inner insofar
instead into inward is isn't
it it'd it'll it's its
itself just keep keeps kept
know known knows last lately
later latter latterly least less
lest let let's like liked
likely little look looking looks
ltd mainly many may maybe
me mean meanwhile merely might
more moreover most mostly much
must my myself name namely
nd near nearly necessary need
needs neither never nevertheless new
next nine no nobody non
none noone nor normally not
nothing novel now nowhere obviously
of off often oh ok
okay old on once one
ones only onto or other
others otherwise ought our ours
ourselves out outside over overall
own particular particularly per perhaps
placed please plus possible presumably
probably provides que quite qv
rather rd re really reasonably
regarding regardless regards relatively respectively
right said same saw say
saying says second secondly see
seeing seem seemed seeming seems
seen self selves sensible sent
serious seriously seven several shall
she should shouldn't since six
so some somebody somehow someone
something sometime sometimes somewhat somewhere
soon sorry specified specify specifying
still sub such sup sure
t's take taken tell tends
th than thank thanks thanx
that that's thats the their
theirs them themselves then thence
there there's thereafter thereby therefore
therein theres thereupon these they
they'd they'll they're they've think
third this thorough thoroughly those
though three through throughout thru
thus to together too took
toward towards tried tries truly
try trying twice two un
under unfortunately unless unlikely until
unto up upon us use
used useful uses using usually
value various very via viz
vs want wants was wasn't
way we we'd we'll we're
we've welcome well went were
weren't what what's whatever when
whence whenever where where's whereafter
whereas whereby wherein whereupon wherever
whether which while whither who
who's whoever whole whom whose
why will willing wish with
within without won't wonder would
wouldn't yes yet you you'd
you'll you're you've your yours
yourself yourselves zero
全文限制
【。。。。】
微调 MySQL 全文搜索
【。。。。】
为全文索引添加排序规则
【。。。。】
ngram 全文分析器
【。。。。】
MeCab 全文分析器插件
【。。。。】