目次媒介我们都知道 InnoDB 在含糊查询数据时使用 "%xx" 会导致索引失效，但偶尔需求就是如此，类似这样的需求另有很多。 比方，搜刮引擎须要根基用户数据的关键字进行全文查找，电子商务网站须要根据用户的查询条件，在可能须要在商品的详细介绍中进行查找，这些都不是 B+ 树索引能很好完成的工作。 通过数值比力，范围过滤等就可以完成绝大多数我们须要的查询了。但是，假如希望通过关键字的匹配来进行查询过滤，那么就须要基于相似度的查询，而不是原来的精确数值比力，全文索引就是为这种场景设计的。 全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据须要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。 在早期的 MySQL 中，InnoDB 并不支持全文检索技术，从 MySQL 5.6 开始，InnoDB 开始支持全文检索通常使用倒排索引（inverted index）来实现，倒排索引同 B+Tree 一样，也是一种索引布局。它在辅助表中存储了单词与单词自身在一个或多个文档中地点位置之间的映射。 这通常使用关联数组实现，拥有两种表现情势：
inverted file index 关联数组，这样存储再进行全文查询就简朴了，可以直接根据 Documents 得到包含查询关键字的文档。 而 full inverted index 存储的是对，即（DocumentId，Position），相比之下，full inverted index 占用了更多的空间，但是能更好的定位数据，并扩充一些其他搜刮特性。 创建全文索引①创建表时创建全文索引语法如下： [code]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;[/code]输入查询语句： [code]SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE 'test/%';[/code]上述六个索引表构成倒排索引，称为辅助索引表。当传入的文档被标记化时，单个词与位置信息和关联的 DOC_ID，根据单词的第一个字符的字符集排序权重，在六个索引表中对单词进行完全排序和分区。 ②在已创建的表上创建全文索引语法如下： [code]CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);[/code]使用全文索引MySQL 数据库支持全文检索的查询，全文索引只能在 InnoDB 或 MyISAM 的表上使用，并且只能用于创建 char，varchar，text 范例的列。 其语法如下： [code]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 }[/code]全文搜刮使用 MATCH() AGAINST() 语法进行，此中，MATCH() 接纳逗号分隔的列表，命名要搜刮的列。 AGAINST() 接收一个要搜刮的字符串，以及一个要实行的搜刮范例的可选修饰符。全文检索分为三种范例：自然语言搜刮、布尔搜刮、查询扩展搜刮，下面将对各种查询模式进行介绍。 Natural Language自然语言搜刮将搜刮字符串解释为自然人类语言中的短语，MATCH() 默认接纳 Natural Language 模式，其表示查询带有指定关键字的文档。 接下来结合 demo 来更好的明白 Natural Language： [code]SELECT count(*) AS count FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( 'MySQL' );[/code]上述语句，查询 title，body 列中包含 'MySQL' 关键字的行数目。上述语句还可以这样写： [code]SELECT count(IF(MATCH ( title, body ) against ( 'MySQL' ), 1, NULL )) AS count FROM `fts_articles`;[/code]上述两种语句固然得到的结果是一样的，但从内部运行来看，第二句 SQL 的实行速度更快些，由于第一句 SQL（基于 where 索引查询的方式）还须要进行相干性的排序统计，而第二种方式是不须要的。 还可以通过 SQL 语句查询相干性： [code]SELECT *, MATCH ( title, body ) against ( 'MySQL' ) AS Relevance FROM fts_articles;[/code]相干性的盘算依据以下四个条件：
对于 InnoDB 存储引擎的全文检索，还须要考虑以下的因素：
假如词在 stopword 中，则不对该词进行查询，如对 'for' 这个词进行查询，结果如下所示： [code]SELECT *, MATCH ( title, body ) against ( 'for' ) AS Relevance FROM fts_articles;[/code]可以看到，'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"的文档，此中+和-分别表示单词必须存在，大概一定不存在。 [code]select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);[/code] [code]Boolean 全文检索支持的范例包罗：[/code]
下面是一些 demo，看看 Boolean Mode 是怎样使用的。 demo1：+ - [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );[/code] [code]上述语句，查询的是包含 'MySQL' 但不包含 'YourSQL' 的信息。[/code]demo2：no operator [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );[/code]上述语句，查询的 'MySQL IBM' 没有 '+'，'-'的标识，代表 word 是可选的，假如出现，其相干性会更高。 demo3：@ [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );[/code]上述语句，代表 "DB2" ，"IBM"两个词之间的间隔在 3 字节之内。 demo4：> < [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );[/code]上述语句，查询同时包含 'MySQL'，'database'，'DBMS' 的行信息，但不包含'DBMS'的行的相干性高于包含'DBMS'的行。 demo5: ~ [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );[/code]上述语句，查询包含 'MySQL' 的行，但假如该行同时包含 'database'，则低落相干性。 demo6：* [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );[/code] [code]上述语句，查询关键字中包含'My'的行信息。[/code]demo7：" [code]SELECT * FROM `fts_articles` WHERE MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );[/code]上述语句，查询包含确切短语 'MySQL Security' 的行信息。 Query Expansion查询扩展搜刮是对自然语言搜刮的修改，这种查询通常在查询的关键词太短，用户须要 implied knowledge（隐含知识）时进行。 比方，对于单词 database 的查询，用户可能希望查询的不但仅是包含 database 的文档，可能还指那些包含 MySQL、Oracle、RDBMS 的单词，而这时可以使用 Query Expansion 模式来开启全文检索的 implied knowledge。 通过在查询语句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启 blind query expansion（又称为 automatic relevance feedback）。 该查询分为两个阶段：
接着来看一个例子，看看 Query Expansion 是怎样使用的。 [code]-- 创建索引 create FULLTEXT INDEX title_body_index on fts_articles(title,body);[/code] [code]-- 使用 Natural Language 模式查询 SELECT * FROM `fts_articles` WHERE MATCH(title,body) AGAINST('database');[/code]使用 Query Expansion 前查询结果如下： [code]-- 当使用 Query Expansion 模式查询 SELECT * FROM `fts_articles` WHERE MATCH(title,body) AGAINST('database' WITH QUERY expansion);[/code]使用 Query Expansion 后查询结果如下： 由于 Query Expansion 的全文检索可能带来许多非相干性的查询，因此在使用时，用户可能须要非常谨慎。 删除全文索引①直接删除全文索引语法如下： [code]DROP INDEX full_idx_name ON db_name.table_name;[/code]②使用 alter table 删除全文索引语法如下： [code]ALTER TABLE db_name.table_name DROP INDEX full_idx_name;[/code]总结本文从理论与实践结合的角度对 fulltext index 做了介绍。InnoDB 的全文检索在一些简朴的搜刮场景下还是比力实用的，可以更换 like+%，并且不须要额外依赖其他服务。复杂搜刮场景的话，我们还是须要使用 ES 这类搜刮引擎。 全文索引技术可以有用地从大量文本中检索信息,适用于搜刮引擎和电商平台等场景,InnoDB从MySQL 5.6开始支持全文索引,使用倒排索引实现,全文检索分为自然语言搜刮、布尔搜刮和查询扩展搜刮三种模式,全文索引提高了含糊查询的服从,优化了基于文本的搜刮查询。 到此这篇关于MySQL全文索引在数据库中的应用和优势（含糊查询不用like+%）的文章就介绍到这了,更多相干MySQL全文索引与like+%内容请搜刮脚本之家从前的文章或继续浏览下面的相干文章希望各人以后多多支持脚本之家！ 来源：https://www.jb51.net/database/327038p30.htm 免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
/6 
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
GMT+8, 2025-7-2 08:30 , Processed in 0.034263 second(s), 18 queries .
Powered by Mxzdjyxk! X3.5
© 2001-2025 Discuz! Team.
