详解MySQL全文检索方法
1. 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;
其中FULLTEXT(title, body) 给title和body这两列建立全文索引，之后检索的时候注意必须同时指定这两列。
2. 插入测试数据
- 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 ...');
3. 全文检索测试
- SELECT * FROM articles
- WHERE MATCH (title,body) AGAINST ('database');
检索结果如下：
- 5 MySQL vs. YourSQL In the following database comparison ...
- 1 MySQL Tutorial DBMS stands for DataBase ...
说明全文匹配时忽略大小写。
4. 可能遇到的困扰
到目前为止都很顺利，但是如果检索SQL改为下面会怎样呢？
- SELECT * FROM articles
- WHERE MATCH (title,body) AGAINST ('well');
结果让人大跌眼镜，开始我也困惑了许久，后来去网上查了下才知道原来是这么回事：
mysql指定了最小字符长度，默认是4，必须要匹配大于4的才会有返回结果，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度，也可以在mysql配置文件my.ini 更改最小字符长度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完后重启mysql即可。
所以上面不能返回结果。但是我用上面的方法改配置文件并重启MySQL服务器后，再用show命令查看，并没有改变。
另外，MySQL还会计算一个词的权值，以决定是否出现在结果集中，具体如下：
mysql在集和查询中的对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重)，因为在这个特定的集中，它有较低的语义值。否则，如果词是较少的，它将得到一个较高的权重，mysql默认的阀值是50%，上面‘you’在每个文档都出现，因此是100%，只有低于50%的才会出现在结果集中。
但是如果不考虑权重，那么该怎么办呢？MySQL提供了布尔全文检索（BOOLEAN FULLTEXT SEARCH）
假设well在所有记录中都出现，并且ft_min_word_len已经改为2，那么下面的SQL检索语句得到的结果集将包含所有记录：
- SELECT * FROM articles WHERE MATCH (title,body)
- AGAINST ('well' IN BOOLEAN MODE );
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压根就不返回。
- SELECT * FROM articles WHERE MATCH (title,body)
- AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);
返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。
6. MySQL不支持中文的全文检索
默认MySQL不支持中文全文检索，怎么办？大致方法有下面几个：
A. 扩展MySQL，添加中文全文检索支持，难度较大
B. 为中文内容表提供一个对应的英文索引表（即将FULLTEXT索引列按照一定的规则转化成英文索引表中的每一条记录，比如全部进行base64编码，内容表和英文索引表的id相同），检索时先将检索词也用相同规则转换成英文，然后再使用。如果还要支持按拼音全文检索，那么还需要在索引表中增加对应的拼音内容（就需要中文转拼音算法了）。当然如果还需要支持中英文交互搜索，比如搜索William时也需要返回威廉，反之亦然，那么还需要将威廉对应的英文翻译也存到索引表中去。
参考网上的链接，具体做法包括先对中文内容进行分词，然后中文转换为四位区位码存到索引表中。检索时，包含中文的检索词也要先分词，再转换为四位区位码，然后在索引表中进行全文检索。
7. 核对条目
A. 只有存储引擎类型为MyISAM类型的表，并且MySQL版本为4.X或者以上才能使用MySQL内置的全文检索支持
B. MySQL全文检索默认不支持中文，且对英文检索时忽略大小写
C. MySQL全文检索时，默认检索长度为4，即关键词的长度必须大于5才能被捕获
D. MySQL全文检索时，所有FULLTEXT索引列必须使用相同的字符集
E. MySQL全文检索返回结果集时还会考虑权重
F. MySQL全文检索还支持灵活的布尔全文检索模式
G. 更多内容参考MySQL5官方手册
- 没有章节