MySQL如何创建全文索引

原创

Power

发布于 2025-02-28 21:34:14

48500

代码可运行

运行总次数：0

代码可运行

一、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索引的表中快得多。

官网介绍：MySQL5.7 MySQL5.6

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

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)

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

TDSQL MySQL 版