mysql全文索引

数据库引擎

旧版本只有MyISAM支持全文索引，其他数据库引擎不支持（不过好像新的版本中，InnoDB也支持了）。所以在建表的时候需要指定MyISAM引擎

create table (
id serial primary key,
name text,
name_fulltext text,
fulltext(name_fulltext)
)engine=MyISAM default charset=utf8;
-- 网上有很多帖子贴出来的都是type=MyISAM，不知道是什么版本的语法，我的mysql版本是5.7.19，用的关键词是engine

指定全文索引

如上面的建表语句所示，其中fulltext(name_fulltext)表示在name_fulltext列上创建全文索引。

当然也可以在建表完成后，再创建全文索引。

CREATE FULLTEXT INDEX full_text_index_name ON myTable(name);
-- 执行这句话的时候，对于InnoDB引擎的数据库表，会报一个warning，而MyISAM则不会。
-- 0 row(s) affected, 1 warning(s): 124 InnoDB rebuilding table to add column FTS_DOC_ID Records: 0  Duplicates: 0  Warnings: 1

使用全文索引

全文索引的基本单位是词

使用方法如下

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('赵信999999号');
-- 当查询结果过多或过少的时候，上述语句可能会返回非预期结果，可以加 in boolean mode来避免这种情况

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('赵信999999号' in boolean mode);

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('+赵信999999号 -卡特1号' in boolean mode);
-- 上述表示所查询的结果中必须包括词'赵信999999号'，但不包括'卡特1号'，注意是词

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('+赵信999999号 >卡特1号 <卡特2号' in boolean mode);
-- >和<分别代表相关度高和低，上述语句表示所查询的结果中必须包括词'赵信999999号'，如果包含'卡特1号'，则排在前面，如果包含'卡特2号'，则排在后面
-- 注意的是最终查询的顺序是：包含'赵信999999号'且'卡特1号' > 包含'赵信999999号'且'卡特2号' > 包含'赵信999999号'
-- 也就是说，指定的>和<都会比没指定的排在前面

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('+赵信*' in boolean mode);
-- *代表通配符，只能放在字符串后面，不能放在前面

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('+赵信999999号 +(>卡特1号 <卡特2号)' in boolean mode);
-- 可以通过()来表示逻辑逻辑关系

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('+赵信999999号 ~卡特1号' in boolean mode);
-- 可以通过~来降低相关度，但不会排除

SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('"赵信999999号 卡特1号"' in boolean mode);
-- 通过双引号，可以指定词组，但匹配到的一定是 ... 赵信999999号 卡特1号 ... 这个样子的

-- 其实用法有很多，我这里写的用法是参考 http://blog.csdn.net/u011734144/article/details/52817766 这篇博文

与like对比

性能

性能其实很明显，一个全表扫描，一个索引扫描，索引扫描性能一定更高。

举个例子，我在myTable表内插入了100万条数据，每一条数据具有相同内容的name和name_fulltext，但前者是无索引，而后者是有全文索引的。

现在想从100万条数据中找出内容包含’赵信999999号’或’提莫500000号’(词)的行。语句如下

SELECT * from myTable WHERE name like '%赵信999999号%' or name like '%提莫500000号%';
SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('赵信999999号 提莫500000号' in boolean mode);

可以看出，前者涉的rows为1000000，而后者为1，这就是表扫描和索引扫描的区别。

时间消耗如下图

功能

个人觉得like的功能更强大点，like可以通过查找任意位置的字符串匹配。

如select * from myTable where name like '%赵信999999号%' or name like '%提莫500000号%';，它可以查到任意位置包含这两个字符串的行，当然性能消耗大。

而SELECT * FROM myTable WHERE MATCH(name_fulltext) AGAINST('赵信999999号 提莫500000号' in boolean mode);，它只能查到包含这两个词的row，也就是说，如果内容是 赵信999999号单杀提莫500000号这样的row，这个语句是查不到的，它只能查到赵信999999号 单杀 提莫500000号或者赵信999999号,单杀,提莫500000号类似的row。(全文索引的基本单位是词)。

所以个人感觉全文索引对中文不太友好。英文的句子是由单词组成的，如zhaoxin999999 kill timo500000，很适合全文索引。而中文的句子，一般都是赵信999999号单杀提莫500000号，很少会是赵信999999号 单杀 提莫500000号(空格区别)。