在MySQL 5.7.6之前,全文索引只支持英文全文索引,不支持中文全文索引,需要利用分词器把中文段落预处理拆分成单词,然后存入数据库。
从MySQL 5.7.6开始,MySQL内置了ngram全文解析器,用来支持中文、日文、韩文分词。
本文使用的MySQL 版本是5.7.25,InnoDB数据库引擎。
1. 创建带全文索引的表
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB;
2. 通过 alter table 的方式来添加
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
3. 直接通过create index的方式
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
4. 添加数据
INSERT INTO `articles` VALUES ('2', '文章标题', '内容正文');
5. 执行查询
mysql> SELECT id,title,body,
MATCH (title,body) AGAINST ('标题' IN NATURAL LANGUAGE MODE) AS score
FROM articles where MATCH (title,body) AGAINST ('标题' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
+----+----------+----------+--------------------+
| id | title | body | score |
+----+----------+----------+--------------------+
| 2 | 文章标题 | 内容正文 | 0.0906190574169159 |
+----+----------+----------+--------------------+
1 row in set
mysql> SELECT id,title,body,
MATCH (title,body) AGAINST ('内容' IN NATURAL LANGUAGE MODE) AS score
FROM articles where MATCH (title,body) AGAINST ('内容' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
+----+----------+----------+--------------------+
| id | title | body | score |
+----+----------+----------+--------------------+
| 2 | 文章标题 | 内容正文 | 0.0906190574169159 |
+----+----------+----------+--------------------+
1 row in set
mysql> SELECT id,title,body,
MATCH (title,body) AGAINST ('机会' IN NATURAL LANGUAGE MODE) AS score
FROM articles where MATCH (title,body) AGAINST ('机会' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
Empty set
常用的全文检索模式有两种:
1、自然语言模式(NATURAL LANGUAGE MODE) ,
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符,不能指定关键词必须出现或者必须不能出现等复杂查询。
2、BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符,可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带' IN NATURAL LANGUAGE MODE);
// 不指定模式,默认使用自然语言模式
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带');
可以使用BOOLEAN模式执行高级查询。
// 必须包含"腾讯"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+腾讯' IN BOOLEAN MODE);
BOOLEAN模式下运算符的使用方式:
'apple banana'
无操作符,表示或,要么包含apple,要么包含banana
'+apple +juice'
必须同时包含两个词
'+apple macintosh'
必须包含apple,但是如果也包含macintosh的话,相关性会更高。
'+apple -macintosh'
必须包含apple,同时不能包含macintosh。
'+apple ~macintosh'
必须包含apple,但是如果也包含macintosh的话,相关性要比不包含macintosh的记录低。
'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录,但是apple juice的相关性要比apple pie高。
'apple*'
查询包含以apple开头的单词的记录,如apple、apples、applet。
'"some words"'
使用双引号把要搜素的词括起来,效果类似于like '%some words%',
例如“some words of wisdom”会被匹配到,而“some noise words”就不会被匹配。
- 只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
- 全文索引只支持InnoDB和MyISAM引擎。
- MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名,必须要与创建全文索引时指定的字段名一致。如上面的示例,MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询,就需要在title和body字段上分别创建新的全文索引。
- MATCH()函数使用的字段名只能是同一个表的字段,因为全文索引不能够跨多个表进行检索。
- 如果要导入大数据集,使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多,所以全文索引是很影响TPS的。