MySQL模糊查询不用like+%，用match

创建全文索引

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.在已创建的表上创建全文索引：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

2.具体普通统计查询

第一种：

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

第二种：

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

3.数据查询，相关性

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

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

word 是否在文档中出现

word 在文档中出现的次数

word 在索引列中的数量

多少个文档包含该 word

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

" ：表示短语

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 模式查询(自动关联 )

SELECT
* 
FROM
`fts_articles` 
WHERE
MATCH(title,body) AGAINST('database' WITH 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;