Mysql自带的全文检索

sql自带的模糊查询对于检索数据来说,是肯定满足不了要求的,但是项目紧张,又没有那么多时间去研究一些全文检索框架,例如Lucene、Solr、ElasticSearch,咋办呢,Mysql有办法啊,其实,在Mysql数据库中,很早之前就已经支持了全文检索数据,只不过有个重点,外国佬怎么会支持中文尼,哈哈。

其实根本原因是因为英文检索是用空格来对分词进行分隔,而中文肯定不能用空格来分隔,只能通过语义进行分词,用空格的话是肯定检索不出某些词汇的。

良心的是,在Mysql 5.7版本时,MySQL内置了ngram全文检索插件,用来支持中文分词,但是仅对MyISAM和InnoDB引擎有效。

在使用中文全文索引之前,需要对索引最小字符长度进行设置,长度一般默认为4个字符,也就是说,查询的时候只对条件字符串aaaa起作用,对字符串aa不起作用,操作步骤就是在my.ini中的[mysqld]后面添加 ft_min_word_len = 1,将字符长度设置为1即可,此时需要重新启动mysql服务。
注意:如果已经在这之前有全文索引的话,需要重新建立索引,重建索引的语法为:

repair table t_tree_info quick;

创建全文索引

1.创建表的时候创建全文索引
语法一般为:

CREATE TABLE article ( 
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
       title VARCHAR(200), 
       body TEXT, 
       FULLTEXT(title, body) 
 ) TYPE=MYISAM;

建立索引的时候,如果只有单独一个字段的索引,可能无法实现全文索引,需要和另外一个字段建立一个联合索引;另外表的类型尽量设置成MYISAM类型。
2.alter table 的方式添加表的索引
语法为:

ALTER TABLE 表名称 ADD FULLTEXT INDEX 索引名称  (字段名1,字段名2)

3.直接创建索引create index方式
语法为:

CREATE FULLTEXT INDEX 索引名称 ON 表名称(字段名1(20),字段名220))

使用全文索引

使用全文索引的语法为:

 SELECT * FROM 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1*')

在使用过程中如果需要满足多个检索字符,可以采用and连接多个MATCH进行查询。

注意:
使用过程中的50%的门坎限制,当查询结果很多,几乎所有记录都有,或者极少的数据,都有可能会返回非所期望的结果,但是可用IN BOOLEAN MODE即可以避开50%的限制,至于要使用的时候在后面加上即可,例如:

SELECT * FROM 表名称 WHERE MATCH(字段名1) AGAINST(‘条件字符串1’ IN BOOLEAN MODE)

实例:
如果我要查询物资表中的带有 塑料、20 两个字符的检索结果,我需要这样写,需要注意50%限制的问题,查询出来的数据更准确:

select t.* from material t where MATCH('material_name') AGAINST('塑料*' IN BOOLEAN MODE) and MATCH('material_name') AGAINST('20*' IN BOOLEAN MODE)

删除全文索引

语法为:

DROP INDEX 索引名称 ON 表名称 ;

全文索引变量值查询

全文索引有几个需要提前设置的变量值,可以用sql语句直接进行查询:

SHOW VARIABLES LIKE 'ft%'; 

执行后会出现几个变量值得具体数据,例如:

ft_boolean_syntax   + -><()~*:""&|  #改变IN BOOLEAN MODE的查询字符,不用重新启动MySQL也不用重建索引
ft_min_word_len    4                #最短的索引字符串,默认值为4,(通常改为1)修改后必须重建索引文件
ft_max_word_len    84               #最长的索引字符串,默认值为84,修改后必须重建索引文件
ft_query_expansion_limit   20       #查询括展时取最相关的几个值用作二次查询
ft_stopword_file    (built-in)      #全文索引的过滤词文件,具体可以参考:MySQL全文检索中不进行全文索引默认过滤词         

关于查询字符ft_boolean_syntax,一般常用的包含下面几个:
1.” ” :整体匹配,用双引号将一段字符串包裹,表示查询出来的字段要完全相符,不能分隔,例如”李明”,就是查询出带有 “李明” 这个字符串的相关字段结果,而不是返回包含”李小明”、”李大明”等的结果。

2.* :通配符,这个只能接在字符串后面。
MATCH (name) AGAINST (‘+小明‘) #错误,不能放前面
MATCH (name) AGAINST (‘+小明*’) #正确

3.~ :表示拥有该字会降低相关性,只是排在较后面。
+apple ~macintosh 先匹配apple,但如果同时包含macintosh,就排名会靠后面。

4.( ):通过括号来使用字条件
找到包含111和222和333,111和222,或者111和333,语法为:
+111 +(>222 <333)
5.> <:提高与降低相关性

> 提高相关性  <降低相关性
决定查询的结果排得比较靠前还是比较靠后的位置

6.+ : 放在词的前面,表示一定要包含该词,并且必须在字符的开始位置
+Apple 匹配:Apple123, “tommy, Apple”

7.- : 不包含该词,所以不能只用「-yoursql」查不到任何row的,必须搭配其他语法使用。
MATCH (name) AGAINST (‘-林11 +张筱雨’)
匹配到: 所有不包含林11,但包含张筱雨的记录

8.空格,表示可选的,包含该词的顺序较高
apple banana 找至少包含上面词中的一个的记录行
+apple +juice 两个词均在被包含
+apple macintosh 包含词 “apple”,但是如果同时包含 “macintosh”,它的排列更高一些
+apple -macintosh 包含 “apple” 但不包含 “macintosh”

  • 6
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值