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;
文章评论