大佬教程收集整理的这篇文章主要介绍了MySQL模糊查询再也不用like+%了,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我们都知道 InnoDB 在模糊查询数据时使用 "%xx" 会导致索引失效c;但有时需求就是如此c;类似这样的需求还有很多c;例如c;搜索引擎需要根基用户数据的关键字进行全文查找c;电子商务网站需要根据用户的查询条件c;在可能需要在商品的详细介绍中进行查找c;这些都不是B+树索引能很好完成的工作。
通过数值比较c;范围过滤等就可以完成绝大多数我们需要的查询了。但是c;如果希望通过关键字的匹配来进行查询过滤c;那么就需要基于相似度的查询c;而不是原来的精确数值比较c;全文索引就是为这种场景设计的。
全文索引(Full-Text Search)是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息c;也可以进行各种统计和分析。
在早期的 MySQL 中c;InnoDB 并不支持全文检索技术c;从 MySQL 5.6 开始c;InnoDB 开始支持全文检索。
全文检索通常使用倒排索引(inverted index)来实现c;倒排索引同 B+Tree 一样c;也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射c;这通常利用关联数组实现c;拥有两种表现形式:
上图为 inverted file index 关联数组c;可以看到其中单词"code"存在于文档1,4中c;这样存储再进行全文查询就简单了c;可以直接根据 Documents 得到包含查询关键字的文档;而 full inverted index 存储的是对c;即(DocumentId,Position)c;因此其存储的倒排索引如下图c;如关键字"code"存在于文档1的第6个单词和文档4的第8个单词。相比之下c;full inverted index 占用了更多的空间c;但是能更好的定位数据c;并扩充一些其他搜索特性。
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_scheR_64_11845@A.INNODB_TABLES
WHERE name LIKE 'test/%';
上述六个索引表构成倒排索引c;称为辅助索引表。当传入的文档被标记化时c;单个词与位置信息和关联的DOC_IDc;根据单词的第一个字符的字符集排序权重c;在六个索引表中对单词进行完全排序和分区。
2、在已创建的表上创建全文索引语法如下:
CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);
其语法如下:
@H_99_50@mATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE @H_99_50@mODE
| IN NATURAL LANGUAGE @H_99_50@mODE WITH QUERY EXPANSION
| IN BooleAN @H_99_50@mODE
| WITH QUERY EXPANSION
}
全文搜索使用 @H_364_49@mATCH() AGAINST()语法进行c;其中c;@H_364_49@mATCH() 采用逗号分隔的列表c;命名要搜索的列。AGAINST()
接收一个要搜索的字符串c;以及一个要执行的搜索类型的可选修饰符。全文检索分为三种类型:自然语言搜索、布尔搜索、查询扩展搜索c;下面将对各种查询模式进行介绍。
自然语言搜索将搜索字符串解释为自然人类语言中的短语c;@H_364_49@mATCH()默认采用 Natural Language 模式c;其表示查询带有指定关键字的文档。
接下来结合demo来更好的理解Natural Language
SELECT
count(*) AS count
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( 'MySQL' );
上述语句c;查询 title,body 列中包含 'MySQL' 关键字的行数量。上述语句还可以这样写:
SELECT
count(IF(@H_99_50@mATCH ( title, body )
against ( 'MySQL' ), 1, NULL )) AS count
FROM
`fts_articles`;
上述两种语句虽然得到的结果是一样的c;但从内部运行来看c;第二句SQL的执行速度更快些c;因为第一句SQL(基于where索引查询的方式)还需要进行相关性的排序统计c;而第二种方式是不需要的。
还可以通过sql语句查询相关性:
SELECT
*,
@H_99_50@mATCH ( title, body ) against ( 'MySQL' ) AS Relevance
FROM
fts_articles;
相关性的计算依据以下四个条件:
对于 InnoDB 存储引擎的全文检索c;还需要考虑以下的因素:
如果词在 stopword 中c;则不对该词进行查询c;如对 'for' 这个词进行查询c;结果如下所示:
SELECT
*,
@H_99_50@mATCH ( title, body ) against ( 'for' ) AS Relevance
FROM
fts_articles;
可以看到c;'for'虽然在文档 2c;4中出现c;但由于其是 stopword ,故其相关性为0
参数 innodb_ft_min_token_size 和 innodb_ft_max_token_size 控制 InnoDB 引擎查询字符的长度c;当长度小于 innodb_ft_min_token_size 或者长度大于 innodb_ft_max_token_size 时c;会忽略该词的搜索。在 InnoDB 引擎中c;参数 innodb_ft_min_token_size 的默认值是3c;innodb_ft_max_token_size的默认值是84
布尔搜索使用特殊查询语言的规则来解释搜索字符串c;该字符串包含要搜索的词c;它还可以包含指定要求的运算符c;例如匹配行中必须存在或不存在某个词c;或者它的权重应高于或低于通常情况。例如c;下面的语句要求查询有字符串"Pease"但没有"hot"的文档c;其中+和-分别表示单词必须存在c;或者一定不存在。
SELEct * from fts_test where @H_99_50@mATCH(content) AGAINST('+Pease -hot' IN BooleAN @H_99_50@mODE);
Boolean 全文检索支持的类型包括:
Proximity Search
c;如 @H_364_49@mATCH(context) AGAINST('"Pease hot"@30' IN BooleAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在30字节内lik*
,表示可以是 lik
c;like
c;likes
下面是一些democ;看看 Boolean Mode 是如何使用的。
demo1:+ -
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BooleAN @H_99_50@mODE );
上述语句c;查询的是包含 'MySQL' 但不包含 'YourSQL' 的信息
demo2: no operator
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BooleAN @H_99_50@mODE );
上述语句c;查询的 'MySQL IBM' 没有 '+'c;'-'的标识c;代表 word 是可选的c;如果出现c;其相关性会更高
demo3:@
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BooleAN @H_99_50@mODE );
上述语句c;代表 "DB2" c;"IBM"两个词之间的距离在3字节之内
demo4:> <
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BooleAN @H_99_50@mODE );
上述语句c;查询同时包含 'MySQL'c;'database'c;'DBMS' 的行信息c;但不包含'DBMS'的行的相关性高于包含'DBMS'的行。
demo5: ~
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BooleAN @H_99_50@mODE );
上述语句c;查询包含 'MySQL' 的行c;但如果该行同时包含 'database'c;则降低相关性。
demo6:*
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( 'My*' IN BooleAN @H_99_50@mODE );
demo7:"
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BooleAN @H_99_50@mODE );
上述语句c;查询包含确切短语 'MySQL Security' 的行信息。
查询扩展搜索是对自然语言搜索的修改c;这种查询通常在查询的关键词太短c;用户需要 implied knowledge(隐含知识)时进行c;例如c;对于单词 database 的查询c;用户可能希望查询的不仅仅是包含 database 的文档c;可能还指那些包含 MySQL、Oracle、RDBMS 的单词c;而这时可以使用 Query Expansion 模式来开启全文检索的 implied knowledge
通过在查询语句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启 blind query expansion(又称为 automatic relevance feedBACk)c;该查询分为两个阶段。
接着来看一个例子c;看看 Query Expansion 是如何使用的。
-- 创建索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);
-- 使用 Natural Language 模式查询
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH(title,body) AGAINST('database');
使用 Query Expansion 前查询结果如下:
-- 当使用 Query Expansion 模式查询
SELECT
*
FROM
`fts_articles`
WHERE
@H_99_50@mATCH(title,body) AGAINST('database' WITH QUERY expansion);
使用 Query Expansion 后查询结果如下:
由于 Query Expansion 的全文检索可能带来许多非相关性的查询c;因此在使用时c;用户可能需要非常谨慎。
1、直接删除全文索引语法如下:
DROP INDEX full_idx_name ON db_name.table_name;
2、使用 alter table 删除全文索引语法如下:
ALTER TABLE db_name.table_name DROP INDEX full_idx_name;
本文从理论与实践结合的角度对 fulltext index 做了介绍c;如对 MySQL 感兴趣可继续关注 MySQL 专栏。
以上是大佬教程为你收集整理的MySQL模糊查询再也不用like+%了全部内容,希望文章能够帮你解决MySQL模糊查询再也不用like+%了所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。