MySQL 高级特性（9）：全文索引使用指南

在 MySQL 中，我们经常使用 WHERE 条件用于进行值的相等比较、范围过滤。然而，我们也会使用关键字进行搜索，这个基于搜索值与其他值的相关性来进行过滤。MySQL全文检索即用于这种场景。本篇将介绍如何使用全文检索。

全文索引需要特殊的查询语法。有没有索引都可以进行全文检索，但是存在索引时会提高匹配的速度。全文索引的索引通过特殊的结构存储以便于找到文档中包含搜索关键字对应的内容。在我们日常生活中，最常见的全文检索就是网络搜索引擎。虽然，网络搜索引擎的数据里十分庞大，并且通常也不会使用关系型数据库，但是原理是相似的。

全文索引支持通过基于字符（CHAR、VARCHAR 和 TEXT 类型的列）的检索，也可以支持自然语言模式（Natural Language Mode， 默认）和布尔模式 (Boolean Mode)。例如我们搜索“数据库引擎”的时候，内容中包括“数据库”、“引擎”和“数据库引擎”的内容都会检索出来。全文索引的实现有大量的限制，而且十分复杂。但是由于内置在MySQL服务端，而且对很多应用都能够满足要求，因此被广泛使用。

在MySQL5.6之前的版本中，只有 MyISAM 存储引擎支持全文索引。创建全文索引需要指定列标记为全文索引，如下面的 content 列。

CREATE TABLE t_news (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    content TEXT,
    author VARCHAR(32),
    title VARCHAR(128),
  FULLTEXT (content)
) ENGINE=InnoDB;

MySQL 5.6以前对中文搜索支持不是太好，需要自己进行分词后将段落预处理拆分成单词在入库。MySQL5.7.6后才有了内置的分词器 ngram。ngram 支持设置设置分词的长度，可以将中文按长度拆分为不同的单词（虽然不太智能，但满足大部分场景）。可以通过 MySQL 的全局变量ngram_token_size设置分词长度，默认是2，支持1-10可选。对于上面的例子，需要指定分词器构建全文索引。

CREATE TABLE t_news (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    content TEXT,
    author VARCHAR(32),
    title VARCHAR(128),
  FULLTEXT KEY idx(content) WITH PARSER ngram
) ENGINE=InnoDB;

插入一条数据测试。

INSERT INTO `t_news` 
(`id`, `content`, `author`, `title`) 
VALUES ('1', '我有一个数据库和引擎', '岛上码农', '数据库引擎');

在简单的模糊搜索中可以使用 LIKE 来完成，而对于全文检索需要使用如下方式的语句：

SELECT * FROM t_news 
WHERE MATCH (content) AGAINST ('数据 引擎' IN NATURAL LANGUAGE MODE)

通过这种方式可以检索出刚刚插入的内容，而如果使用 LIKE 是没法完成的。也支持使用相关性排序，再插入一条数据：

INSERT INTO `t_news`
(`id`, `content`, `author`, `title`) 
VALUES (2,'我有一个数据库','岛上码农','数据库')

然后执行排序查询：

SELECT *, MATCH (content) AGAINST ('数据 引擎' ) AS relevance
FROM t_news 
WHERE MATCH (content) AGAINST ('数据 引擎' ) 
ORDER BY relevance ASC

这里将匹配值作为一列查询，以便使用其别名进行排序。相关性越高，对应的 relevance 值越大，因此可以用作排序。入股不相关，那么 relevance 的值为0。

布尔模式可以做更多的控制，例如包括使用+号保留匹配结果和使用-号排除匹配结果，下面的就匹配了数据，而排除了包含引擎的数据。更多操作符可以参考 MySQL 的官方文档：全文索引操作符。

SELECT * FROM t_news 
WHERE MATCH (content) AGAINST ('+数据* -引擎' IN BOOLEAN MODE);