大家好，这里是架构资源栈！点击上方关注，添加“星标”，一起学习大厂前沿架构！

PostgreSQL 内置了强大的全文搜索支持。虽然许多开发人员都熟悉基本的to_tsvector函数to_tsquery，但还有很多更深层次的功能有待挖掘。在本指南中，我们将探索更多高级功能，例如排名、前缀匹配、加权以及将全文与结构化过滤器相结合。

1. 基本设置和索引

首先，确保您的文本内容已正确编入索引。以下是如何创建用于全文搜索的 GIN 索引：

CREATE TABLE articles (id SERIAL PRIMARY KEY,title TEXT,body TEXT,tsv TSVECTOR
);UPDATE articles SET tsv = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));CREATE INDEX tsv_idx ON articles USING GIN(tsv);
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

使用触发器来保持 tsvector 自动更新：

CREATE FUNCTION articles_tsv_trigger() RETURNS trigger AS $$
BEGINNEW.tsv := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, ''));RETURN NEW;
END
$$ LANGUAGE plpgsql;CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_tsv_trigger();
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

2. 基本全文查询

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'postgres & search');
  • 1.
  • 2.

这与包含“postgres”和“search”的行匹配。

3. 短语和前缀搜索

要搜索短语或前缀，请使用：

SELECT * FROM articles
WHERE tsv @@ phraseto_tsquery('english', 'full text search');
  • 1.
  • 2.

对于前缀搜索：

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'search:*');
  • 1.
  • 2.

4. 排名结果

使用ts_rank或ts_rank_cd按相关性排序：

SELECT *, ts_rank(tsv, to_tsquery('english', 'postgres')) AS rank
FROM articles
WHERE tsv @@ to_tsquery('english', 'postgres')
ORDER BY rank DESC;
  • 1.
  • 2.
  • 3.
  • 4.

5. 加权不同字段

您可以为字段分配不同的权重，以使标题优先于正文，例如：

UPDATE articles
SET tsv = setweight(to_tsvector('english', coalesce(title, '')), 'A') ||setweight(to_tsvector('english', coalesce(body, '')), 'B');
  • 1.
  • 2.
  • 3.

6. 与结构化过滤器结合

全文搜索可以与其他SQL条件组合：

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'index')
AND published = true
AND category = 'PostgreSQL';
  • 1.
  • 2.
  • 3.
  • 4.

7. 突出显示匹配项

用于ts_headline突出显示匹配的术语：

SELECT title, ts_headline('english', body, to_tsquery('english', 'search'))
FROM articles
WHERE tsv @@ to_tsquery('english', 'search');
  • 1.
  • 2.
  • 3.

8. JSON 和全文

您甚至可以索引和搜索 JSON 字段：

CREATE INDEX idx_json_search ON documents
USING GIN (to_tsvector('english', data::text));
  • 1.
  • 2.

结论

PostgreSQL 的全文搜索功能尚未得到充分利用。正确使用后，它允许您直接在数据库内部构建可扩展、复杂的搜索功能，无需第三方引擎。无论您是维护博客还是大型文档系统，掌握这些技术都能将您的搜索功能提升到一个新的水平。

原文地址： https://mp.weixin.qq.com/s/n8wS-qITatRE0X9EDpRziA
本文由博客一文多发平台  OpenWrite 发布！