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)] ) );
1
2
3
4
5
6
7

使用方法

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');		//查询
1
2
3
4
5

 
 
2. 已存在的表上创建索引
方法一:
在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：

CREATE FULLTEXT INDEX indexname
    ON tablename( propname1 [ ( length ) ] ); 
1
2

在上述语句中，关键字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');	//查询
1
2
3
4
5
6

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

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

使用方法

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');	//查询							//查询
1
2
3
4
5
6

 
 
 
 

MySQL8 中文分词支持

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

[mysqld]			//找到这一行
ngram_token_size=2	//复制这一行, 设置2个字为一组查找词
1
2

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('狂徒');	//只查询两个字
1
2
3
4