mysql 长文本索引 mysql全文索引详解

转载

mob64ca140dc73b 2023-08-30 08:27:34

文章标签 mysql 长文本索引数据库 mysql 全文索引字段 文章分类 MySQL 数据库

全文索引详解

1.全文索引

全文索引主要对字符串类型建立基于分词的索引，主要是基于CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符。

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

`创建表时定义索引`

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('我是一个兵');#利用全文检索索引快速查询记录

`已存在的表上创建索引`

方法一: 执行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;  #查看表定义

方法二: 执行ALTER TABLE 语句

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

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

`使用场景`

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

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('唐僧');#利用全文检索索引快速查询记录