mysql 全文索引

注意 并非所有的引擎都支持 全文检索

mysql最常用的引擎 INnodb 和 myisam 后者支持全文检索 前者不支持

创建表的时候指定要检索列

CREATE TABLE TEST_FULLTEXT(note_id int not null auto_increment,note_text text null,
primaty key(note_id),FULLTEXT(note_text)
)engine=myisam;

fulltext 索引某个列 fulltext(note_text) ，在某note_text列上建立全文索引

插入数据

然后用 match()指定列 Against()指定词
如 语句

select *
from TEST_FULLTEXT
where Match(note_text) Against('hello');

查找note_txt列中含有 hello词的行 返回的结果为 两行

note_text
'hello' was said by quester 
quster say 'hello' to pp and he try again

- 注意 搜索是不区分大小的 除非使用 BINARY方式

既然这样 为什么 不用  like语句呢  再来看上面例子  用like实现
select *
from TEST_FULLTEXT
where note_text like '%hello%';

返回的结果一样为两行

note_text
quster say 'hello' to pp and he try again
'hello' was said by quester
看采用全文搜索和like的返回结果   使用全文搜索的返回结果是已经排好序的   而 like的返回结果则没有
排序主要是针对  hello出现在行的位置   
全文结果中 第一个词  和 第三个词    like则没有按顺序排

Mysql主要根据等级来进行排序

我们可以采用下面方式查看 表中某一列 在某一个词的等级 ，继续用上面的例子

select note_text, Match(note_text) Aginst('hello') as rannk
from TEST_FULLTEXT

输出如下：

note_text                                             rank
 fhgjkhj                                                0
 fdsf shi jian                                          0
 quster say 'hello' to pp and he try again           1.3454876123454
 huijia quba                                            0
 'hello' was said by quester                         1.5656454547876

等级的计算 由 mysql 由根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含改词行的数目计算出来 不包含词的行的等级 为0 上面的结果中 词在前面的等级值要高于在后面的

使用查询扩展

当你想要在note_text 中查找 pp时 从上面知道 只有一行 如果用下面语句

select note_text 
from test_fulltext
where match(note_text) against('pp');

返回结果是

note_text
quster say 'hello' to pp and he try again

如果采用扩展查询，分为以下三部

  • 1、先根据全文检索 查找到 所有行 如上面的返回结果 只有一行
  • 2、mysql检索上面1的所以行，选择有用的词
  • 3、mysql再次全文检索，这一次还需要加上2中选择出来的有用的词 作为against中的词
select note_text 
from test_fulltext
where match(note_text) against('pp' with query expansion);

返回结果

note_text
quster say 'hello' to pp and he try again
'hello' was said by quester

如pp本来有的行中含有 hello 所以hello也作为关键字

使用布尔查询 

即使没有建立fulltext索引也能够用，但是速度非常慢 没有50%规则  （参见下 50%规则介绍）
可以用包含特定意义的操作符，如 +、-、""，作用于查询字符串上。查询结果不是以相关性排序的。

如语句

select note_text 
from test_fulltext
where match(note_text) against('hello -pp*' IN BOOLEAN MODE );

表示匹配hello但是不包含 pp的行 结果为

note_text
'hello' was said by quester

全文检索的一些说明 和限制

  • 1、只有MyISAM表支持
  • 2、对大多数的多字节字符集适用，进行全文索引的列必须使用相同的字符集和校验码(collation)。
  • 3、表意性语言，如汉语、日语没有词分界符(英语用空格隔开每个单词)，全文分析器无法确定一个词的开始和结尾，所以MySQL中的全文检索不支持。
  • 4、在自然语言检索中，只能检索被全文索引的那些列，如果要对索引的多列进行某一列的检索，必须对这一列单独建立全文索引。布尔检索可以在非索引的列上进行，但会慢一些。
  • 5、against后的参数必须是常量字符串。
  • 6、索引没有记录关键词在字符串中的位置，排序算法太单一。
  • 7、如果索引不在内存中，检索速度会很慢；如果是短语查询，需要索引和数据都在内存中，否则速度会很慢，所以需要更大的key buffer。索引有碎片时也会很慢，所以需要更频繁的optimize table操作。
  • 8、全文索引对于insert、update、delete都很慢。如更改100个词需要进行100次的索引操作而不是1次。

50% 规则

如果一个词出现在50%以上的行中，那么mysql将他作为一个非用词忽略   50%规则不适用于布尔查询

    如果行数小于三行   则不返回结果  参考 50%规则