mysql5.7 前后关于对中文检索的差异和介绍

一、概述

      MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。


二、语法

      MATCH (col1,col2,...) AGAINST (expr [search_modifier])

      search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }

     

      例如:SELECT * FROM tab_name WHERE MATCH ("列名1,列名2...列名n") AGAINST("词1 词2 词3 ... 词m");

   

      即:MATCH 相当于要匹配的列,而 AGAINST 就是要找的内容。 

      这里的table需要是MyISAM类型的表,col1、col2 必须是char、varchar或text类型,在查询之前需要在 col1 和 col2 上分别建立全文索引(FULLTEXT索引)。


三、检索方式

     1、自然语言检索: IN NATURAL LANGUAGE MODE


     2、布尔检索: IN BOOLEAN MODE

          剔除一半匹配行以上都有的词,譬如说,每个行都有this这个字的话,那用this去查时,会找不到任何结果,这在记录条数特别多时很有用,

          原因是数据库认为把所有行都找出来是没有意义的,这时,this几乎被当作是stopword(中断词);但是若只有两行记录时,是啥鬼也查不出来的,

          因为每个字都出现50%(或以上),要避免这种状况,请用IN BOOLEAN MODE。


       ● IN BOOLEAN MODE的特色:

          ·不剔除50%以上符合的row。

          ·不自动以相关性反向排序。

          ·可以对没有FULLTEXT index的字段进行搜寻,但会非常慢。

          ·限制最长与最短的字符串。

          ·套用Stopwords。

 

       ● 搜索语法规则:

         +   一定要有(不含有该关键词的数据条均被忽略)。

         -   不可以有(排除指定关键词,含有该关键词的均被忽略)。 

         >   提高该条匹配数据的权重值。

         <   降低该条匹配数据的权重值。

         ~   将其相关性由正转负,表示拥有该字会降低相关性(但不像 - 将之排除),只是排在较后面权重值降低。

         *   万用字,不像其他语法放在前面,这个要接在字符串后面。

         " " 用双引号将一段句子包起来表示要完全相符,不可拆字。


         SELECT * FROM articles WHERE MATCH (title,content) AGAINST ("+apple -banana" IN BOOLEAN MODE);

         + 表示AND,即必须包含。- 表示NOT,即必须不包含。即:返回记录必需包含 apple,且不能包含 banner。


         SELECT * FROM articles WHERE MATCH (title,content) AGAINST ("apple banana" IN BOOLEAN MODE);

         apple和banana之间是空格,空格表示OR。即:返回记录至少包含apple、banana中的一个。


         SELECT * FROM articles WHERE MATCH (title,content) AGAINST ("+apple banana" IN BOOLEAN MODE);

         返回记录必须包含apple,同时banana可包含也可不包含,若包含的话会获得更高的权重。


         SELECT * FROM articles WHERE MATCH (title,content) AGAINST ("+apple ~banana" IN BOOLEAN MODE);

         ~ 是我们熟悉的异或运算符。返回记录必须包含apple,若也包含了banana会降低权重。

         但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。


         SELECT * FROM articles WHERE MATCH (title,content) AGAINST ("+apple +(>banana <orange)" IN BOOLEAN MODE);

         返回必须同时包含“apple banana”或者必须同时包含“apple orange”的记录。

         若同时包含“apple banana”和“apple orange”的记录,则“apple banana”的权重高于“apple orange”的权重。



    3、查询扩展检索: WITH QUERY EXPANSION



四、MySQL全文检索的条件限制

     1、在MySQL5.6以下,只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。

     2、相应字段建立FULLTEXT索引



五、与全文检索相关的系统变量:

     ft_min_word_len = 全文检索的最小许可字符(默认4,通过 SHOW VARIABLES LIKE "ft_min_word_len" 可查看),

     中文通常是两个字就是一个词,所以做中文的话需要修改这个值为2最好。



六、总结事项

     1、预设搜寻是不分大小写,若要分大小写,columne 的 character set要从utf8改成utf8_bin。


     2、预设 MATCH...AGAINST 是以相关性排序,由高到低。


     3、MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。

          如果只要单查title或content一个字段,那得另外再建一个 FULLTEXT(title) 或 FULLTEXT(content),也因为如此,MATCH()的字段一定不能跨table,但是另外两种搜寻方式好像可以。

   

     4、MySQL不支持中文全文索引,原因很简单:与英文不同,中文的文字是连着一起写的,中间没有MySQL能找到分词的地方,截至目前MySQL5.6版本是如此,但是有变通的办法,就是将整句的中文分词,并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。



MySQL5.7之后也支持对中文的全文检索,这里描述如何启用MySQL的中文全文检索。


首先,MySQL启用全文检索要对字段加全文检索的索引,注意,一个表只能建立一个全文检索字段,如需要检索多个字段,需要将多个字段一起建立索引,单独建立多个索引是无效的。所以建立方法如下:


ALTER TABLE `localgo`.`entity` ADD FULLTEXT INDEX `entity_info` (`entity_name`, `entity_introduction`) WITH PARSER ngram;

MySQL5.7支持对中文进行全文检索,其自带了内部的切词系统,默认切词系统的字段是4,但中文一般是两个字组成一个单词,因此需要改变。首先看一下内部切词的长度


SHOW VARIABLES LIKE "ft_min_word_len";

SHOW VARIABLES LIKE "ft%";


◆ 步骤1 配置my.ini,在my.ini末尾添加如下:


# 修改全文检索的最小许可字符为2个字符或汉字

发现结果都是4,下面更改数据库配置,在[client]下加上ft_min_word_len = 2,在[mysqld]下加上另两行。如下所示,注意,一般情况下这两个配置下都含有其他设置,这里省去了,只需要在这两个配置的末尾加上如下内容即可,不要删除之前的配置。


完成后“重启 MySQL 服务”,并用 SHOW VARIABLES LIKE "ft_min_word_len" 查询下是否得到了正确的结果值为2,




[mysqld]

ft_min_word_len = 2

ngram_token_size=2

[client]

ft_min_word_len = 2

最后,用如下语句即可支持MySQL对中文进行全文检索了(注意检索词需要放在星号之间,支持空格或者分号作为关键词分隔符)。


SELECT * FROM localgo.entity WHERE MATCH(`entity_name`,`entity_introduction`) AGAINST("*程序*" IN BOOLEAN MODE);


select

    us.userName as nickname,

    us.headImg as faceImg,

    search.*

    from

    (

    SELECT id, userid, title, faceimg as cpimg, 

    collectioncount as collections,

    (collectioncount + visitcount) as total,

    createtime

    from recipe_info

    where MATCH (title, tagKey) AGAINST ("*肉*" IN BOOLEAN MODE) and `status` in (0,1) and isEnable=1

    order by total desc,id desc limit 1, 10

    ) as search

    inner join user as us

    on us.id = search.userid and us.`status`=0

    order by total desc;


            
            

本博客源码Github地址:

https://github.com/whisnos/myblog

请随手给个star,谢谢!

打赏

评论