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 <ccc) // 找到有aaa和bbb,或者aaa和ccc,然后aaa&bbb排在aaa&ccc前面 

~:将其相关性由正转负,表示拥有该字会降低相关性,但不像「-」将之排除,只是排在较后面。 

*:万用字,不像其他语法放在前面,这个要接在字符串后面。 

" ":用双引号将一段句子包起来表示要完全相符,不可拆字。 

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执行的

标签:<a href="/?tag=mysql">mysql</a>,<a href="/?tag=全文检索">全文检索</a>