sql - 逻辑搜索字段数据库选择问题

Question

这是一个有点复杂但简单的问题。我正在创建一个允许存储在对象上的小型应用程序，该对象具有标题、描述、标签和子对象。主题包含评论。使用搜索字段在这些对象中查找任何匹配项的理想方式是什么。

例如对象 1 {id:1, title:test, description:fun, tags:[{games, toys}]}subobject 1 {lid:1, comment:'this is fun and fast'}

假设我搜索:快速有趣。或者我搜索:测试

我希望其中任何一个返回相同的对象。

我知道我必须用空格或逗号分解变量并创建一个数组，但我应该如何构造 sql。

我并不是要创建一个复杂的网页排名算法，如果我必须限制通过标题或标签或描述进行搜索，我会这样做。

注意:这是一个简单的应用程序，我只是在寻找选项。

Answer 1

我知道的最简单的方法是使用 PostgreSQL 三元组搜索。假设您有一个这样的表:

CREATE TABLE t (id int NOT NULL PRIMARY KEY, value text);
INSERT INTO t VALUES (1, '{id:1, title:test, description:fun, tags:[{games, toys}]} subobject 1 {lid:1, comment:''this is fun and fast''}');

你可以用这个来准备搜索:

CREATE EXTENSION pg_trgm;
CREATE INDEX t_trgm ON t USING gin (value gin_trgm_ops);
SELECT set_limit(0.05);

您只需为每个数据库安装一次扩展。您可以使用 GiST 或 GIN 索引；只需使用相应的操作集。 GiST 通常更新速度更快； GIN 通常搜索速度更快——因此最好的选择取决于工作量。我将连接的相似性限制设置为 5%，因为您似乎对字符串的一小部分匹配很满意。根据需要进行调整。请记住，此限制是基于 session 的，因此您需要在使用 % 运算符之前在连接上进行设置。

完成该设置后，这里是搜索:

SELECT * FROM t WHERE value % 'test';
SELECT * FROM t WHERE value % 'fun fast';

没有比这更简单的了。

虽然当表中只有一行时它甚至可能不会使用索引，但我们发现在我们的生产数据库中有数百万行要搜索，通常会运行相似性搜索在几十毫秒内。

http://www.postgresql.org/docs/current/interactive/pgtrgm.html