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;

文章评论

0条评论