MySQL全文索引

MySQL全文索引是MySQL中提供的一种基于全文索引的搜索功能，主要用于在InnoDB或MyISAM表中搜索信息。

创建全文索引

创建全文索引使用FULLTEXT关键字。你可以在创建表时添加全文索引，或者通过修改已有表结构添加全文索引。

在创建表时添加全文索引：

CREATE TABLE table_name
(
    column1 datatype,
    column2 datatype,
    ...,
    FULLTEXT (column_name)
);

在已经存在的表上添加全文索引：

ALTER TABLE table_name ADD FULLTEXT(column_name);

以上两个SQL语句将在指定的列上创建全文索引。

使用全文索引

全文索引主要使用MATCH()和AGAINST()函数来进行搜索操作。

MATCH() 和 AGAINST() 函数：

其基本语法格式为：

SELECT column_name(s)
FROM table_name
WHERE MATCH (col1,col2,...) AGAINST (expr [search_modifier]);

其中，

• MATCH(col1,col2,..): 这部分定义了要在哪些列上进行全文搜索。你可以在一个或多个列上进行全文搜索。
• AGAINST(expr [search_modifier]): expr 是你要搜索的字符串，search_modifier 是可选项，用于定义搜索模式。

search_modifier 可以是以下几种类型：

• 在自然语言模式搜索（默认）：不使用任何修饰符。
• 在布尔模式搜索：使用 IN BOOLEAN MODE 修饰符。
• 在查询扩展模式搜索：使用 WITH QUERY EXPANSION 修饰符。

例：

SELECT column_name(s)
FROM table_name
WHERE MATCH (col1) AGAINST ('string to search');

此查询将返回包含“string to search”的所有行，其中搜索发生在列col1上。

MySQL的全文搜索的三种模式

MySQL的全文搜索有三种模式：自然语言模式，布尔模式和查询扩展模式。下面我们将逐一对这三种模式进行说明。

自然语言模式：

这是默认的全文搜索模式。在该模式下，MySQL会根据每个单词在整个集合中出现的频率以及它们在给定文档中出现的频率来计算相关性。不允许使用运算符。

SELECT column_name(s)
FROM table_name
WHERE MATCH (column_name) AGAINST ('word_to_search');

布尔模式：

在此模式下，可以使用布尔运算符，如 +（必须存在）、 -（不能存在）、 < > ~ * " "（引号内的词汇必须作为一个词汇匹配）等，以控制搜索的行为。

SELECT column_name(s)
FROM table_name
WHERE MATCH (column_name) AGAINST ('+word1 -word2' IN BOOLEAN MODE);

上述查询将返回所有包含“word1”但不包含“word2”的行。

查询扩展模式：

在此模式下，MySQL首先执行自然语言模式搜索，然后再搜索与初步搜索结果最相关的行，并基于这些行生成查询，然后执行第二次搜索并返回结果。

SELECT column_name(s)
FROM table_name
WHERE MATCH (column_name) AGAINST ('word_to_search' WITH QUERY EXPANSION);

这样的搜索通常会返回更多的结果，因为它们通过搜索与初步搜索结果相关的词汇来扩展查询。

全文索引的限制

• 全文搜索仅支持InnoDB和MyISAM类型的表。

• 区表上不支持分全文索引。

• 大多数多字节字符集都可以使用全文搜索。例外是对于Unicode，可以使用utf8mb3或utf8mb4字符集，但不可以使用ucs2字符集。尽管无法使用ucs2列上的FULLTEXT索引，但你仍可以在没有此类索引的ucs2列上执行IN BOOLEAN MODE（布尔模式）搜索。

• 对于utf8mb3的说明同样适用于utf8mb4，对于ucs2的说明也适用于utf16、utf16le和utf32。

• 汉字和日文等表意文字语言没有词分隔符。因此，内建的全文解析器无法确定这些及其他类似语言中单词的开始和结束位置。

• 提供了一个支持中文、日文和韩文（CJK）的基于字符的ngram全文解析器，以及一个支持日文的基于词的MeCab解析器插件，可用于InnoDB和MyISAM表。

• 尽管支持在单个表中使用多种字符集，但在FULLTEXT索引中的所有列必须使用相同的字符集和排序规则。

• MATCH()函数中的列列表必须与表中某个FULLTEXT索引定义的列列表完全匹配，除非这个MATCH()是在MyISAM表上的IN BOOLEAN MODE。对于MyISAM表，即使是非索引的列也可以进行布尔模式搜索，尽管它们可能会很慢。

• AGAINST()的参数必须是查询评估期间恒定的字符串值。这排除了例如表列，因为它可能对每一行都不同。

• 从MySQL 8.0.28版本开始，MATCH()的参数不能使用汇总列。

• 对于全文搜索，索引提示比非全文搜索更有限。

• 对于InnoDB，在事务提交时处理涉及全文索引列的所有DML操作（INSERT、UPDATE、DELETE）。例如，对于INSERT操作，插入的字符串被标记化并分解为单独的单词。当交易提交时，这些单词随后被添加到全文索引表中。结果是，全文搜索只返回已提交的数据。

• '%'字符不是全文搜索支持的通配符。