mysql全文索引中文问题的解决（转）_MySQL

MySQL从3.23.23开始就逐渐支持全文索引和搜寻。 
全文索引就是建index，全文搜寻就是去查index。 
LIKE是用Regular Expression去做查询。 
MySQL全文索引是一种index type：FULLTEXT。 
全文索引的index只能用在MyISAM表格的char、varchar和text的字段上。 
全文索引的index可以在create table、alter table和create index时产生。 
create table... 
CREATE TABLE article ( 
   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    title VARCHAR(200), 
    body TEXT, 
    FULLTEXT(title, body) 
) TYPE=MYISAM; 
alter table... 
create index... 
要倒大量的数据到有全文索引index的table速度会很慢，建议先拿掉全文索引index再倒数据，倒完后再加上全文索引index。 
全文搜寻的语法： 
MATCH (col1, col2,...) AGAINST (expr [search_modifier]) 
三种搜寻方式： 
IN BOOLEAN MODE 
IN NATURAL LANGUAGE MODE 
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION 

IN NATURAL LANGUAGE MODE 
expr就是要搜寻的字符串。 
没有特殊字符。 
套用Stopwords。 
剔除一半row以上都有的字，譬如说，每个row都有mysql这个字的话，那用mysql去查时，会找不到任何row，这在row的数量无敌多时很有用，因为把所有row都找出来是没有意义的，这时，mysql几乎被当作是stopword；但是当row只有两笔时，是啥鬼也查不出来的，因为每个字都出现50%以上，要避免这种状况，请用IN BOOLEAN MODE。 
预设的搜寻方式。 
SELECT * 
FROM article 
WHERE MATCH(title, body) 
AGAINST ('xxx' IN NATURAL LANGUAGE MODE); 
预设搜寻是不分大小写，若要分大小写，columne的character set要从utf8改成utf8_bin。 
预设MATCH...AGAINST是以相关性排序，由高到低。 
MATCH...AGAINST可以跟所有MySQL语法搭配使用，像是JOIN或是加上其他过滤条件。 
-- 第一种count 
SELECT COUNT(*) 
FROM article 
WHERE MATCH(title, body) 
AGAINST ('xxx' IN NATURAL LANGUAGE MODE); 
-- 第二种count 
SELECT COUNT(IF(MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count 
FROM article 
当符合的笔数较多时，第一种count比较慢，因为MATCH...AGAINST会先依相关性排序。 
当符合的笔数较少时，第二种count比较慢，因为第二种count会扫过所有数据。 
MATCH(title, body)里的字段必须和FULLTEXT(title, body)里的字段一模一样，如果只要单查title或body一个字段，那得另外再建一个FULLTEXT(title)或FULLTEXT(body)，也因为如此，MATCH()的字段一定不能跨table，但是另外两种搜寻方式好像可以。 
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score 
FROM article; 
这样可以取得相关值，而且也因为没有WHERE和ORDER BY，所以不会排序。 
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score 
FROM article 
WHERE MATCH(title, body) 
AGAINST ('xxx' IN NATURAL LANGUAGE MODE); 
排序又取得相关性，虽然MATCH...AGAINST用了两次，但是MySQL知道这两个MATCH...AGAINST是一样的，所以只会用一次。 
SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score 
FROM article 
ORDER BY score desc; 
为啥不这样用就好？ 
MySQL的FULLTEXT怎么断字： 
字母、数字、底线的组合视为一个字，不会把底线断字。 
会被断字的字符：空白、逗号（,）与点（.），但不用这些断字的语言，如中文，就得自行手动断字。 
可以自行实做一个断字的外挂来取代内建的断字parser。 
接受一个单引号，如aaa'bbb视为一个字，但是aaa''bbb就是两个字。 
前缀或字尾的单引号会被去掉，如'aaa或aaa'。 
全文搜寻时，stopword与少于四个字符的字符串会被忽略。 
可以覆写内建的stopword清单。 
可以修改最少四个字符的设定。 

IN BOOLEAN MODE 
expr里有特殊字符辅助特殊的搜寻语法。 
SELECT * 
FROM article 
WHERE MATCH(title, body) 
AGAINST ('+mysql -yoursql' IN BOOLEAN MODE); 
一定要有msysql，且不要有yoursql。 
IN BOOLEAN MODE的特色： 
不剔除50%以上符合的row。 
不自动以相关性反向排序。 
可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。 
限制最长与最短的字符串。 
套用Stopwords。 
搜寻语法： 
+：一定要有。 
-：不可以有，但这个「不可以有」指的是在符合的row里不可以有指定的字符串，所以不能只下「-yoursql」这样是查不到任何row的，必须搭配其他语法使用。 
：（什么都没）预设用法，表示可有可无，有的话排比较前面，没有的排后面。 
>：提高该字的相关性。 
( )：条件可以巢状。 
+aaa +(>bbb ~：将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。 
*：万用字，不像其他语法放在前面，这个要接在字符串后面。 
" "：用双引号将一段句子包起来表示要完全相符，不可拆字。 

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 
也可以用WITH QUERY EXPANSION。 
IN NATURAL LANGUAGE MODE的衍生版。 
先用IN NATURAL LANGUAGE MODE做搜寻，得到最相关的字段的字再加到原expr里，再查一次。 
神奇功能之一：可以用database查出mysql或oracle，第一次查询用databae得到一些结果，从这些结果里抽取字符串，此时得到mysql与oracle的机率相当高，最后用database和这些出取出来的字符串做一次查询。 
神奇功能之二：无法拼出正确字符串时，第一次用「相似」的错误字符串查询，很有可以得到正确的字符串，再用正确的字符串急可以得到想要的结果。 
因为这种查询方式会让「噪声」爆增，所以建议第一次的查询字符串尽量精简。 
Stopwords请参考http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html。 
全文搜寻的限制： 
只能用在MyISAM表格上。 
支援UTF-8。 
中文支持问题： 
MySQL不会断中文字：MySQL内建的字依据是空白、逗号和点，对此内建机制的白痴解法是，存中文字时自行塞入空白断字，但是还是有下面的限制。 
查询字符串最少四个字符的限制：所以一二三个中文字都不能查，必须将ft_min_word_len从预设的4改成1。 
虽然同一个表格可以有不同字符集的字段，但是同一个FULLTEXT index里的字段必须是同一个字符集与collation。 
MATCH里的字段必须和FULLTEXT里的一模一样，IN BOOLEAN MODE允许不一样，甚至使用未FULLTEXT index的字段，但速度很慢。 
AGAINST里必须是字符串，不可以是变量或域名。 
全文搜寻使index hint受限。 
MySQL全文搜寻设定： 
大部分的参数都是启动参数，也就是修改后必须重新启动MySQL。 
有些参数修改必须重新产生索引文件。 
mysql> SHOW VARIABLES LIKE 'ft%'; 

ft_boolean_syntax    + ->ft_min_word_len    4 
ft_max_word_len    84 
ft_query_expansion_limit   20 ft_stopword_file    (built-in) 

ft_min_word_len：最短的索引字符串，默认值为4，修改后必须重建索引文件。 
ft_max_word_len：最长的索引字符串，默认值因版本而不同，余同上一点。 
[mysqld] 
ft_min_word_len=1 
ft_stopword_file：stopword档案路径，若留空白不设定表示要停用stopword过滤，修改后必须重新启动MySQL和重建索引；stopword档案内容可以用分行空白与逗号区隔stopword，但底线和单引号视为合法的字符串字符。 
50%的门坎限制：配置文件在storage/myisam/ftdefs.h，将 #define GWS_IN_USE GWS_PROB 改为 #define GWS_IN_USE GWS_FREQ，然后重新编译MySQL，因为近低门坎会影响数据的精准度，所以不建议如此，可用IN BOOLEAN MODE即可以避开50%的限制。 
ft_boolean_syntax：改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引。 
修改字符串字符的认定，譬如说将「-」认定为字符串的合法字符： 
方法一：修改storage/myisam/ftdefs.h的true_word_char()与misc_word_char()，然后重新编译MySQL，最后重建索引。 
方法二：修改字符集档，然后在FULLTEXT index的字段使用该字符集，最后重建索引。 
重建索引： 
每个有FULLTEXT index的表格都要这么做。 
mysql> REPAIR TABLE tbl_name QUICK; 
要注意如果用过myisamchk，会导致上述的设定值回复成默认值，因为myisamchk不是用MySQL的设定值。 
解法一：将修改过得设定值加到myisamchk的参数里。 
shell> myisamchk --recover --ft_min_word_len=1 tbl_name.MYI 
解法二：两边都要设定。 
[mysqld] 
ft_min_word_len=1 
[myisamchk] 
ft_min_word_len=1 
解法三：用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE与ALTER TABLE取代myisamchk语法，因为这些语法是由MySQL执行的。