MySQL全文索引-fulltext

MySQL 5.6 之前的版本只有MyISAM存储引擎支持全文索引，5.6 及之后的版本，InnoDB 才开始支持。全文索引相对于like %这种模糊查询效率更高，查询方式更多样，查询结果也更准确。在全文检索领域，全文索引功能非常有用，比如博客类网站，可以对文章标题，文章内容创建全文索引，用户可以根据关键词搜索想要的文章。

1、创建全文索引

能够创建全文索引的字段类型必须是char，varchar，text等这类字符文本类型。

创建一个带有全文索引的表：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;

通过ALTER TABLE创建、删除全文索引：

创建全文索引

ALTER TABLE articles ADD FULLTEXT KEY ft_title(title);

删除索引

ALTER TABLE articles DROP INDEX ft_title;

2、查询全文索引

MySQL使用MATCH AGAINST语法来查询全文索引，支持自然语言和布尔类型两种查询模式。

自然语言模式查询：

示例：
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’ IN NATURAL LANGUAGE MODE);
±—±------------------±-----------------------------------------+
| id | title | body |
±—±------------------±-----------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
| 5 | MySQL vs. YourSQL | In the following database comparison … |
±—±------------------±-----------------------------------------+
2 rows in set (0.01 sec)

布尔类型模式查询：

+表示AND
-表示NOT
没有符号的表示OR

示例：
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (’+MySQL -YourSQL’ IN BOOLEAN MODE);
±—±----------------------±------------------------------------+
| id | title | body |
±—±----------------------±------------------------------------+
| 6 | MySQL Security | When configured properly, MySQL … |
| 1 | MySQL Tutorial | DBMS stands for DataBase … |
| 2 | How To Use MySQL Well | After you went through a … |
| 3 | Optimizing MySQL | In this tutorial we will show … |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. … |
±—±----------------------±------------------------------------+
5 rows in set (0.02 sec)

+MySQL表示必须包含MySQL
-YourSQL表示必须不包含YourSQL

3、相关性得分

查询结果的相关性得分，通过计算查询词与索引匹配的相似度，计算得分，全文索引查询结果默认按相关性得分倒序排序。
mysql> SELECT title,MATCH (title,body) AGAINST (’+MySQL -YourSQL’ IN BOOLEAN MODE) as score FROM articles WHERE MATCH (title,body) AGAINST (’+MySQL -YourSQL’ IN BOOLEAN MODE);
±----------------------±---------------------------+
| title | score |
±----------------------±---------------------------+
| MySQL Security | 0.000000003771856604828372 |
| MySQL Tutorial | 0.000000001885928302414186 |
| How To Use MySQL Well | 0.000000001885928302414186 |
| Optimizing MySQL | 0.000000001885928302414186 |
| 1001 MySQL Tricks | 0.000000001885928302414186 |
±----------------------±---------------------------+
5 rows in set (0.00 sec)

4、大小写敏感

如果字段的字符集校验规则是大小写敏感的，那么全文索引匹配时也是大小写敏感，否则就是大小写不敏感。通常校验规则名称带bin的都是大小写敏感的，比如utf8mb4_bin。名称带cs一般也是大小写敏感的，比如latin1_general_cs。名称带ci一般是大小写不敏感的，比如utf8mb4_general_ci。

5、支持中文

从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文分词。设置全局变量 ngram_token_size=1，以支持单个字检索。创建全文索引时，加上WITH PARSER ngram，如下：

alter table blog add fulltext ft_title(title) WITH PARSER ngram;