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

PHP 2014-11-27 16:24:53 转载来源: 网络整理/侵权必删

本文为大家讲解的是Mysql数据库的FULLTEXT全文索引使用方法概述,感兴趣的同学参考下。在Mysql数据应用开发中，我想不少人应该会遇到以下的这样的一个需求：需要在一个字段中查询和目标字符串是有交集；比如字段名称是分类的ID集合 classids，目标字符串是 2,6,9 ，注意 classids 字段里存放的不是单个ID，也是多个id的集合，如 6,7,8

本文为大家讲解的是Mysql 数据库的FULLTEXT 全文索引使用方法概述,感兴趣的同学参考下。

在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清单。
可以修改最少四个字符的设定。