飘易博客(作者:Flymorn)
订阅《飘易博客》RSS,第一时间查看最新文章!
飘易首页 | 留言本 | 关于我 | 订阅Feed

查交集?Mysql数据库的FULLTEXT全文索引使用方法概述

Author:飘易 Source:飘易
Categories:数据库 PostTime:2014-10-29 18:14:36
正 文:
    在Mysql数据应用开发中,我想不少人应该会遇到以下的这样的一个需求:需要在一个字段中查询和目标字符串是有交集;比如字段名称是分类的ID集合 classids,目标字符串是 2,6,9 ,注意 classids 字段里存放的不是单个ID,也是多个id的集合,如 6,7,8 。

    这个需求有点像使用 mysql多表连接时的 inner join,inner join查询的结果是两个或多个表的笛卡尔乘积。不过笛卡尔乘积不是我们最终希望得到的结果。

    一、也许你会使用 like 方法查找,但是用 like 需要先行分割,然后再用 多个 or 链接查询,这样做效率会很低。LIKE用法:
SELECT * FROM content WHERE concat(',',classids,',') LIKE ′%,2,%′ or classids LIKE ′%,6,%′ or classids LIKE ′%,9,%′
 
    注意:为了避免查询2时,把21,23 这些也查出来,需要处理前后逗号问题,在 classids 前后 拼接分隔符号,用 concat 函数即可。

    二、也许你还会使用 正则 regexp,比如:
select * from content where concat(',',classids,',') regexp concat(',2,|,6,|,9,');  

或者
select * from content where concat(',',classids,',') regexp concat(',(',replace('2,6,9',',','|'),'),')

    这种方法,由于使用了MYSQL内部函数 concat 和 正则查询,查询效率一样低下。并且也需要处理前后逗号问题。

    三、稍微高级点的 可能会使用MYSQL 的另一个函数 FIND_IN_SET 。

    FIND_IN_SET 函数在查询 一对多的时候特别有用,如果我们上面的 目标字符串 不是id集合,而就是单个的 id 字段, 那么使用 FIND_IN_SET 函数是比较合适的。

    FIND_IN_SET 使用方法:FIND_IN_SET(str,strlist) 
    sql语句如下:
SELECT * FROM `content` WHERE FIND_IN_SET('2',classids )

补充:
FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

    但是我们的是需求是多个id集合,怎么办? 也有变通的方法:
    先把需要查询的字符串分割,然后一个个查:
SELECT * FROM content WHERE FIND_IN_SET('2',classids ) or FIND_IN_SET('6',classids ) or FIND_IN_SET('9',classids )
 
    虽然看上去高级了,但是效率还是不高啊。

    四、本文的重点来了,使用 MYSQL FULLTEXT 全文索引 MATCH AGAINST

    针对我们这样的需求,使用 全文索引 是比较合适的一种方法。先来看使用的sql语句:
SELECT * FROM `content` WHERE MATCH(classids) AGAINST('2,6,9' IN BOOLEAN MODE) 

    看到没,很精简,但是完全满足我们的需求,而且效率还比较高。在数据里大的时候,像discuz这类论坛在查询时,一般会使用 这种全文索引的方法查询。

    MATCH AGAINST 全文检索的默认分隔符是标点符号(英文逗号,点号,空格)和stopwords,其中前者正是我们需要的特性。全文检索按照逗号将MATCH和AGAINST里的字符串做分割,然后将它们匹配。

    使用 全文索引 FULLTEXT 必须要注意的几点
1、需要对目标字段建立fulltext索引
2、每个被标点符号分割的word长度必须在3个字符以上,这才是关键,我们的id太短了,会被自动忽略掉,这个时候你可以考虑让id从一个比较大值开始自增,比如1000,这样它就够长了。
   【原因】:mysql有一个系统变量FT_MIN_WORD_LEN指定了全文检索时可接受的最小单词长度,默认值是4。
   【修改】修改 my.ini (Linux 下是 my.cnf ) ,在 [mysqld] 后面加入一行“ft_min_word_len=1 ”,然后【必须重建全文索引】,否则将无法使用全站搜索功能。

3、你撞到了stopwords,比如你的tags字段是这样的'hello,nobody',nobody是mysql的一个默认的stop words,它会被自动忽略。stop words是英文中的一些无意义词,搜索的时候不需要它们,类似汉语中的助词等等。但在我们的使用中显然不是用来做搜索的,因此可以在my.cnf文件里,加上ft_stopword_file=''来禁用它。

     上面默认的 MATCH AGAINST 实际上默认使用了自然语言搜索方式
select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword')
select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword' IN NATURAL LANGUAGE MODE)

    这2个语句其实一样的。
    【自然语言搜索模式的么特点】:
# 忽略停词(stopword),英语中频繁出现的and/or/to等词被认为是没有实际搜索的意义,搜索这些不会获得任何结果。
# 如果某个词在数据集中频繁出现的几率超过了50%,也会被认为是停词,所以如果数据库中只有一行数据,不管你怎么全文搜索都不能获得结果。
# 搜索结果都具有一个相关度的数据,返回结果自动按相关度由高到低排列。
# 只针对独立的单词进行检索,而不考虑单词的局部匹配,如搜索box时,就不会将boxing作为检索目标。

    为了避免上面的“频繁出现的几率超过了50%,则不会返回结果”,我们可以使用另外一种搜索方式IN BOOLEAN MODE 
   比如:
SELECT * FROM `content` WHERE MATCH(groupids) AGAINST('1,4' IN BOOLEAN MODE) 

    【IN BOOLEAN MODE的特色】: 
不剔除50%以上符合的row。 
不自动以相关性反向排序。 
可以对没有FULLTEXT index的字段进行搜寻,但会非常慢。 
限制最长与最短的字符串。 
套用Stopwords。 


    【MySQL的FULLTEXT怎么断字】: 
字母、数字、底线的组合视为一个字,不会把底线断字。 
会被断字的字符:空白、逗号(,)与点(.),但不用这些断字的语言,如中文,就得自行手动断字。 
可以自行实做一个断字的外挂来取代内建的断字parser。 
接受一个单引号,如aaa'bbb视为一个字,但是aaa''bbb就是两个字。 
前缀或字尾的单引号会被去掉,如'aaa或aaa'。 
全文搜寻时,stopword与少于四个字符的字符串会被忽略。 
可以覆写内建的stopword清单。 
可以修改最少四个字符的设定。 
作者:飘易
来源:飘易
版权所有。转载时必须以链接形式注明作者和原始出处及本声明。
上一篇:umeditor在CHROME下清除格式报错:removeAttributeNode
下一篇:HTTPS架设:LNMP和LNMPA配置NameCheap的comodo positivessl SSL(9美元/年)
1条评论 “查交集?Mysql数据库的FULLTEXT全文索引使用方法概述”
1 gavin
2022-5-5 16:55:36
帮了大忙了,感谢???
发表评论
名称(*必填)
邮件(选填)
网站(选填)

记住我,下次回复时不用重新输入个人信息
© 2007-2019 飘易博客 Www.Piaoyi.Org 原创文章版权由飘易所有