站点搜索怎么做：PostgreSQL 全文检索 + 中文分词的完整方案

-- 生成向量
SELECT to_tsvector('simple', '自己编译一个静态 ffmpeg');
-- 'ffmpeg':5 '一个':3 '编译':2 '自己':1 '静态':4

-- 生成查询
SELECT plainto_tsquery('simple', 'ffmpeg 编译');
-- 'ffmpeg' & '编译'

-- 匹配
SELECT to_tsvector('simple', '自己编译一个静态 ffmpeg') @@ plainto_tsquery('simple', 'ffmpeg 编译');
-- t

-- 危险：用户搜 "C++ 和 python"，& 和 + 会被当操作符
SELECT to_tsquery('simple', 'C++ 和 python');
-- ERROR:  syntax error in tsquery

SELECT to_tsvector('simple', '自己编译一个静态 ffmpeg');

'自己编译一个静态 ffmpeg'  →  ['自己编译一个静态', 'ffmpeg']

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;

SELECT to_tsvector('chinese', '自己编译一个静态 ffmpeg');
-- 'ffmpeg':5 '一个':3 '编译':2 '静态':4 '自己':1     ← 正确切开了

ERROR:  could not open extension control file ".../zhparser.control": No such file or directory

-- 加一列存搜索向量
ALTER TABLE downloader_blogpost ADD COLUMN search_vector tsvector;

-- 标题赋最高权重 A，摘要 B，正文 C
UPDATE downloader_blogpost SET search_vector =
    setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple', coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('simple', coalesce(search_text, '')), 'C');

-- GIN 索引（全文检索必须用 GIN，不是 B-tree）
CREATE INDEX blog_search_idx ON downloader_blogpost USING GIN (search_vector);

ALTER TABLE downloader_blogpost ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('simple'::regconfig, coalesce(title, '')), 'A') ||
    setweight(to_tsvector('simple'::regconfig, coalesce(excerpt, '')), 'B') ||
    setweight(to_tsvector('simple'::regconfig, coalesce(search_text, '')), 'C')
) STORED;

ERROR:  generation expression is not immutable

# search_utils.py
import re
import jieba

# 关闭 jieba 的新词发现（可选，能提速）
jieba.initialize()

# 保留英文单词、数字和中文，其它当分隔符
SPLIT_RE = re.compile(r"[^\w\u4e00-\u9fff]+", re.UNICODE)


def tokenize(text: str) -> str:
    """把中英混合文本切成「空格分隔的词序列」，供 to_tsvector('simple', ...) 使用。"""
    if not text:
        return ''
    text = text.lower()
    out = []
    for seg in SPLIT_RE.split(text):
        if not seg:
            continue
        if re.search(r'[\u4e00-\u9fff]', seg):
            # 含中文 → 用 jieba 切
            out.extend(w for w in jieba.cut(seg) if w.strip())
        else:
            # 纯英文/数字 → 直接作为一个词
            out.append(seg)
    return ' '.join(out)

>>> tokenize('自己编译一个静态 ffmpeg 的完整记录')
'自己 编译 一个 静态 ffmpeg 的 完整 记录'
>>> tokenize('HLS AES-128 加密流怎么下')
'hls aes 128 加密流 怎么 下'

STOPWORDS = {'的', '了', '是', '在', '和', '与', '这', '那', '一个', '我们', '怎么', '什么', ...}

def tokenize(text, drop_stopwords=True):
    words = ...
    if drop_stopwords:
        words = [w for w in words if w not in STOPWORDS]
    return ' '.join(words)

jieba.load_userdict('dict.txt')

ffprobe 100 n
断点续传 100 n
m3u8 100 n
HLS 100 n

class BlogPost(models.Model):
    ...
    search_text = models.TextField(blank=True, verbose_name='分词后的正文', editable=False)
    search_vector = models.TextField(blank=True, verbose_name='搜索向量', editable=False)  # 见下方说明

from django.db.models.signals import pre_save
from django.dispatch import receiver
from .search_utils import tokenize


@receiver(pre_save, sender=BlogPost)
def build_search_text(sender, instance, **kwargs):
    # 只取正文的纯文本（去掉 markdown 标记），并截断（正文很长，全部分词没意义）
    plain = strip_markdown(instance.content or '')
    instance.search_text = tokenize(plain[:20000])

from django.db import connection
from .search_utils import tokenize


def search_posts(q: str, limit: int = 20):
    """全文检索。返回 [(id, title, excerpt, rank)]。"""
    if not q.strip():
        return []

    # 用户输入用同样的分词规则处理，再转成 tsquery
    words = [w for w in tokenize(q).split() if w]
    if not words:
        return []
    # 词之间用 & （AND）：必须全部命中
    tsquery = ' & '.join(f"'{w}'" for w in words)

    sql = """
        SELECT id, title, excerpt, pub_date,
               ts_rank(search_vector, query) AS rank
        FROM downloader_blogpost, to_tsquery('simple', %s) query
        WHERE search_vector @@ query AND is_published = TRUE
        ORDER BY rank DESC, pub_date DESC
        LIMIT %s
    """
    with connection.cursor() as c:
        c.execute(sql, [tsquery, limit])
        return c.fetchall()

sql = """
    SELECT ... WHERE search_vector @@ websearch_to_tsquery('simple', %s)
"""

-- 权重的默认值
-- A=1.0, B=0.4, C=0.2, D=0.1

搜索 "ffmpeg 编译"：
1. 自己编译一个静态 ffmpeg：从依赖地狱到 16MB 单文件   (rank 0.91)  ← 标题全命中
2. 用 Python 包一层 ffmpeg 跑批量任务                 (rank 0.42)
3. FFmpeg 从入门到放弃                               (rank 0.38)

ORDER BY rank * (1.0 + 0.1 * EXTRACT(EPOCH FROM (now() - pub_date)) / -864000) DESC

ORDER BY (rank + 0.05 * exp(-EXTRACT(EPOCH FROM (now() - pub_date)) / (180*86400))) DESC

ts_rank_cd(search_vector, query, 32)    -- 32 = 归一化 + 考虑唯一词频

SELECT ts_headline(
    'simple',
    coalesce(title, '') || ' ' || coalesce(excerpt, ''),
    query,
    'StartSel=<mark>, StopSel=</mark>, MaxFragments=1, MaxWords=40, MinWords=15'
) AS headline
FROM downloader_blogpost, to_tsquery('simple', 'ffmpeg & 编译') query
WHERE search_vector @@ query;

自己编译一个静态 <mark>ffmpeg</mark>：从依赖地狱到 16MB 单文件

import bleach

headline_html = bleach.clean(raw_headline, tags=['mark'], attributes={}, strip=True)

@shared_task
def rebuild_search_vector(post_id):
    from django.db import connection
    with connection.cursor() as c:
        c.execute("""
            UPDATE downloader_blogpost SET search_vector =
                setweight(to_tsvector('simple'::regconfig, coalesce(title,'')), 'A') ||
                setweight(to_tsvector('simple'::regconfig, coalesce(excerpt,'')), 'B') ||
                setweight(to_tsvector('simple'::regconfig, coalesce(search_text,'')), 'C')
            WHERE id = %s
        """, [post_id])

@shared_task
def rebuild_all():
    """分批重建，别一次性 UPDATE（参考不停机迁移那篇）。"""
    ids = list(BlogPost.objects.values_list('id', flat=True))
    for i in range(0, len(ids), 200):
        for pk in ids[i:i + 200]:
            rebuild_search_vector(pk)
        time.sleep(0.05)

SYNONYMS = {
    '转码': ['转码', '编码', '重编码'],
    '去重': ['去重', '重复', '查重'],
    '部署': ['部署', '上线', '发布'],
}