PostgreSQL/Django实现多关键词任意顺序精确不区分大小写查询求助

一、PostgreSQL 原生查询实现 #

要满足精确匹配独立单词、不区分大小写、所有搜索词必须存在的要求，有两种可靠方案：

方法1：全文搜索（高性能推荐） #

利用PostgreSQL的全文搜索特性，将三个字段合并为可检索的向量，再匹配包含所有搜索词的查询：

SELECT *
FROM "Article"
WHERE to_tsvector('english', title || ' ' || description || ' ' || categories) @@ plainto_tsquery('english', 'word1 & word2');

• 动态多词扩展：只需将搜索词用&连接（如'word1 & word2 & word3'）即可支持任意数量的搜索词。
• 优势：自带大小写忽略、单词边界识别，且可通过建立全文索引大幅提升查询速度。

方法2：正则表达式匹配 #

通过正则的\m（词首边界）和\M（词尾边界）确保匹配独立单词，用~*实现不区分大小写：

SELECT *
FROM "Article"
WHERE (title || ' ' || description || ' ' || categories) ~* '\mword1\M'
  AND (title || ' ' || description || ' ' || categories) ~* '\mword2\M';

• 注意：该方案适合小数据量场景，大数据量下需手动建立正则索引，维护成本较高。

二、PostgreSQL 存储过程实现 #

如果需要封装成可直接传入搜索词列表的调用接口，可编写PL/pgSQL存储过程：

CREATE OR REPLACE FUNCTION search_articles(search_terms text[])
RETURNS SETOF "Article" AS $$
DECLARE
  term text;
  query text := 'SELECT * FROM "Article" WHERE 1=1';
BEGIN
  FOREACH term IN ARRAY search_terms LOOP
    -- 转义正则特殊字符，避免语法错误
    query := query || format(' AND (title || '' '' || description || '' '' || categories) ~* ''\m%s\M''', regexp_replace(term, '([\\.*+?|()\[\]{}\$^])', '\\\1', 'g'));
  END LOOP;
  RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql STABLE;

调用方式：

SELECT * FROM search_articles(ARRAY['word1', 'word2']);

三、Django ORM 实现 #

假设你的Django模型定义如下：

from django.db import models

class Article(models.Model):
    title = models.TextField()
    description = models.TextField()
    categories = models.TextField()

方法1：正则匹配方案 #

通过Q对象构造多条件查询，确保每个搜索词匹配独立单词：

import re
from django.db.models import Q

def search_articles(search_terms):
    q_filter = Q()
    combined_expr = 'title || '' '' || description || '' '' || categories'
    for term in search_terms:
        escaped_term = re.escape(term)
        q_filter &= Q(**{f'{combined_expr}__iregex': rf'\m{escaped_term}\M'})
    return Article.objects.filter(q_filter)

调用示例：search_articles(['word1', 'word2'])

方法2：全文搜索方案（高性能推荐） #

利用Django对PostgreSQL全文搜索的封装，支持排序和高效检索：

from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank

def search_articles(search_terms):
    # 合并三个字段为搜索向量
    search_vector = SearchVector('title', 'description', 'categories', config='english')
    # 构造多词AND查询
    search_query = SearchQuery(' & '.join(search_terms), config='english')
    # 按匹配度排序返回结果
    return Article.objects.annotate(rank=SearchRank(search_vector, search_query))\
                          .filter(rank__gt=0)\
                          .order_by('-rank')

• 注：config='english'会自动处理大小写和词形，若需更简单的精确匹配，可替换为config='simple'。

内容的提问来源于stack exchange，提问作者Soumya