FULLTEXT查询与Postgresql中的分数/排名 [英] FULLTEXT query with scores/ranks in Postgresql

问题描述

Im new to Postgres and I dont know how to translate this MySQL query to postgres:

（title，cached_tag_list）AGAINST（'phrase'）AS得分FROM图片WHERE MATCH（title，cached_tag_list）AGAINST（'phrase'）ORDER BY score DESC;

SELECT pictures.id, MATCH (title, cached_tag_list) AGAINST ('phrase') AS score FROM pictures WHERE MATCH (title, cached_tag_list) AGAINST ('phrase') ORDER BY score DESC;

推荐答案

Postgres全文搜索与MySQL有点不同全文搜索。它有更多的选择，但可能有点难以按照自己喜欢的方式工作。

The Postgres fulltext search is a little different from the MySQL fulltext search. It has a lot more options but can be a bit more difficult to get working the way you like it.

本文档告诉您如何对搜索结果进行排名，但我强烈建议您阅读手册中的整个全文部分，以了解您可以使用它做什么： http://www.postgresql.org/docs/current/interactive/textsearch-controls.html#TEXTSEARCH-RANKING

This document tells you how to rank your search results, but I strongly recommend you read the entire fulltext section from the manual to get an idea about what you can do with it: http://www.postgresql.org/docs/current/interactive/textsearch-controls.html#TEXTSEARCH-RANKING

基本上，您的查询的等价物应该是这样的：

Basically, the equivalent of your query would be this:

SELECT pictures.id, ts_rank_cd(textsearch, 'phrase') AS score
FROM pictures
ORDER BY score DESC

尽可以看，这使用 textsearch 这是你必须自己定义的东西。对于简短版本，请阅读： http://www.postgresql.org/ docs / current / interactive / textsearch-tables.html

As you can see, this uses textsearch which is something you will have to define yourself. For the short version, read: http://www.postgresql.org/docs/current/interactive/textsearch-tables.html

查询本质上非常简单：

SELECT pictures.id, ts_rank_cd(to_tsvector('english', pictures.title), 'phrase') AS score
FROM pictures
ORDER BY score DESC

但我强烈建议添加索引：

But I would strongly recommend adding indexes aswell:

CREATE INDEX pictures_title ON pictures USING gin(to_tsvector('english', title));

这篇关于FULLTEXT查询与Postgresql中的分数/排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！