表索引——全文索引

会飞的鱼-blog

已于 2023-05-06 14:26:44 修改

阅读量1.1k

点赞数 1

分类专栏： Mysql复习文章标签：数据库 mysql java

于 2023-04-28 13:45:00 首次发布

本文链接：https://blog.csdn.net/m0_65635427/article/details/130383578

版权

Mysql复习专栏收录该内容

24 篇文章 1 订阅

订阅专栏

前言

        全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

        全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

        MySQL从3.23.23版本开始支持全文索引，MySQL5.6以前只能在存储引擎为MyISAM的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

一、全文索引

1.创建表时定义索引

CREATE TABLE tablename(

    propname1 type1,

    propname2 type2,

    ……

    propnamen type..n,

    FULLTEXT INDEX | KEY

    [indexname] (propnamen [(length)] ) );

mysql> create database school; #创建数据库school

mysql> use school; #选择数据库school

mysql>create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment)); #创建表class, 并建立为comment 字段为全文索引

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2

mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.'); #插入记录3

mysql> select * from class where match(comment) AGAINST('我是一个兵');#利用全文检索索引快速查询记录

2.已存在的表上创建索引

方法1：执行 create 语句

在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：

CREATE FULLTEXT INDEX indexname

ON tablename( propname1 [ ( length ) ] );

在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。

如下例表已存在，可通过CREATE语句创建全文索引：

mysql> create database school; #创建数据库school

mysql> use school; #选择数据库school

mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引

mysql> create FULLTEXT index index_teacher on class(teacher ); #追加全文索引

mysql> show create table class; #查看表定义

方法2：执行alter table 语句

除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

ALTER TABLE tablename

ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

3.使用场景

根据全文索引字段进行全文检索数据：

SELECT * FROM 表名WHERE MATCH(`列名`) AGAINST('关键字');

mysql> create database school; #创建数据库school

mysql> use school; #选择数据库school

mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),info varchar(1024),FULLTEXT INDEX index_des(info)); #创建表class, 并建立为info 字段为全文索引

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2

mysql> insert into class values(3,'3班','Janny','I'm Miss Zhang.'); # 插入记录3

mysql> select * from class where match(teacher) AGAINST('我是一个兵');#根据id查询记录

二、MySQL8 中文分词支持

配置文件my.ini（Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0）中增加如下配置项，同时重启MySQL80 服务:

[mysqld]

ngram_token_size=2

mysql> create database school; #创建数据库school

mysql> use school; #选择数据库school

mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser ngram); #创建表class, 并建立为comment 字段为全文索引

mysql> insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!'); # 插入记录1

mysql> insert into class values(2,'2班','Rock','此班主任毕业自唐僧系'); # 插入记录2

mysql> insert into class values(3,'3班','Janny','I''m Miss Zhang.'); #插入记录3

mysql> select * from class where match(comment) AGAINST('百姓');#利用全文检索索引快速查询记录

mysql> select * from class where match(comment) AGAINST('唐僧');#利用全文检索索引快速查询记录