PostgreSQL：全文搜索

本文介绍了PostgreSQL：全文搜索 - 如何搜索部分单词？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在此处发布一个关于如何提高SQL Search方法速度的问题后，建议我更新我的表以利用全文搜索。这就是我现在所做的，使用Gist索引来加快搜索速度。在一些简单的查询中，我注意到了一个显着的增加，我很高兴。

然而，我很难搜索部分单词。例如，我有几个包含Squire（454）的记录，而且我有几个包含Squirrel（173）的记录。现在，如果我搜索Squire，它只会返回454条记录，但我也希望它返回Squirrel记录。

我的查询看起来像这样

SELECT title FROM movies WHERE vectors @@ to_tsoquery（'squire'）;

我以为我可以做 to_tsquery（'squire％'）但这不起作用。 如何获得它以搜索部分匹配？

另外，在我的数据库中，我有记录是电影和其他只是电视节目。这些名称与名称不同，因此明斯特是电视节目，而明斯特是该节目的电影。我想要做的就是只搜索电视节目和电影。关于如何实现这一目标的任何想法？

问候 Anthoni

解决方案甚至使用 LIKE ，你将无法从 squire％获得'松鼠'，因为'松鼠'有两个'r'。要获得Squire和Squirrel，你可以运行下面的查询： pre $ SELECT title FROM movies WHERE vectors @@ to_tsquery（'squire | squirrel ）;

为了区分电影和电视节目，您应该在数据库中添加一列。但是，这种猫的皮肤有很多种方法。你可以使用子查询来强制postgres首先找到匹配'squire'和'squirrel'的电影，然后搜索该子集来查找以'''开头的标题。可以创建索引以便在 LIKE'％...'搜索。

没有探索其他索引的可能性，你也可以运行这些 - 他们找到哪个是最快的：

SELECT title FROM（ SELECT * FROM电影 WHERE vectors @@ to_tsquery（'squire | squirrel'））t WHERE title ILIKE'％';

Following a question posted here about how I can increase the speed on one of my SQL Search methods, I was advised to update my table to make use of Full Text Search. This is what I have now done, using Gist indexes to make searching faster. On some of the "plain" queries I have noticed a marked increase which I am very happy about.

However, I am having difficulty in searching for partial words. For example I have several records that contain the word Squire (454) and I have several records that contain Squirrel (173). Now if I search for Squire it only returns the 454 records but I also want it to return the Squirrel records as well.

My query looks like this

SELECT title FROM movies WHERE vectors @@ to_tsoquery('squire');

I thought I could do to_tsquery('squire%') but that does not work. How do I get it to search for partial matches ?

Also, in my database I have records that are movies and others that are just TV Shows. These are differentiated by the "" over the name, so like "Munsters" is a TV Show, whereas The Munsters is the film of the show. What I want to be able to do is search for just the TV Show AND just the movies. Any idea on how I can achieve this ?

Regards Anthoni

解决方案

Even using LIKE you will not be able to get 'squirrel' from squire% because 'squirrel' has two 'r's. To get Squire and Squirrel you could run the following query:

SELECT title FROM movies WHERE vectors @@ to_tsquery('squire|squirrel');

To differentiate between movies and tv shows you should add a column to your database. However, there are many ways to skin this cat. You could use a sub-query to force postgres to first find the movies matching 'squire' and 'squirrel' and then search that subset to find titles that begin with a '"'. It is possible to create indexes for use in LIKE '"%...' searches.

Without exploring other indexing possibilities you could also run these - mess around with them to find which is fastest:

SELECT title FROM ( SELECT * FROM movies WHERE vectors @@ to_tsquery('squire|squirrel') ) t WHERE title ILIKE '"%';

or

SELECT title FROM movies WHERE vectors @@ to_tsquery('squire|squirrel') AND title ILIKE '"%';