PostgreSQL的进阶使用： 高级全文搜索

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

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);

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

CREATE FUNCTION articles_tsv_trigger() RETURNS trigger AS $$
BEGIN
  NEW.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();

2. 基本全文查询

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

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

3. 短语和前缀搜索

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

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

对于前缀搜索：

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

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;

5. 加权不同字段

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

UPDATE articles
SET tsv = setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
           setweight(to_tsvector('english', coalesce(body, '')), 'B');

6. 与结构化过滤器结合

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

SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'index')
AND published = true
AND category = 'PostgreSQL';

7. 突出显示匹配项

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

SELECT title, ts_headline('english', body, to_tsquery('english', 'search'))
FROM articles
WHERE tsv @@ to_tsquery('english', 'search');

8. JSON 和全文

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

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

结论

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

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