MySQL 使用全文索引（fulltext index）
1、 创建全文索引（fulltext index）
旧版的MySQL的全文索引只能用在MyISM 表格的char 、varchar和text字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
① 创建表的同时创建全文索引
CREATE TABLE article ( id INT(10) AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), content TEXT, FULLTEXT(title, content) ) TYPE=MYISAM;
② 通过alter table的方式来添加
ALTER TABLE `table_name` ADD FULLTEXT INDEX index_name (`name`) // index_name是索引名，可以随便起
或者：
ALTER TABLE ` table_name ` ADD FULLTEXT index_name (`name`)
③ 直接通过create index的方式
CREATE FULLTEXT INDEX index_name ON `user` (`name`)
也可以在创建索引的时候指定索引的长度：
CREATE FULLTEXT INDEX index_name ON `user` (`name`(20))
2、 删除全文索引
① 直接使用drop index（注意：没有drop fulltext index这种用法）
DROP INDEX index_name ON table_name ;
② 使用alter table的方式
ALTER TABLE table_name DROP INDEX index_name;
3、 使用全文索引
① 创建MySQL表
CREATE TABLE IF NOT EXISTS `temp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `char` char(50) NOT NULL, `varchar` varchar(50) NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `char` (`char`), FULLTEXT KEY `varchar` (`varchar`), FULLTEXT KEY `text` (`text`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
② 插入数据
INSERT INTO `temp` (`id`, `char`, `varchar`, `text`) VALUES (1, 'a bc 我 知道 1 23', 'a bc 我 知道 1 23', 'a bc 我 知道 1 23');
③ 查询数据
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a')注意：如果一个关键词在50%的数据出现，那么这个词会被当做无效词，如果你想去除50%的现在请使用IN BOOLEAN MODE 搜索
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a' IN BOOLEAN MODE)加入几条无用数据用来解除50%限制
INSERT INTO `temp` ( `id`, `char`, `varchar`, `text` ) VALUES ( NULL, '7', '7', '7' ), ( NULL, '7', '7', '7' ), ( NULL, 'a,bc,我,知道,1,23', 'a,bc,我,知道,1,23', 'a,bc,我,知道,1,23' ), ( NULL, 'x', 'x', 'x' );
这时以下SQL语句都可以查询到数据
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('bc');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('我');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('知道');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('1');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('23');这些语句不能查询到数据
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('b');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('c');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('知');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('道');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('2');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('3');如果搜索多个词，请用空格或者逗号隔开
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a x');
SELECT * FROM `temp` WHERE MATCH(`char`) AGAINST ('a,x');4、 全文检索搜索模式
① MySQL 4.x版本及以上版本提供了全文检索支持，但是表的存储引擎类型必须为MyISAM，以下是建表SQL，注意其中显式设置了存储引擎类型
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
② 插入测试数据
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');③ 全文检索测试
SELECT * FROM `articles ` WHERE MATCH(`title`,`body`) AGAINST ('database');检索结果如下：
注意：
MySQL指定了最小字符长度，默认是4，必须要匹配大于4的才会有返回结果，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度。
ps：这是我已经修改过的，修改方法如下：
在mysql.ini更改最小字符长度，方法时在mysql.int中增加一行：
ft_min_word_len = 1
修改或增加完毕后记得重启mysql
Mysql还会计算一个词的权值，已决定是否出现在结果集中，具体如下：
MySQL在集和查询中的对每个合适的词都会计算它们的权重，一个出现在多个文档中的词将有较低的权重（可能甚至有一个零权重），因为在这个特定的集合中，它有较低的语义值。否则，如果词是较少的，他将得到一个较高的权重，MySQL默认的阀值是50%，上面‘you’在每个文档中都出现，因此是100%，只有低于50%的才会出现在结果集中。
如果不考虑权重，MySQL提供了布尔全文索引（BOOLEAN FULLTEXT SEARCH）
5、 布尔全文检索语法
上面通过 IN BOOLEAN MODE 指定全文检索模式为布尔全文检索。MySQL还提供了类似我们平时使用搜索引擎时用到的语法：逻辑与、逻辑或、逻辑非等、具体通过几个SQL语句来说明：
SELECT * FROM articles WHERE MATCH ( title, body ) AGAINST ( '+apple -banana' IN BOOLEAN MODE );
+ 表示AND，即必须包含。-表示NOT，即不包含。
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('apple banana' IN BOOLEAN MODE);Apple和banana之间是空格，空格表示or，即至少包含Apple ，banana中的一个
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple banana' IN BOOLEAN MODE);必须包含Apple，但是如果同时包含banana则会获得更高的权重
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+apple ~banana' IN BOOLEAN MODE);~ 是我们熟悉的异或运算符。返回的记录必须包含Apple，但是如果同时也包含banana会降低权重。但是它没有+Apple –banana严格，因为后者如果包含banana压根就不返回。
AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);返回同时包含Apple和banana或者同时包含Apple和orange的记录。但是同时包含Apple和banana的记录的权重高于同时包含Apple和orange的记录。
