MySQL|MySQL - 全文索引

全文索引(英文查找)

  • 全文索引主要对字符串类型建立基于分词的索引,主要是基于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 table class(id int, name varchar(64), comment varchar(64), FULLTEXT INDEX comment_index(comment)); //创建class表, 指定comment为全文索引 mysql> show create table class; //查询表结构 mysql> insert into class values(1, '张三', 'I''m Zhang san'); //插入数据1 mysql> insert into class values(2, '李四', 'I''m Li si'); //插入数据2 mysql> select * from class where match(comment) AGAINST('Li'); //查询

【MySQL|MySQL - 全文索引】

2. 已存在的表上创建索引
方法一:
在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外,还可以通过SQL语句CREATE FULLTEXT INDEX来实现,其语法形式如下:
CREATE FULLTEXT INDEX indexname ON tablename( propname1 [ ( length ) ] );

在上述语句中,关键字CREATE FULLTEXT INDEX表示用来创建全文索引。
如下例表已存在,可通过CREATE语句创建全文索引:
mysql> create table class(id int, name varchar(64), comment varchar(64)); //创建class表 mysql> create FULLTEXT INDEX comment_index ON class(comment); //追加全文索引 mysql> show create table class; //查询表结构 mysql> insert into class values(1, '张三', 'I''m Zhang san'); //插入数据1 mysql> insert into class values(2, '李四', 'I''m Li si'); //插入数据2 mysql> select * from class where match(comment) AGAINST('Zhang'); //查询

方法二:
除了上述两种方式来创建全文索引外,在MySQL中创建全文索引还可以通过SQL语句ALTER来实现,其语法形式如下:
ALTER TABLE tablename ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

使用方法
mysql> create table class(id int, name varchar(64), comment varchar(64)); //创建class表 mysql> alter table class add FULLTEXT INDEX comment_index(comment); //追加全文索引 mysql> show create table class; //查询表结构 mysql> insert into class values(1, '张三', 'I''m Zhang san'); //插入数据1 mysql> insert into class values(2, '李四', 'I''m Li si'); //插入数据2 mysql> select * from class where match(comment) AGAINST('Zhang'); //查询//查询





MySQL8 中文分词支持
配置文件my.ini(Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0) 中增加如下配置项,同时重启MySQL80 服务:
[mysqld]//找到这一行 ngram_token_size=2 //复制这一行, 设置2个字为一组查找词

MySQL|MySQL - 全文索引
文章图片

mysql> create table class(id int, name varchar(64), FULLTEXT INDEX name_index(name) with parser ngram); //创建class表 name列设置为全文索引, 支持中文分词索引 mysql> insert into class values(1, '法外狂徒张三'); //插入数据1 mysql> insert into class values(2, '尼古拉斯赵四'); //插入数据2 mysql> select * from class where match(name) against('狂徒'); //只查询两个字

MySQL|MySQL - 全文索引
文章图片

    推荐阅读