表索引——全文索引

目录

一、全文索引

1.创建表时定义索引

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

方法1：执行 create 语句

方法2：执行alter table 语句

3.使用场景

二、MySQL8 中文分词支持

总结

---

前言

        全文索引主要对字符串类型建立基于分词的索引，主要是基于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('唐僧');#利用全文检索索引快速查询记录

---

总结

以上