关于java:MySQL模糊查询再也不用-like-了

作者：沸羊羊 \
起源：juejin.cn/post/6989871497040887845

前言

咱们都晓得 InnoDB 在含糊查问数据时应用 “%xx” 会导致索引生效，但有时需要就是如此，相似这样的需要还有很多，例如，搜索引擎须要根基用户数据的关键字进行全文查找，电子商务网站须要依据用户的查问条件，在可能须要在商品的具体介绍中进行查找，这些都不是 B + 树索引能很好实现的工作。

通过数值比拟，范畴过滤等就能够实现绝大多数咱们须要的查问了。然而，如果心愿通过关键字的匹配来进行查问过滤，那么就须要基于类似度的查问，而不是原来的准确数值比拟，全文索引就是为这种场景设计的。

全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找进去的技术。它能够依据须要取得全文中无关章、节、段、句、词等信息，也能够进行各种统计和剖析。

在晚期的 MySQL 中，InnoDB 并不反对全文检索技术，从 MySQL 5.6 开始，InnoDB 开始反对全文检索。

倒排索引

全文检索通常应用倒排索引（inverted index）来实现，倒排索引同 B+Tree 一样，也是一种索引构造。它在辅助表中存储了单词与单词本身在一个或多个文档中所在位置之间的映射，这通常利用关联数组实现，领有两种表现形式：

• inverted file index：{单词，单词所在文档的 id}
• full inverted index：{单词，（单词所在文档的 id，再具体文档中的地位）}

上图为 inverted file index 关联数组，能够看到其中单词 ”code” 存在于文档 1,4 中，这样存储再进行全文查问就简略了，能够间接依据 Documents 失去蕴含查问关键字的文档；而 full inverted index 存储的是对，即（DocumentId,Position），因而其存储的倒排索引如下图，如关键字 ”code” 存在于文档 1 的第 6 个单词和文档 4 的第 8 个单词。

相比之下，full inverted index 占用了更多的空间，然而能更好的定位数据，并裁减一些其余搜寻个性。

全文检索

创立全文索引

1、创立表时创立全文索引语法如下：

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;

输出查问语句：

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';

上述六个索引表形成倒排索引，称为辅助索引表。当传入的文档被标记化时，单个词与地位信息和关联的 DOC_ID，依据单词的第一个字符的字符集排序权重，在六个索引表中对单词进行齐全排序和分区。

2、在已创立的表上创立全文索引语法如下：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

应用全文索引

MySQL 数据库反对全文检索的查问，全文索引只能在 InnoDB 或 MyISAM 的表上应用，并且只能用于创立 char,varchar,text 类型的列。

其语法如下：

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

全文搜寻应用 MATCH() AGAINST() 语法进行，其中，MATCH() 采纳逗号分隔的列表，命名要搜寻的列。AGAINST() 接管一个要搜寻的字符串，以及一个要执行的搜寻类型的可选修饰符。全文检索分为三种类型：自然语言搜寻、布尔搜寻、查问扩大搜寻，上面将对各种查问模式进行介绍。

Natural Language

自然语言搜寻将搜寻字符串解释为天然人类语言中的短语，MATCH() 默认采纳 Natural Language 模式，其示意查问带有指定关键字的文档。

接下来联合 demo 来更好的了解 Natural Language

SELECT
    count(*) AS count
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('MySQL');

上述语句，查问 title,body 列中蕴含 ‘MySQL’ 关键字的行数量。上述语句还能够这样写：

SELECT
    count(IF(MATCH ( title, body)
    against ('MySQL'), 1, NULL )) AS count
FROM
    `fts_articles`;

上述两种语句尽管失去的后果是一样的，但从外部运行来看，第二句 SQL 的执行速度更快些，因为第一句 SQL（基于 where 索引查问的形式）还须要进行相关性的排序统计，而第二种形式是不须要的。

还能够通过 SQL 语句查问相关性：

SELECT
    *,
    MATCH (title, body) against ('MySQL') AS Relevance
FROM
    fts_articles;

相关性的计算根据以下四个条件：

• word 是否在文档中呈现
• word 在文档中呈现的次数
• word 在索引列中的数量
• 多少个文档蕴含该 word

对于 InnoDB 存储引擎的全文检索，还须要思考以下的因素：

• 查问的 word 在 stopword 列中，疏忽该字符串的查问
• 查问的 word 的字符长度是否在区间 [innodb_ft_min_token_size,innodb_ft_max_token_size] 内

如果词在 stopword 中，则不对该词进行查问，如对 ‘for’ 这个词进行查问，后果如下所示：

SELECT
    *,
    MATCH (title, body) against ('for') AS Relevance
FROM
    fts_articles;

能够看到，’for’ 尽管在文档 2，4 中呈现，但因为其是 stopword , 故其相关性为 0

参数 innodb_ft_min_token_size 和 innodb_ft_max_token_size 管制 InnoDB 引擎查问字符的长度，当长度小于 innodb_ft_min_token_size 或者长度大于 innodb_ft_max_token_size 时，会疏忽该词的搜寻。在 InnoDB 引擎中，参数 innodb_ft_min_token_size 的默认值是 3，innodb_ft_max_token_size 的默认值是 84

Boolean

布尔搜寻应用非凡查询语言的规定来解释搜寻字符串，该字符串蕴含要搜寻的词，它还能够蕴含指定要求的运算符，例如匹配行中必须存在或不存在某个词，或者它的权重应高于或低于通常状况。

例如，上面的语句要求查问有字符串 ”Pease” 但没有 ”hot” 的文档，其中 + 和 - 别离示意单词必须存在，或者肯定不存在。

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Boolean 全文检索反对的类型包含：

• +：示意该 word 必须存在
• -：示意该 word 必须不存在
• (no operator) 示意该 word 是可选的，然而如果呈现，其相关性会更高
• @distance 示意查问的多个单词之间的间隔是否在 distance 之内，distance 的单位是字节，这种全文检索的查问也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE) 语句示意字符串 Pease 和 hot 之间的间隔需在 30 字节内
• >：示意呈现该单词时减少相关性
• <：示意呈现该单词时升高相关性
• ~：示意容许呈现该单词，但呈现时相关性为负
• *：示意以该单词结尾的单词，如 lik*, 示意能够是 lik，like，likes
• "：示意短语

上面是一些 demo，看看 Boolean Mode 是如何应用的。

demo1：+ –

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

上述语句，查问的是蕴含 ‘MySQL’ 但不蕴含 ‘YourSQL’ 的信息

demo2：no operator

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('MySQL IBM' IN BOOLEAN MODE);

上述语句，查问的 ‘MySQL IBM’ 没有 ‘+’，’-‘ 的标识，代表 word 是可选的，如果呈现，其相关性会更高

demo3：@

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('"DB2 IBM"@3' IN BOOLEAN MODE);

上述语句，代表 “DB2”，”IBM” 两个词之间的间隔在 3 字节之内

demo4：> <

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('+MySQL +(>database <DBMS)' IN BOOLEAN MODE );

上述语句，查问同时蕴含 ‘MySQL’，’database’，’DBMS’ 的行信息，但不蕴含 ’DBMS’ 的行的相关性高于蕴含 ’DBMS’ 的行。

demo5: ~

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('MySQL ~database' IN BOOLEAN MODE);

上述语句，查问蕴含 ‘MySQL’ 的行，但如果该行同时蕴含 ‘database’，则升高相关性。

demo6：*

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('My*' IN BOOLEAN MODE);

上述语句，查问关键字中蕴含 ’My’ 的行信息。

demo7：”

SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH (title, body) AGAINST ('"MySQL Security"' IN BOOLEAN MODE);

上述语句，查问蕴含确切短语 ‘MySQL Security’ 的行信息。

Query Expansion

查问扩大搜寻是对自然语言搜寻的批改，这种查问通常在查问的关键词太短，用户须要 implied knowledge（隐含常识）时进行，例如，对于单词 database 的查问，用户可能心愿查问的不仅仅是蕴含 database 的文档，可能还指那些蕴含 MySQL、Oracle、RDBMS 的单词，而这时能够应用 Query Expansion 模式来开启全文检索的 implied knowledge 通过在查问语句中增加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 能够开启 blind query expansion（又称为 automatic relevance feedback），该查问分为两个阶段。

• 第一阶段：依据搜寻的单词进行全文索引查问
• 第二阶段：依据第一阶段产生的分词再进行一次全文检索的查问

接着来看一个例子，看看 Query Expansion 是如何应用的。

-- 创立索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);
-- 应用 Natural Language 模式查问
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database');

应用 Query Expansion 前查问后果如下：

-- 当应用 Query Expansion 模式查问
SELECT
    *
FROM
    `fts_articles`
WHERE
    MATCH(title,body) AGAINST('database' WITH QUERY expansion);

应用 Query Expansion 后查问后果如下：

因为 Query Expansion 的全文检索可能带来许多非相关性的查问，因而在应用时，用户可能须要十分审慎。

删除全文索引

1、间接删除全文索引语法如下：

DROP INDEX full_idx_name ON db_name.table_name;

2、应用 alter table 删除全文索引语法如下：

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿 (2022 最新版)

2. 劲爆！Java 协程要来了。。。

3.Spring Boot 2.x 教程，太全了！

4. 别再写满屏的爆爆爆炸类了，试试装璜器模式，这才是优雅的形式！！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！