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/%';

文章插图

【MySQL模糊查询再也不用like+%了】上述六个索引表构成倒排索引，称为辅助索引表。当传入的文档被标记化时，单个词与位置信息和关联的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
SELECTcount(*) AS count FROM`fts_articles` WHEREMATCH ( title, body ) AGAINST ( 'MySQL' );

文章插图

上述语句，查询 title,body 列中包含 'MySQL' 关键字的行数量。上述语句还可以这样写：
SELECTcount(IF(MATCH ( title, body )against ( 'MySQL' ), 1, NULL )) AS count FROM`fts_articles`;上述两种语句虽然得到的结果是一样的，但从内部运行来看，第二句SQL的执行速度更快些，因为第一句SQL（基于where索引查询的方式）还需要进行相关性的排序统计，而第二种方式是不需要的。