MySQL模糊查询再也用不着 like % 了！

媒介

我们都晓得 InnoDB 在模糊查询数据时利用 "%xx" 会招致索引失效，但有时需求就是如斯，类似如许的需求还有良多，例如，搜刮引擎需要根底用户数据的关键字停止全文查找，电子商务网站需要按照用户的查询前提，在可能需要在商品的详细介绍中停止查找，那些都不是B+树索引能很好完成的工做。

通过数值比力，范畴过滤等就能够完成绝大大都我们需要的查询了。但是，若是希望通过关键字的婚配来停止查询过滤，那么就需要基于类似度的查询，而不是本来的切确数值比力，全文索引就是为那种场景设想的。

全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的肆意信息查找出来的手艺。它能够按照需要获得全文中有关章、节、段、句、词等信息，也能够停止各类统计和阐发。

在早期的 MySQL 中，InnoDB 其实不撑持全文检索手艺，从 MySQL 5.6 起头，InnoDB 起头撑持全文检索。

倒排索引

全文检索凡是利用倒排索引（inverted index）来实现，倒排索引同 B+Tree 一样，也是一种索引构造。它在辅助表中存储了单词与单词本身在一个或多个文档中所在位置之间的映射，那凡是操纵联系关系数组实现，拥有两种表示形式：

inverted file index：{单词，单词所在文档的id}

full inverted index：{单词，（单词所在文档的id，再详细文档中的位置）}

上图为 inverted file index 联系关系数组，能够看到此中单词"code"存在于文档1,4中，如许存储再停止全文查询就简单了，能够间接按照 Documents 得到包罗查询关键字的文档；而 full inverted index 存储的是对，即（DocumentId,Position），因而其存储的倒排索引如下图，如关键字"code"存在于文档1的第6个单词和文档4的第8个单词。

比拟之下，full inverted index 占用了更多的空间，但是能更好的定位数据，并扩大一些其他搜刮特征。

全文检索创建全文索引1、创建表时创建全文索引语法如下：

CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200), title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

输入查询语句：

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLESWHERE name LIKE 'test/%';

上述六个索引表构成倒排索引，称为辅助索引表。当传入的文档被标识表记标帜化时，单个词与位置信息和联系关系的DOC_ID，按照单词的第一个字符的字符集排序权重，在六个索引表中对单词停止完全排序和分区。

2、在已创建的表上创建全文索引语法如下：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

利用全文索引

MySQL 数据库撑持全文检索的查询，全文索引只能在 InnoDB 或 MyISAM 的表上利用，而且只能用于创建 char,varchar,text 类型的列。

其语法如下：

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}

全文搜刮利用 MATCH() AGAINST()语法停止，此中，MATCH()接纳逗号分隔的列表，定名要搜刮的列。AGAINST()领受一个要搜刮的字符串，以及一个要施行的搜刮类型的可选润色符。全文检索分为三品种型：天然语言搜刮、布尔搜刮、查询扩展搜刮，下面将对各类查询形式停止介绍。

Natural Language

天然语言搜刮将搜刮字符串解释为天然人类语言中的短语，MATCH()默认接纳 Natural Language 形式，其暗示查询带有指定关键字的文档。

接下来连系demo来更好的理解Natural Language

SELECT count(*) AS count FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( 'MySQL' );

上述语句，查询 title,body 列中包罗 'MySQL' 关键字的行数量。上述语句还能够如许写：

SELECT count(IF(MATCH ( title, body ) against ( 'MySQL' ), 1, NULL )) AS count FROM `fts_articles`;

上述两种语句固然得到的成果是一样的，但从内部运行来看，第二句SQL的施行速度更快些，因为第一句SQL（基于where索引查询的体例）还需要停止相关性的排序统计，而第二种体例是不需要的。

还能够通过SQL语句查询相关性：

SELECT *, MATCH ( title, body ) against ( 'MySQL' ) AS Relevance FROM fts_articles;

相关性的计算根据以下四个前提：

word 能否在文档中呈现

word 在文档中呈现的次数

word 在索引列中的数量

几个文档包罗该 word

关于 InnoDB 存储引擎的全文检索，还需要考虑以下的因素：

查询的 word 在 stopword 列中，忽略该字符串的查询

查询的 word 的字符长度能否在区间 [innodb_ft_min_token_size,innodb_ft_max_token_size] 内

若是词在 stopword 中，则不合错误该词停止查询，如对 'for' 那个词停止查询，成果如下所示：

SELECT *, MATCH ( title, body ) against ( 'for' ) AS Relevance FROM fts_articles;

能够看到，'for'固然在文档 2，4中呈现，但因为其是 stopword ,故其相关性为0

参数 innodb_ft_min_token_size 和 innodb_ft_max_token_size 控造 InnoDB 引擎查询字符的长度，当长度小于 innodb_ft_min_token_size 或者长度大于 innodb_ft_max_token_size 时，会忽略该词的搜刮。在 InnoDB 引擎中，参数 innodb_ft_min_token_size 的默认值是3，innodb_ft_max_token_size的默认值是84

Boolean

布尔搜刮利用特殊查询语言的规则来解释搜刮字符串，该字符串包罗要搜刮的词，它还能够包罗指定要求的运算符，例如婚配行中必需存在或不存在某个词，或者它的权重应高于或低于凡是情况。

例如，下面的语句要求查询有字符串"Pease"但没有"hot"的文档，此中+和-别离暗示单词必需存在，或者必然不存在。

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Boolean 全文检索撑持的类型包罗：

+：暗示该 word 必需存在

-：暗示该 word 必需不存在

(no operator)暗示该 word 是可选的，但是若是呈现，其相关性会更高

@distance暗示查询的多个单词之间的间隔能否在 distance 之内，distance 的单元是字节，那种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句暗示字符串 Pease 和 hot 之间的间隔需在30字节内

>：暗示呈现该单词时增加相关性

<<>：暗示呈现该单词时降低相关性

~：暗示允许呈现该单词，但呈现时相关性为负

* ：暗示以该单词开头的单词，如 lik*,暗示能够是 lik，like，likes

" ：暗示短语

下面是一些demo，看看 Boolean Mode 是若何利用的。

demo1：+ -

SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );

上述语句，查询的是包罗 'MySQL' 但不包罗 'YourSQL' 的信息