`
wcdzxxgc
  • 浏览: 82181 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

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执行的。
分享到:
评论

相关推荐

    MySQL中文全文索引插件64位版本

    一、MySQL中文全文索引插件mysqlcft的特点:  1、优点:  ①、精准度很高:采用自创的“三字节交叉切分算法”,对中文语句进行分割,无中文分词词库,搜索精准度远比中文分词算法高,能达到LIKE '%...%"的准确率。...

    MySQL全文索引实现简单版搜索引擎实例代码

    只有Innodb和myisam存储引擎能用全文索引(innodb支持全文索引是从mysql5.6开始的) char、varchar、text类型字段能创建全文索引(fulltext index type) 全文索引的基于关键词的,如何区分不同的关键词了,就要...

    mysqlcft中文全文索引插件 32位版本

    一、MySQL中文全文索引插件mysqlcft的特点:  1、优点:  ①、精准度很高:采用自创的“三字节交叉切分算法”,对中文语句进行分割,无中文分词词库,搜索精准度远比中文分词算法高,能达到LIKE '%...%"的准确率。...

    Mysql全文搜索match against的用法

    对于大的数据库,将数据装载到一...建立全文检索先期配置由于Mysql的默认配置是索引的词的长度是4,所以要支持中文单字的话,首先更改这个.*Unix用户要修改my.cnf,一般此文件在/etc/my.cnf,如果没有找到,先查找一下find /

    Mysql实现全文检索、关键词跑分的方法实例

    一、前言 今天一个同事问我,如何使用 ...在 MySQL 5.7.6 版本之前,全文索引只支持英文全文索引,不支持中文全文索引,需要利用分词器把中文段落预处理拆分成单词,然后存入数据库。本篇文章测试的时候,采用的 Mysql

    MySQL 5.1参考手册中文版

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)...

    MySQL 5.1中文手冊

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    MySQL 5.1官方简体中文参考手册

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    MySQL 5.1参考手册 (中文版)

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    mysql5.1 中文手册 CHM

    这是MySQL参考手册的翻译版本,关于MySQL参考手册,请访问dev.mysql.com。 支持查询,支持全文索引

    mysql5.1中文手册

    MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是什么 1.8.2. 选择SQL模式 1.8.3. 在ANSI模式下运行MySQL 1.8.4. MySQL对标准SQL的扩展 1.8.5. MySQL与标准SQL的...

    MYSQL中文手册

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合...

    mysql官方中文参考手册

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    MySQL5.1参考手册官方简体中文版

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    MySQL 5.1参考手册

    微调MySQL全文搜索 12.8. Cast函数和操作符 12.9. 其他函数 12.9.1. 位函数 12.9.2. 加密函数 12.9.3. 信息函数 12.9.4. 其他函数 12.10. 与GROUP BY子句同时使用的函数和修改程序 12.10.1. GROUP BY(聚合)函数 ...

    深度解析MySQL 5.7之中文全文检索

    这不,从MySQL 5.7开始,MySQL内置了ngram全文检索插件,用来支持中文分词,并且对MyISAM和InnoDB引擎有效。 在使用中文检索分词插件ngram之前,先得在MySQL配置文件里面设置他的分词大小,比如, [mysqld] ngram_...

    Sphinx搜索引擎架构与使用文档(和MySQL结合)V1.1.

    4、创建Sphinx索引文件和MySQL数据文件存放目录 6 5、创建MySQL配置文件(以线上配置文件为准) 6 ⑴、创建配置文件/mysql/3306/my.cnf(以db11的3306端口为例) 6 ⑵、创建配置文件/mysql/3406/my.cnf(以db11的...

    mysql全文模糊搜索MATCH AGAINST方法示例

    问题是match against对中文模糊搜索支持不是太好 新建一个utf8 MyISAM类型的表并建立一个全文索引 : CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body...

    2023年全新MySQL大厂面试经验

    ### 创建索引 #### 创建表之后建立索引 ```sql create TABLE user_index( id int auto_increment primary ...-- 鸡肋,全文索引不支持中文 add FULLTEXT KEY (information); ``` `show create table user_index`:

    NewvBB Core v1.1 RC(vBulletin 简体中文化插件版)

    中文搜索完美解决 3.4 版本(可以在改进的二元分割算法(效率高、负载小) 、直接搜索 post 表(更准确、数据库空间占用小)和 MySQL 全文索引(MySQL 版本低于 4.1.1 不支持中文搜索)三种方式选择) ...

Global site tag (gtag.js) - Google Analytics