本文介绍通过全文检索进行查询的具体方法。

前提条件

已创建包含全文索引列的表,本文以创建fulltext_test表为例说明,包含两个全文索引:t_idxb_idx
CREATE TABLE fulltext_test (
    id int,
    title varchar,
    body varchar,
    FULLTEXT INDEX t_idx(title),
    FULLTEXT INDEX b_idx(body),
    PRIMARY KEY (id)
)
DISTRIBUTE BY HASH(id);

基本查询

SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('杭州');

参数说明:

  • match(column_name[, …]):在建表语句中创建了全文索引的列名,可以填写一个或多个列名。例如match(body)表示只在body这一列中进行检索,match(title, body)表示在titlebody两列中分别进行检索。
  • against('words'):进行检索的关键词,例如against(’浙江省杭州市’)表示要检索浙江省杭州市。

多列查询

SELECT *
FROM fulltext_test
WHERE MATCH (title, body) AGAINST ('杭州');
在匹配多列时,不需要创建多列联合索引,只要多列条件中的每一列具有全文索引,即可进行多列联合全文检索查询,等价于以下查询语句:
SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('杭州')
    OR MATCH (body) AGAINST ('杭州');

布尔查询(使用逻辑操作符)

查询时支持如下逻辑操作符,操作符不区分大小写。
  • AND:表示操作符两边的关键词都必须出现。
  • OR:表示操作符两边的关键词出现一个即可。
  • NOT:表示右侧的关键词不能出现。
查询语句:
SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('杭州 AND 浙江');

SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('杭州 OR 浙江');
可以使用括号构造更复杂的布尔查询:
SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('(杭州 OR 浙江) and (教育 or 医疗)');

结果集过滤

全文检索会返回所有跟关键词近似的结果。在某些数据量很大的场景中,命中关键词的结果集可能也很大,但是往往只需要取出近似度较高的部分结果,因此提供了结果集过滤的功能。

如下示例中where match() against() > 0.9表示只取近似度排在前10%的结果,过滤掉了90%的低近似度结果:
SELECT *
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9;

近似度分数查询/按近似度排序

支持获取结果集的近似度分数,以及按照近似度分数排序的功能。

获取近似度分数的查询语句:

SELECT *, MATCH (title) AGAINST ('浙江省杭州市') AS score
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9;

默认情况下,返回的结果集将不会按照近似度分数排序,如果需要将结果按照近似度从高到低排序,则需要加上ORDER BY DESC。

按照近似度从高到低排序的查询语句:

SELECT *, MATCH (title) AGAINST ('浙江省杭州市') AS score
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
ORDER BY score DESC;
投影中的match(title) against ('浙江省杭州市')不必和后续查询中的match against语句保持一致,例如,可以通过以下的语句获取match(body) against ('中国')的近似度分数:
SELECT *, MATCH (body) AGAINST ('中国') AS score
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
ORDER BY score DESC;

短语查询或精确匹配

默认情况下,全文检索会对词语进行分词后,再进行检索。比如中华人民共和国被分词为中华人民共和国三个词分别检索。在某些特殊情况下,可能需要完全精确的短语匹配,比如只想返回完全精确匹配中华人民共和国的结果,而不希望返回分词后的检索结果,则使用短语查询语法。

注意
  • 基本查询:分词后再检索,关键词没有双引号。
  • 短语查询:精确匹配,不会做分词处理,关键词有双引号。
基本查询语句:
SELECT *, MATCH (title) AGAINST ('中华人民共和国') AS score
FROM fulltext_test
WHERE MATCH (title) AGAINST ('中华人民共和国') > 0.9
ORDER BY score DESC;

短语查询语句:

SELECT *, MATCH (title) AGAINST ('"中华人民共和国"') AS score
FROM fulltext_test
WHERE MATCH (title) AGAINST ('"中华人民共和国"') > 0.9
ORDER BY score DESC;

高亮支持

使用函数高亮

全文检索功能可以对查询的结果进行高亮,使用函数fulltext_highlight(column)实现。查询语句如下:

SELECT MATCH (title) AGAINST ('浙江省杭州市') AS score, fulltext_highlight(title)
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
ORDER BY score DESC
LIMIT 5;

执行结果:

score              | fulltext_highlight(title)                                                              |
+--------------------+------------------------------------------------------------------------------------+
| 15.57516860961914  | <em>浙江省</em>、<em>杭州市</em>网信办联合约谈“二更食堂”公众号负责人                               |
| 11.763599395751953 | <em>浙江省</em>兰溪<em>市</em>华宇方圆项目正式签约                                           |
| 11.269683837890625 | <em>浙江省</em>大学前五名,你知道是那些大学吗?                                                |
| 11.153276443481445 | <em>浙江省</em>的985、211大学只有浙大,是否可以说<em>浙江省</em>的大学教育质量差?                      |
| 10.928305625915527 | <em>杭州市</em>最适合情侣去的地方                                                                                                            |

使用hint自定义高亮

查询语句如下:

/*fulltext_highlight_pre_tag=<span>,fulltext_highlight_post_tag=</span>*/
SELECT MATCH (title) AGAINST ('浙江省杭州市') AS score, fulltext_highlight(title)
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
ORDER BY score DESC
LIMIT 5;

如果您自定义的tag中包含等于号或者逗号,需要将整个tag值用中括号包裹,示例如下:

/*fulltext_highlight_pre_tag=[<h3 style="color:blue">],fulltext_highlight_post_tag=</h3>*/
SELECT MATCH (title) AGAINST ('浙江省杭州市') AS score, fulltext_highlight(title)
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
ORDER BY score DESC
LIMIT 5;

执行结果:

+--------------------+--------------------------------------------------------------------------------------------------------------------------+
| score              | fulltext_highlight(title)                                                                                                |
+--------------------+--------------------------------------------------------------------------------------------------------------------------+
| 15.867133140563965 | <h3 style="color:blue">浙江省</h3>、<h3 style="color:blue">杭州市</h3>网信办联合约谈“二更食堂”公众号负责人               |
| 12.205625534057617 | <h3 style="color:blue">浙江省</h3>兰溪<h3 style="color:blue">市</h3>华宇方圆项目正式签约                                 |
| 11.646674156188965 | <h3 style="color:blue">浙江省</h3>的985、211大学只有浙大,是否可以说<h3 style="color:blue">浙江省</h3>的大学教育质量差? |
| 11.338353157043457 | <h3 style="color:blue">浙江省</h3>大学前五名,你知道是那些大学吗?                                                       |
| 11.2699556350708   | <h3 style="color:blue">杭州</h3>GDP一万亿,宁波GDP八千五百亿,那为什么<h3 style="color:blue">浙江省</h3>经济中心在宁波? |
+--------------------+--------------------------------------------------------------------------------------------------------------------------+

同一列有多个全文索引时高亮

如果您针对同一个列有多个全文索引,如下查询语句:
SELECT MATCH (title) AGAINST ('浙江省杭州市') AS score, fulltext_highlight(title)
FROM fulltext_test
WHERE MATCH (title) AGAINST ('浙江省杭州市') > 0.9
    AND MATCH (title) AGAINST ('中国') > 0.9
ORDER BY score DESC
LIMIT 5;
那么最终将同时对浙江省杭州市中国进行高亮,结果为:
+--------------------+----------------------------------------------+
| score              | fulltext_highlight(title)                    |
+--------------------+----------------------------------------------+
| 4.041414260864258  | <em>杭州</em>有哪些<em>中国</em>之最?       |
| 3.8747754096984863 | <em>中国</em><em>杭州</em>旅游区             |
| 3.7213351726531982 | 走遍<em>中国</em>-<em>杭州</em>印象          |
| 3.7213351726531982 | 「<em>杭州</em>篇」<em>中国</em>旅途美食地图 |
| 3.641066789627075  | 湘西在<em>中国</em>的哪个<em>省</em>?       |
+--------------------+----------------------------------------------+

不支持的语法

全文索引过滤条件match() against()不能放在子查询外作为子查询的过滤条件,例如:
SELECT *
FROM (
    SELECT id, substr(text, 4) AS text
    FROM tbl
) A
WHERE MATCH (A.text) AGAINST ('浙江省杭州市');