本文介绍如何通过全文索引函数match() against()
、match() fuzzy()
、match() phrase()
进行全文检索以及如何高亮全文检索关键词。
前提条件
已创建全文索引。如何创建,请参见创建全文索引。
示例数据
本文所有示例均基于示例表tbl_fulltext_demo。每个全文索引都使用了不同的分词器,示例表的建表语句和数据写入语句如下。
CREATE TABLE `tbl_fulltext_demo` (
`id` int,
`content` varchar,
`content_alinlp` varchar,
`content_ik` varchar,
`content_standard` varchar,
`content_ngram` varchar,
FULLTEXT INDEX fidx_c(`content`), // 使用默认分词器
FULLTEXT INDEX fidx_alinlp(`content_alinlp`) WITH ANALYZER alinlp,
FULLTEXT INDEX fidx_ik(`content_ik`) WITH ANALYZER ik,
FULLTEXT INDEX fidx_standard(`content_standard`) WITH ANALYZER standard,
FULLTEXT INDEX fidx_ngram(`content_ngram`) WITH ANALYZER ngram,
PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(id);
INSERT INTO tbl_fulltext_demo(id, content, content_alinlp, content_ik, content_standard, content_ngram)
VALUES(1, '客户需要更好的产品和服务', '客户需要更好的产品和服务', '客户需要更好的产品和服务', '客户需要更好的产品和服务', '客户需要更好的产品和服务')
,(2, '武汉市长江大桥','武汉市长江大桥','武汉市长江大桥','武汉市长江大桥', '武汉市长江大桥')
,(3, 'Hangzhou, Zhejiang Province','Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province', 'Hangzhou, Zhejiang Province')
,(4, '产品的用户价值和商业价值','产品的用户价值和商业价值', '产品的用户价值和商业价值', '产品的用户价值和商业价值', '产品的用户价值和商业价值');
注意事项
全文索引函数支持使用特殊字符
+-&|!(){}[]^"~*?:\/
,但需要对特殊字符进行转义处理,转义符为\\
。例如,需要检索包含
春天/美景
的数据,错误写法为match(content) against('春天 / 美景' )
,正确写法为match(content) against('春天 \\/ 美景')
。全文索引函数不支持
=
、!=
、between
、is null
、is not null
以及like
等操作符。
match() against()
match() against()
支持词条匹配和精确匹配,对指定列查找与关键词匹配的内容。
语法
SELECT * FROM `table_name` WHERE match (column_name[ , ... ]) against('term')
参数说明
table_name:检索的数据表。
column_name:检索的数据列。如果对多个列进行检索,列名之间用英文逗号(,)隔开。
term:检索的关键词。关键词查询时支持如下逻辑操作符:
AND:检索出同时匹配所有关键词的内容。
OR:检索出匹配其中一个关键词的内容。
NOT:检索出与逻辑操作符左侧关键词匹配,但与右侧关键词不匹配的内容。
说明逻辑操作符不区分大小写。
示例一:单列查询
SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH (`content`) AGAINST ('产品服务');
返回结果:
+------+--------------------------------------+
| id | content |
+------+--------------------------------------+
| 4 |产品的用户价值和商业价值 |
+------+--------------------------------------+
| 1 | 客户需要更好的产品和服务 |
+------+--------------------------------------+
示例二:多列查询
在匹配多列时,不需要创建多列联合索引,只要多列条件中的每一列具有全文索引,即可进行多列联合全文索引查询。
SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH (content, content_alinlp) AGAINST ('服务');
或
SELECT id, content, content_alinlp
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('服务')
OR MATCH (content_alinlp) AGAINST ('服务');
以上两个查询语句的作用相同。
返回结果:
+------+-----------------------------+------------------------------+
| id | content | content_alinlp |
+------+-----------------------------+------------------------------+
| 1 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 |
+------+-----------------------------+------------------------------+
示例三:布尔查询
使用AND逻辑操作符,检索匹配所有关键词的内容。
SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品 AND 服务');
返回结果:
+------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | id | content | content_alinlp | content_ik | content_standard | content_ngram | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 1 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+
使用OR操作符,检索匹配任意关键词的内容。
SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品 OR 服务');
返回结果:
+------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | id | content | content_alinlp | content_ik | content_standard | content_ngram | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 4 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 |产品的用户价值和商业价值 | 产品的用户价值和商业价值 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 1 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+
使用NOT操作符,检索只匹配逻辑操作符左侧关键词的内容。
SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品 NOT 服务');
返回结果:
+------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | id | content | content_alinlp | content_ik | content_standard | content_ngram | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 4 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+
使用NOT操作符,检索出与逻辑操作符右侧关键词不匹配的内容。
SELECT * FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('*:* NOT 服务');
重要*:*
表示所有JSON文档,仅在逻辑操作符的左侧使用,并且只能放在表达式的起始位置。返回结果:
+------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | id | content | content_alinlp | content_ik | content_standard | content_ngram | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 2 | 武汉市长江大桥 | 武汉市长江大桥 | 武汉市长江大桥 | 武汉市长江大桥 | 武汉市长江大桥 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 4 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+ | 3 | Hangzhou, Zhejiang Province | Hangzhou, Zhejiang Province | Hangzhou, Zhejiang Province | Hangzhou, Zhejiang Province | Hangzhou, Zhejiang Province | +------+--------------------------------+--------------------------------+-----------------------------+--------------------------------------+--------------------------------------+
使用括号构造复杂的布尔查询:
SELECT id, content_alinlp FROM `tbl_fulltext_demo` WHERE MATCH (content_alinlp) AGAINST ('(武汉 OR 杭州) AND (大桥 OR 西湖)');
返回结果:
+------+-----------------------+ | id | content_alinlp | +------+-----------------------+ | 2 | 武汉市长江大桥 | +------+-----------------------+
示例四:结果集过滤
全文检索会返回所有跟关键词近似的结果。在某些数据量很大的场景中,查询关键词的结果集也很大,但是只需要取出近似度较高的结果,因此AnalyticDB for MySQL提供了结果集过滤的功能。
以下示例过滤掉90%的低近似度结果,只返回近似度排在前10%的结果。
SELECT id, content
FROM `tbl_fulltext_demo`
WHERE MATCH (content) AGAINST ('产品服务') > 0.9;
返回结果:
+------+--------------------------------------+
| id | content |
+------+--------------------------------------+
| 4 | 产品的用户价值和商业价值 |
+------+--------------------------------------+
| 1 | 客户需要更好的产品和服务 |
+------+--------------------------------------+
示例五:近似度分数与排序
AnalyticDB for MySQL支持获取结果集的近似度分数,以及按照近似度分数排序的功能。
查询近似度分数。
SELECT id, content, MATCH (content) AGAINST ('产品服务') AS score FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品服务') > 0.9;
说明WHERE MATCH (content) AGAINST ('产品服务') > 0.9
表示过滤掉90%的低近似度结果,只返回近似度排在前10%的结果。默认情况下,返回的结果集不会按照近似度分数排序:
+------+--------------------------------------+----------------------+ | id | content | score | +------+--------------------------------------+----------------------+ | 4 | 产品的用户价值和商业价值 | 0.13076457381248474 | +------+--------------------------------------+----------------------+ | 1 | 客户需要更好的产品和服务 | 1.1090354919433594 | +------+--------------------------------------+----------------------+
SELECT投影中的match(content) against ('武汉') 不需要和WHERE查询条件中的match(content) against ('产品服务') 语句保持一致。以下语句可以查询match(content) against ('武汉') 的近似度分数。
SELECT *, MATCH (content) AGAINST ('武汉') AS score FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品服务') > 0.9 ORDER BY score DESC;
返回结果:
+------+--------------------------------+--------------------------------+-----------------------------+-------------------------------------+--------------------------------------+-------------+ | id | content | content_alinlp | content_ik | content_standard | content_ngram |score | +------+--------------------------------+--------------------------------+-----------------------------+-------------------------------------+--------------------------------------+-------------+ | 4 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 | 产品的用户价值和商业价值 |0.0 | +------+--------------------------------+--------------------------------+-----------------------------+-------------------------------------+--------------------------------------+-------------+ | 1 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 | 客户需要更好的产品和服务 |客户需要更好的产品和服务 | 客户需要更好的产品和服务 |0.0 | +------+--------------------------------+--------------------------------+-----------------------------+-------------------------------------+--------------------------------------+-------------+
按近似度排序。
使用ORDER BY语句,并按近似度降序排序。
SELECT id, content, MATCH (content) AGAINST ('产品服务') AS score FROM `tbl_fulltext_demo` WHERE MATCH (content) AGAINST ('产品服务') > 0.9 ORDER BY score DESC;
返回结果:
+------+--------------------------------------+---------------------+ | id | content | score | +------+--------------------------------------+---------------------+ | 1 | 客户需要更好的产品和服务 | 1.1090354919433594 | +------+--------------------------------------+---------------------+ | 4 | 产品的用户价值和商业价值 | 0.13076457381248474 | +------+--------------------------------------+---------------------+
示例六:词条匹配
默认情况下,全文检索会先对词语进行分词后,再进行检索。如果关键词不添加双引号,查询结果只需要与任意一个关键词匹配,就可以返回结果。
Alinlp分词器将“产品和服务”分词为“产品”、“和”与“服务”,将“产品服务”分词为“产品”与“服务”。
SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('产品服务') AS score
FROM `tbl_fulltext_demo`
WHERE MATCH (content_alinlp) AGAINST ('产品服务') > 0.9
ORDER BY score DESC;
只需要匹配“产品服务”中的任意一个关键词,就能得到返回结果:
+------+--------------------------------------+---------------------+
| id | content_alinlp | score |
+------+--------------------------------------+---------------------+
| 1 | 客户需要更好的产品和服务 | 0.5953410863876343 |
+------+--------------------------------------+---------------------+
| 4 | 产品的用户价值和商业价值 | 0.13076457381248474 |
+------+--------------------------------------+---------------------+
示例七:精确匹配
如果关键词添加双引号,分词器不会对双引号中的关键词进行分词,指定列中的数据需要完全匹配双引号中的关键词,才能返回结果。
检索与关键词产品服务精确匹配的内容。
SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('"产品服务"') AS score FROM `tbl_fulltext_demo` WHERE MATCH (content_alinlp) AGAINST ('"产品服务"') > 0.9 ORDER BY score DESC;
精确匹配关键词“产品服务”的返回结果为空:
Empty set
检索与关键词产品和服务精确匹配的内容。
SELECT id, content_alinlp, MATCH (content_alinlp) AGAINST ('"产品和服务"') AS score FROM `tbl_fulltext_demo` WHERE MATCH (content_alinlp) AGAINST ('"产品和服务"') > 0.9 ORDER BY score DESC;
返回结果:
+------+--------------------------------------+--------------------+ | id | content_alinlp | score | +------+--------------------------------------+--------------------+ | 1 | 客户需要更好的产品和服务 | 0.8930116891860962 | +------+--------------------------------------+--------------------+
match() fuzzy()
match()fuzzy()
支持模糊匹配查询,基于Levenshtein Edit Distance(莱温斯坦编辑距离),对检索的文本进行模糊匹配。在一些输入出现错误的场景下,使用模糊匹配查询功能可以在一定程度上查询出与关键词相近的内容。
语法
SELECT * FROM `table_name` WHERE match (`column_name`) fuzzy('term') [max_edits(n)];
参数说明
table_name:检索的数据表。
column_name:检索的数据列。
term:检索的关键词。
max_edits(n):最大编辑距离(可选),也就是字符串A变成字符串B所需要的最小变化(插入,删除或者替换)的次数。默认值为2,取值范围是0~2之间的整数。例如:
windos
进行1次变化,插入字符w,变成windows
,所以windos
和windows
最大编辑距离为1。
示例一:相近查询
SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou');
返回结果:
+------+-----------------------------+
| id | content |
+------+-----------------------------+
| 3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+
示例二:编辑距离为1的相近查询
SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('hangzou') max_edits(1);
返回结果:
+------+-----------------------------+
| id | content |
+------+-----------------------------+
| 3 | Hangzhou, Zhejiang Province |
+------+-----------------------------+
示例三:编辑距离为2的相近查询
SELECT id, content
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('武汉长') max_edits(2);
返回结果:
+------+-----------------------+
| id | content |
+------+-----------------------+
| 2 | 武汉市长江大桥 |
+------+-----------------------+
match() phrase()
match() phrase()
支持短语查询,对指定列查找匹配多个关键词的内容。在多个关键词都匹配的情况下,您可以将slop参数作为判断是否满足短语查询的另一个依据。
语法
SELECT * FROM `table_name` WHERE match (`column_name`) phrase('term1 term2') [slop(n)]
参数说明
table_name:检索的数据表。
column_name:检索的数据列。
term1 term2:检索的关键词列表,关键词之间用空格隔开,关键词的顺序将影响匹配的结果。
slop(n):最大移动步长(可选)。使用分词器对文本分词得到词条列表后,按照顺序对每个词条从0开始依次递增标记序号位置,通过左右移动词条使其与phrase参数指定的关键词列表匹配。默认值是0,取值范围是0~6之间的整数。例如,对“商品和服务”使用Standard分词器分词,结果是“商”、“品”、“和”、“服”和“务”。
文本
商
品
和
服
务
序号位置
0
1
2
3
4
phrase('商 和'):slop距离是1,表示“商”往右移动1个步长或“和”往左移动1个步长。
phrase('商 服'):slop距离是2,表示“商”往右移动2个步长或“服”往左移动2个步长。
示例一:英文短语查询
AnalyticDB for MySQL内置的Standard分词器,在默认配置的情况下对“Hangzhou, Zhejiang Province”分词为“Hangzhou”、“Zhejiang”和“Province”。
检索与关键词zhejiang hangzhou 匹配的内容。
SELECT id, content FROM tbl_fulltext_demo WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou');
slop参数默认设置为0,检索与关键词zhejiang hangzhou 匹配的内容,返回结果为空:
Empty set
检索移动1个步长后与关键词hangzhou Province匹配的内容。
SELECT id, content FROM tbl_fulltext_demo WHERE MATCH(content_standard) PHRASE('hangzhou Province') slop(1);
返回结果:
+------+-----------------------------+ | id | content | +------+-----------------------------+ | 3 | Hangzhou, Zhejiang Province | +------+-----------------------------+
检索移动2个步长后与关键词zhejiang hangzhou匹配的内容。
SELECT id, content FROM tbl_fulltext_demo WHERE MATCH(content_standard) PHRASE('zhejiang hangzhou') slop(2);
返回结果:
+------+-----------------------------+ | id | content | +------+-----------------------------+ | 3 | Hangzhou, Zhejiang Province | +------+-----------------------------+
示例二:中文短语查询
在match() phrase()
中文短语匹配场景下,使用内置的Alinlp分词器和Ngram分词器分词可能会导致中文匹配查询数据失败。因此推荐您使用Standard Analyzer分词器和Ik Analyzer分词器。
检索经过移动1个步长后与关键词产品 服务匹配的内容。
SELECT id, content FROM tbl_fulltext_demo WHERE MATCH(content_ik) PHRASE('产品 服务') slop(1);
返回结果:
+------+--------------------------------------+ | id | content | +------+--------------------------------------+ | 1 | 客户需要更好的产品和服务 | +------+--------------------------------------+
检索经过移动2个步长后与关键词产 服匹配的内容。
SELECT id, content FROM tbl_fulltext_demo WHERE MATCH(content_standard) PHRASE('产 服') slop(2);
返回结果:
+------+--------------------------------------+ | id | content | +------+--------------------------------------+ | 1 | 客户需要更好的产品和服务 | +------+--------------------------------------+
高亮支持
使用函数高亮
AnalyticDB for MySQL支持使用fulltext_highlight(`column_name`)函数对全文索引列中的关键词进行高亮。
示例一:MATCH() AGAINST()关键词高亮
对单列查询进行高亮。
SELECT MATCH (content_alinlp) AGAINST ('武汉长江') AS score, fulltext_highlight(content_alinlp) FROM tbl_fulltext_demo WHERE MATCH (content_alinlp) AGAINST ('武汉长江') > 0.9 ORDER BY score DESC LIMIT 3;
返回结果:
+--------------------+-----------------------------------------+ | score | fulltext_highlight(content_alinlp) | +--------------------+-----------------------------------------+ | 0.2615291476249695 | <em>武汉</em>市<em>长江</em>大桥 | +--------------------+-----------------------------------------+
对多列查询进行高亮。
SELECT MATCH (content_alinlp) AGAINST ('武汉长江') AS score, fulltext_highlight(content_alinlp) FROM tbl_fulltext_demo WHERE MATCH (content_alinlp) AGAINST ('武汉长江') > 0.9 AND MATCH (content_alinlp) AGAINST ('大桥') > 0.9 ORDER BY score DESC LIMIT 3;
返回结果:
+--------------------+--------------------------------------------------+ | score | fulltext_highlight(content_alinlp) | +--------------------+--------------------------------------------------+ | 0.2615291476249695 | <em>武汉</em>市<em>长江</em><em>大桥</em> | +--------------------+--------------------------------------------------+
示例二:MATCH() FUZZY()关键词高亮
SELECT id, MATCH(content_standard) FUZZY('武汉长') as score, fulltext_highlight(content_standard)
FROM tbl_fulltext_demo
WHERE MATCH(content_standard) FUZZY('武汉长');
返回结果:
+------+-------+--------------------------------------------------+
| id | score | fulltext_highlight(content_standard) |
+------+-------+--------------------------------------------------+
| 2 | 0.0 | <em>武</em><em>汉</em>市<em>长</em>江大桥 |
+------+-------+--------------------------------------------------+
示例三:MATCH() PHRASE()关键词高亮
SELECT id, MATCH(content_ik) PHRASE('产品 服务') slop(1) as score, fulltext_highlight(content_ik)
FROM tbl_fulltext_demo
WHERE MATCH(content_ik) PHRASE('产品 服务') slop(1);
返回结果:
+------+--------------------+--------------------------------------------------------+
| id | score | fulltext_highlight(content_ik) |
+------+--------------------+--------------------------------------------------------+
| 1 | 0.6931471824645996 | 客户需要更好的<em>产品</em>和<em>服务</em> |
+------+--------------------+--------------------------------------------------------+
使用hint自定义高亮
AnalyticDB for MySQL全文检索的高亮默认使用<em>和</em>标签,您也可以使用hint设定fulltext_highlight_pre_tag和fulltext_highlight_post_tag的值定义全文高亮的左右标签。
示例一:MATCH() AGAINST()自定义高亮
/*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) AGAINST ('武汉长江') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) AGAINST ('武汉长江') > 0.9
ORDER BY score DESC LIMIT 3;
返回结果:
+--------------------+-------------------------------------------------+
| score | fulltext_highlight(content_alinlp) |
+--------------------+-------------------------------------------------+
| 0.2615291476249695 | <span>武汉</span>市<span>长江</span>大桥 |
+--------------------+-------------------------------------------------+
示例二:MATCH() FUZZY()自定义高亮
/*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) FUZZY ('武汉长江') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) FUZZY ('武汉长江') > 0.9
ORDER BY score DESC LIMIT 3;
返回结果:
+--------------------+-------------------------------------------------+
| score | fulltext_highlight(content_alinlp) |
+--------------------+-------------------------------------------------+
| 0.0 | <span>武汉</span>市<span>长江</span>大桥 |
+--------------------+-------------------------------------------------+
示例三:MATCH() PHRASE()自定义高亮
/*+ fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (content_alinlp) PHRASE ('武汉') AS score, fulltext_highlight(content_alinlp)
FROM tbl_fulltext_demo
WHERE MATCH (content_alinlp) PHRASE ('武汉') > 0.9
ORDER BY score DESC LIMIT 3;
返回结果:
+--------------------+---------------------------------------------------------------+
| score | fulltext_highlight(content_alinlp) |
+--------------------+---------------------------------------------------------------+
| 0.8930116891860962 | 客户需要更好的<span>产品</span><span>和</span><span>服务</span> |
+--------------------+---------------------------------------------------------------+