数据库学习----MySQL 全文索引

数据库学习----MySQL 全文索引

前言

我们在使用MySQL做模糊匹配的时候往往只能通过几个相连的关键字进行匹配，这种方式MySQL是不会走索引进行搜索的。MySQL 5.6及以上版本给我们提供了一种可以通过更多方式进行匹配的搜索方式（全文索引查询），并且是通过索引搜索数据。大大提高了查询的效率。

索引的建立方式及销毁方式

创建索引

• 无表的时候创建索引

CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200), 
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

• 有表的时候创建索引

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

删除索引

• 直接删除

DROP INDEX full_idx_name ON db_name.table_name;

• 使用alter table 的方式删除

DROP INDEX full_idx_name ON db_name.table_name;

使用全文索引查询数据

使用自然语言模式查询

MySQL默认的分词方法是所有非字母和数字的特殊符号都是分词符(如果希望对中文进行分词，则可以使用MySQL内置的ngram插件)，对于太短的查询则不会出现结果，默认是3个字符

-- 自然语言模式查询（默认）
select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 every man');

上面的SQL中match ( content ,tag ) AGAINST ('在哪里 every man'); 则是使用全文索引进行搜索

使用拓展模式查询

拓展模式是经过两次全文索引的结果，

第二次搜索使用第一次搜索的结果，让结果更精确。

-- 拓展模式查询
select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 every man' WITH QUERY expansion);

boolean 模式

• +：表示该 word 必须存在

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('+在哪里 -every man' IN BOOLEAN MODE);

• -：表示该 word 必须不存在
• (no operator)表示该 word 是可选的，但是如果出现，其相关性会更高

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 every man' IN BOOLEAN MODE);

• @distance表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在30字节内

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 "every man"@2' IN BOOLEAN MODE);
--	表示every，man 两个词之间的距离在2字节以内

• >：表示出现该单词时增加相关性（结果排序）
• <：表示出现该单词时降低相关性（结果排序）

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 >every <man' IN BOOLEAN MODE);

• ~：表示允许出现该单词，但出现时相关性为负(结果排序)

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 ~every man' IN BOOLEAN MODE);

• *：表示以该单词开头的单词，如 lik*,表示可以是 lik，like，likes

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('在哪里 every* man' IN BOOLEAN MODE);

• " ：表示短语

select
	*
from
	wenan_reptile_official wro
where
	match ( content ,
	tag ) AGAINST ('"every man"' IN BOOLEAN MODE);