MySQL 全文检索(Full-Text Search)
全文检索FTS不同于模糊查询like，它可以匹配局部的查询条件，即把原查询条件做下分词再去查询。
比如查询条件是：food fruit，全文检索可以做到返回 包含 food fruit，food, fruit, fruit food的结果集。
不仅如此，MySQL通过Boolean FTS还可以做到查询结果包含 food 但不包含 fruit。
MySQL全文检索概述
- 目前MySQL支持
FULLTEXTindex的存储引擎是InnoDB和MyISAM； FULLTEXTindex的column列类型只支持：CHAR, VARCHAR, or TEXT;FULLTEXTindex 只有在查询语句使用MATCH() AGAINST()时候才会生效；
创建 Full-Text Search
MySQL FTS需要创建 FULLTEXT 类型的 index，index要包含所有想要全文检索的列。
创建FTS语法：
CREATE TABLE table_name(
column_list,
...,
FULLTEXT (column1,column2,..)
);
ALTER TABLE table_name
ADD FULLTEXT(column_name1, column_name2,…)
CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,...)
删除FTS语法：
ALTER TABLE table_name
DROP INDEX index_name;
创建full-text index 示例:
create fulltext index full_idx_name_author_publisher
on book(name, author, publisher);
MySQL默认的 full-text parser 根据单词间的空格或空白符来分割单词的；但这对于表意语言如中文、韩语和日本语就没作用了。
为了解决这个问题，MySQL提供了ngram full-text parser，从5.76开始，MySQL把ngram full-text parser作为server内置插件。
create fulltext index full_idx_name_author_publisher
on book(name, author, publisher) WITH PARSER NGRAM;
注意： 实际使用 ngram parser 的时候，需要关注 stopwords，最好自定义相关语言的 stop word 列表。
MySQL FTS检索
MySQL全文检索类型：
- natural language search type
- boolean search type
- query expansion search type
Natural-Language检索
select * from book
where
match(name, author, publisher) -- 想要匹配的column列表，需要和创建index时候的column列表一致：列名和个数要一样（顺序可以不一样），不然sql会报错。
against('北京日报') -- 关键字
或
select * from book
where
match(publisher,name, author)
against('北京日报' IN NATURAL LANGUAGE MODE);
- 如果查询的是中文，创建index的时候要指定用
ngram parser； - match的column列表需要和创建index时候的column列表一致：列名和个数要一样（顺序可以不一样），不然sql会报错；
IN NATURAL LANGUAGE MODE是可选项，因为它是默认检索类型。
Boolean FTS
select * from book
where
match(publisher,name, author)
against('北京日报 -青鸟' IN BOOLEAN MODE);
Boolean操作符:
+: Include, the word must be present.–: Exclude, the word must not be present.>: Include, and increase ranking value.<: Include, and decrease the ranking value.(): Group words into subexpressions (allowing them to be included, excluded, ranked, and so forth as a group).~: Negate a word’s ranking value.*: Wildcard at the end of the word.“”: Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).
Query Expansion
select * from book
where
match(publisher,name, author)
against('老舍' WITH QUERY EXPANSION);
- 先查出匹配关键字的结果集
- 从结果集里获取相关词汇
- 用相关词汇做为条件再次查询
注意：可能会查出很多不相关的结果。
