全文检索方式最佳实践_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

AnalyticDB MySQL版2.0除了支持基本的全文检索方式，还支持以下方式：

按近似度排序
结果集过滤
多列查询
短语查询、精确匹配
逻辑操作符AND OR NOT
结构化、非结构化联合检索
高级SQL语法：结构化、非结构化GROUP BY, JOIN, UNION

按近似度排序

SQL语义规定在不带ORDER BY的情况下不会按照近似度排序。

如果需要将结果按照近似度从高到低排序，则加上ORDER BY DESC，示例如下。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(title) against ('浙江省杭州市')
ORDER BY match(title) against ('浙江省杭州市') DESC
LIMIT 100;

结果集过滤

全文检索会召回所有跟关键词近似的结果。在某些数据量很大的场景中，命中关键词的结果集可能也很大，但是往往只需要取出近似度较高的部分结果。

分析型数据库MySQL版提供了结果集过滤的功能，如下例子中where match() against() > 0.9 表示只取近似度排在前10%的结果，过滤掉了90%的低近似度结果。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(title) against ('浙江省杭州市') > 0.9
ORDER BY match(title) against ('浙江省杭州市') DESC
LIMIT 100;

多列查询

在title，body两列中同时检索”浙江省杭州市”关键字，返回近似度排在前10%的结果行，并且将结果行按照近似度逆序排列。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(title,body) against ('浙江省杭州市') > 0.9
ORDER BY match(title, body) against ('浙江省杭州市') DESC
LIMIT 100;

短语查询、精确匹配

默认情况下，分析型数据库MySQL版全文检索会对词语进行分词后，再进行检索。比如检索”中华人民共和国”会被分词为”中华”、”人民”、”共和国”三个词分别检索。但是某些特殊情况下，可能需要完全精确的短语匹配。比如只希望返回完全精确匹配”中华人民共和国”的结果，而不希望返回分词后的检索结果，则使用短语查询语法。语法格式为：在检索关键词上加双引号。

基本查询：分词后再检索。注意关键词的写法：没有双引号。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(body) against ('中华人民共和国') > 0.9
ORDER BY match(body) against ('中华人民共和国') DESC
LIMIT 100;

短语查询：精确匹配，不会做分词处理。注意关键词的写法，有双引号。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(body) against ('"中华人民共和国"') > 0.9
ORDER BY match(body) against ('"中华人民共和国"') DESC
LIMIT 100;

逻辑操作符 AND OR NOT

分析型数据库MySQL版支持利用逻辑操作符对结果进行控制。目前支持的逻辑操作符包括：AND OR NOT。

其中AND表示要求操作符两边的关键词都必须出现。OR表示操作符两边的关键词出现一个即可。NOT表示右侧的关键词不能出现。

语法为：<word1> AND/OR/NOT <word2>

重要

AND/OR/NOT必须大写；
AND/OR/NOT两边必须有空格。

要求 “浙江省”和”杭州市”都必须出现。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(body) against ('浙江省 AND 杭州市') > 0.9
ORDER BY match(body) against ('浙江省 AND 杭州市') DESC
LIMIT 100;

要求 “浙江省”，”杭州市”出现一个即可。效果等同于如 “浙江省杭州市”, “浙江省杭州市”。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(body) against ('浙江省 OR 杭州市') > 0.9
ORDER BY match(body) against ('浙江省 OR 杭州市') DESC
LIMIT 100;

要求 “浙江省”一定不能出现。

SELECT id, title, author, body 
FROM articles_test 
WHERE match(body) against ('杭州市 NOT 浙江省') > 0.9
ORDER BY match(body) against ('杭州市 NOT 浙江省') DESC
LIMIT 100;

结构化、非结构化联合检索

分析型数据库MySQL版支持对结构化列、非结构化列进行联合条件检索。

要求查询create_time在20180201-20180930之间的、body命中“浙江省杭州市”、且comment不为空的数据行。

SELECT id, title, author, body 
FROM articles_test 
WHERE create_time > '2018-02-01 00:00:00' 
        AND create_time < '2018-09-30 00:00:00'
        AND match(body) against ('浙江省杭州市') > 0.9
        AND comment is not null
ORDER BY match(body) against ('浙江省杭州市') DESC
LIMIT 100;

要求在body中检索“浙江省杭州市”, 且在author中检索“张三”。

SELECT id, author, body 
FROM articles_test 
WHERE match(body) against ('浙江省杭州市') > 0.9
        AND match(author) against('张三') > 0.9
ORDER BY match(body) against ('浙江省杭州市') DESC, match(author) against('张三') DESC
LIMIT 100;

高级SQL语法：结构化、非结构化融合GROUP BY, JOIN, UNION

分组

SELECT author, body, max(match(body) against ('浙江省杭州市')) as score
FROM articles_test 
WHERE match(body) against ('浙江省杭州市') > 0.9
GROUP BY author, body
ORDER BY score DESC
LIMIT 100;

表连接：在文章表articles_test中检索“浙江省杭州市”，在作者信息表author_info中检索“张三”，并且利用ID列将两表进行join。

CREATE TABLE author_info (
  id bigint COMMENT '',
  name varchar COMMENT '',
  addr varchar COMMENT '',
  FULLTEXT INDEX name_fulltext (name),
  FULLTEXT INDEX addr_fulltext (addr),
  PRIMARY KEY (id)
)
PARTITION BY HASH KEY(id)
PARTITION NUM 8
CLUSTERED BY (id)
TABLEGROUP test_group
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

insert into author_info(id, name, addr) values(0, '张三', '湖南省张家界市武陵源区');

SELECT articles_test.id, articles_test.title, author_info.name, author_info.addr
FROM articles_test 
    JOIN author_info
    ON articles_test.id = author_info.id 
WHERE
    match (articles_test.body) against ('浙江省杭州市')
    AND match(author_info.name) against ('张三')
    AND articles_test.create_time between '2018-01-01 00:00:00' and '2018-09-30 00:00:00';

合并

SELECT id, title 
FROM articles_test 
WHERE
    match (articles_test.body) against ('浙江省杭州市')
UNION ALL
SELECT id, addr 
FROM author_info
WHERE
    match(author_info.name) against ('张三');