MySQL -- 索引 三 FULLTEXT

MySQL 评论

建立全文索引

对于MySQL，全文索引建立在char、varchar和text的字段上，以前只能建立在 MyISAM 引擎上，现在新版MySSL 也可建立全文索引。

设置 MySQL 配置文件

1
2
3
4
5
[mysqld]
ft_wordlist_charset #表示词典的字符集
ft_wordlist_file #词表文件，每行一个词及其词频
ft_stopword_file #过滤掉不索引的词表，一行一个
ft_min_word_len #加入索引的词的最小长度，默认为4，为了支持中文单字故可设置为2

三种建立方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// 方式一
CREATE TABLE article ( 
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    title VARCHAR(200), 
    body TEXT, 
    FULLTEXT(title, body) 
) TYPE=MYISAM;

// 方式二
ALTER TABLE student ADD FULLTEXT INDEX ft_stu_name (name);
ALTER TABLE student ADD FULLTEXT ft_stu_name (name);

// 方式三
CREATE FULLTEXT INDEX ft_email_name ON student (name);
// 也可指定长度
CREATE FULLTEXT INDEX ft_email_name ON student (name(20));
``` 

两种删除方式
``` bash
// 方式一
DROP INDEX full_idx_name ON tommy.girl;

// 方式二
ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

搜索语法

MATCH (col1,col2,…) AGAINST (expr[search_modifier])
col1,col2,…为已建立FULLTEXT索引并要从中查找数据的列
expr为要查找的文本内容
search_modifier的每个取值代表一种类型的全文搜索
search_modifier取值:
IN NATURAL LANGUAGE MODE：
自然语言全文搜索（默认）, 把搜索字符串解释为一系列单词并查找包含这些单词的数据行。

1
2
3
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);
或
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');

IN BOOLEAN MODE：
布尔全文搜索
 把搜索字符串解释为一系列单词，但允许使用一些操作符字符来"修饰"这些单词以表明特定的要求，如某给定单词必须出现（或不出现）在匹配数据行里，某个数据行必须包含一个精确的短语，等等


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -贯彻' IN BOOLEAN MODE);

   “+” 用在词的前面，表示一定要包含该词，并且必须在开始位置。
       eg: +Apple 匹配：Apple123,     "tommy, Apple"
“-” 不包含该词，所以不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用。
   	eg: MATCH (girl_name) AGAINST ('-林志玲 +张筱雨')
     	匹配到： 所有不包含林志玲，但包含张筱雨的记录 
空字符表明后跟的单词是可选的,但出现的话会增加该行的相关性
       apple banana 找至少包含上面词中的一个的记录行
    	+apple +juice 两个词均在被包含
    	+apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列将更高一些
    	+apple -macintosh 包含 “apple” 但不包含 “macintosh”
“@distance” 用于指定两个或多个单词相互之间的距离（以单词度量）需在指定的范围内
“>” 提高该字的相关性，查询的结果会排在比较靠前的位置。
“<” 降低相关性，查询的结果会排在比较靠后的位置。
	总结：
	1. 只要使用 ><的总比没用的 靠前；
       2. 使用  >的一定比 <的排的靠前 (这就符合相关性提高和降低)；
       3. 使用同一类的，使用的越早，排的越前。
“()” 用于将单词分组为子表达式且可以嵌套
	eg: +aaa +(>bbb <ccc) 
	// 找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，
       // 然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc
“~” 将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。
	 eg:   +apple ~macintosh   先匹配apple，但如果同时包含macintosh，就排名会靠后。
“*” 为普通的通配符，若为单词指定了通配符，那么即使该单词过短或者出现在了停止字列表中它也不会被移除,这个只能接在字符串后面.
        MATCH (girl_name) AGAINST ('+*ABC*')   #错误，不能放前面
        MATCH (girl_name) AGAINST ('+张筱雨*')  #正确
“" "” 整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字。 
        eg:  "tommy huang" 可以匹配  tommy huang xxxxx   但是不能匹配  tommy is huang。


WITH QUERY EXPANSION：
查询扩展全文搜索,这种搜索分两阶段进行。第一次，查出用户给定的关键词对应的记录；第二次，用第一次查出的结果里的关键词，再去查一次，把两次的结果返回给用户。

1
SELECT id,title,body FROM articles WHERE MATCH(title,body) AGAINST('fulltext' with query expansion ) ORDER BY id ASC;