mysql使用全文检索实现模糊查询再也不使用like+%_mysql全文索引 模糊查询

前言

mysql在模糊查询数据时使用 “%xx” 会导致索引失效，MySQL 5.6 开始，InnoDB 开始支持全文检索（使用B+tree索引完成模糊查询）

全文检索

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;
1
2

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

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);
1

使用全文索引

其语法如下：

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
}
1
2
3
4
5
6
7
8

全文搜索使用 MATCH() AGAINST()语法进行，其中，MATCH()采用逗号分隔的列表，命名要搜索的列。AGAINST()接收一个要搜索的字符串，以及一个要执行的搜索类型的可选修饰符。

自然搜索

SELECT
    count(*) AS count 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL' );
1
2
3
4
5
6

上述语句，查询 title,body 列中包含 ‘MySQL’ 关键字的行数量。上述语句还可以这样写：

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

上述两种语句虽然得到的结果是一样的，但从内部运行来看，第二句SQL的执行速度更快些，因为第一句SQL（基于where索引查询的方式）还需要进行相关性的排序统计，而第二种方式是不需要的。

还可以通过SQL语句查询相关性：

SELECT
    *,
    MATCH ( title, body ) against ( 'MySQL' ) AS Relevance 
FROM
    fts_articles;
1
2
3
4
5

布尔搜索

例如，下面的语句要求查询有字符串"Pease"但没有"hot"的文档，其中+和-分别表示单词必须存在，或者一定不存在。

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

删除全文索引

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

DROP INDEX full_idx_name ON db_name.table_name;
1

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

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;
1