MySQL如何创建全文索引

作者：UStarGao 日期：2022-06-22 12:09:35 人气：栏目：MySQL

一、MySQL全文索引介绍

MySQL全文索引功能，分为三种：自然语言全文搜索、布尔全文搜索、具有查询扩展的全文搜索。业务生产环境中，不建议使用MySQL进行文本检索类业务的开发。针对大数据检索场景，建议使用更加专业的Elasticsearch技术栈解决方案。

MySQL的全文索引是FULLTEXT类型的索引。
全文索引只能用于InnoDB或MyISAM表，并且只能用于CHAR、VARCHAR或TEXT列。
MySQL提供了一个内置的全文ngram解析器，支持中文、日语和韩语(CJK)，以及一个可安装的日语MeCab全文解析器插件。 （5.7新增）
FULLTEXT索引定义可以在创建表时在CREATE TABLE语句中给出，或者稍后使用ALTER TABLE或CREATE index添加。
对于大型数据集，将数据加载到没有FULLTEXT索引的表中，然后再创建索引，要比将数据加载到已有FULLTEXT索引的表中快得多。

二、操作演示之自然语言全文搜索

2.1 准备表结构

mysql> create database starcto;
Query OK, 1 row affected (0.00 sec)

mysql> use starcto;
Database changed

# 创建articles表
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=InnoDB;

2.2 插入测试数据

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial, we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  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 show ...            |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.01 sec)

2.3 查询测试

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.00 sec)

2.4 查询相关度算分

mysql> SELECT id, body, MATCH (title,body)
    -> AGAINST ('Security implications of running MySQL as root'
    -> IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles
    -> WHERE MATCH (title,body) 
    -> AGAINST('Security implications of running MySQL as root'
    -> IN NATURAL LANGUAGE MODE);
+----+------------------------------------------+----------------------------+
| id | body                                     | score                      |
+----+------------------------------------------+----------------------------+
|  4 | 1. Never run mysqld as root. 2. ...      |         0.6055193543434143 |
|  6 | When configured properly, MySQL ...      |         0.6055193543434143 |
|  1 | DBMS stands for DataBase ...             | 0.000000001885928302414186 |
|  2 | After you went through a ...             | 0.000000001885928302414186 |
|  3 | In this tutorial, we show ...            | 0.000000001885928302414186 |
|  5 | In the following database comparison ... | 0.000000001885928302414186 |
+----+------------------------------------------+----------------------------+
6 rows in set (0.00 sec)

注：搜索的相关性算分，描述了一个文档和查询语句匹配的程度。打分的本质是排序，需要把最符合用户需求的文档排在前面。Elasticsearch在搜索相关性算分方面非常强大。

作者：UStarGao
链接：https://www.starcto.com/mysql/297.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud云平台推荐

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面