PostgreSQL 全文搜索 #

┌─────────────────────────────────────────────────────────────┐
│                    全文搜索 vs LIKE                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  LIKE 模糊匹配                                              │
│  ├── 简单模式匹配                                          │
│  ├── 不支持词干提取                                        │
│  ├── 不支持相关性排序                                      │
│  └── 性能较差                                              │
│                                                             │
│  全文搜索                                                   │
│  ├── 分词处理                                              │
│  ├── 词干提取（running → run）                             │
│  ├── 停用词过滤（the, a, an）                              │
│  ├── 相关性排序                                            │
│  └── 支持索引，性能好                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- tsvector：文档的词素向量
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':8 'fox':4 'jumps':5 'lazi':7 'quick':2

-- tsquery：搜索查询
SELECT to_tsquery('english', 'quick & fox');
-- 'quick' & 'fox'

-- 匹配操作符 @@
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox');
-- true

┌─────────────────────────────────────────────────────────────┐
│                    分词过程                                  │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  原文：The Quick Brown Fox                                  │
│                                                             │
│  1. 分词                                                    │
│     ['The', 'Quick', 'Brown', 'Fox']                       │
│                                                             │
│  2. 小写化                                                  │
│     ['the', 'quick', 'brown', 'fox']                       │
│                                                             │
│  3. 去停用词                                                │
│     ['quick', 'brown', 'fox']                              │
│                                                             │
│  4. 词干提取                                                │
│     ['quick', 'brown', 'fox']                              │
│                                                             │
│  5. 生成 tsvector                                           │
│     'brown':2 'fox':3 'quick':1                            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建示例表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO articles (title, content) VALUES
    ('PostgreSQL Guide', 'PostgreSQL is a powerful open source relational database'),
    ('Database Design', 'Learn how to design efficient database schemas'),
    ('SQL Tutorial', 'Master SQL queries with practical examples'),
    ('Web Development', 'Build modern web applications with PostgreSQL backend');

-- 基本全文搜索
SELECT * FROM articles 
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database');

-- 输出：
-- id | title            | content
-- ---+------------------+--------------------------------------------------
-- 1  | PostgreSQL Guide | PostgreSQL is a powerful open source relational...
-- 2  | Database Design  | Learn how to design efficient database schemas

-- 添加 tsvector 列
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 更新 tsvector 列
UPDATE articles 
SET search_vector = to_tsvector('english', title || ' ' || content);

-- 创建触发器自动更新
CREATE OR REPLACE FUNCTION articles_search_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION articles_search_trigger();

-- 使用 tsvector 列搜索
SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'database');

-- to_tsquery：解析查询字符串
SELECT to_tsquery('english', 'database & design');
-- 'design' & 'databas'

-- plainto_tsquery：解析普通文本
SELECT plainto_tsquery('english', 'database design');
-- 'databas' & 'design'

-- phraseto_tsquery：短语查询
SELECT phraseto_tsquery('english', 'database design');
-- 'databas' <-> 'design'

-- websearch_to_tsquery：Web 风格搜索
SELECT websearch_to_tsquery('english', 'database OR design -sql');
-- 'databas' | 'design' & !'sql'

-- & 与（AND）
SELECT * FROM articles WHERE search_vector @@ to_tsquery('database & design');

-- | 或（OR）
SELECT * FROM articles WHERE search_vector @@ to_tsquery('database | sql');

-- ! 非（NOT）
SELECT * FROM articles WHERE search_vector @@ to_tsquery('database & !sql');

-- <-> 短语（相邻）
SELECT * FROM articles WHERE search_vector @@ to_tsquery('open <-> source');

-- @> 包含
SELECT * FROM articles WHERE search_vector @> to_tsquery('database');

-- 创建 GIN 索引
CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

-- 或直接在表达式上创建索引
CREATE INDEX idx_articles_search_expr ON articles USING gin(to_tsvector('english', title || ' ' || content));

-- 使用索引
SELECT * FROM articles WHERE search_vector @@ to_tsquery('database');

-- GiST 索引更小，但查询稍慢
CREATE INDEX idx_articles_search_gist ON articles USING gist(search_vector);

-- 计算相关性分数
SELECT 
    title,
    ts_rank(search_vector, to_tsquery('database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('database')
ORDER BY rank DESC;

-- 输出：
-- title            | rank
-- ------------------+------
-- Database Design  | 0.27
-- PostgreSQL Guide | 0.14

-- 考虑词频的排名
SELECT 
    title,
    ts_rank_cd(search_vector, to_tsquery('database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('database')
ORDER BY rank DESC;

-- 设置不同字段的权重
-- A: 标题, B: 摘要, C: 内容, D: 忽略
SELECT 
    title,
    ts_rank(
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', content), 'B'),
        to_tsquery('database')
    ) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('database')
ORDER BY rank DESC;

-- 更新触发器使用权重
CREATE OR REPLACE FUNCTION articles_search_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 高亮显示匹配内容
SELECT 
    title,
    ts_headline('english', content, to_tsquery('database')) AS highlight
FROM articles
WHERE search_vector @@ to_tsquery('database');

-- 输出：
-- title            | highlight
-- ------------------+--------------------------------------------------
-- PostgreSQL Guide | PostgreSQL is a powerful open source relational <b>database</b>
-- Database Design  | Learn how to design efficient <b>database</b> schemas

-- 自定义高亮样式
SELECT 
    title,
    ts_headline('english', content, to_tsquery('database'),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=20, MinWords=10'
    ) AS highlight
FROM articles
WHERE search_vector @@ to_tsquery('database');

-- PostgreSQL 默认不支持中文分词
-- 需要安装 zhparser 扩展

-- 安装后创建配置
CREATE TEXT SEARCH CONFIGURATION zh (PARSER = zhparser);
COMMENT ON TEXT SEARCH CONFIGURATION zh IS 'Chinese configuration';

-- 使用中文配置
SELECT to_tsvector('zh', 'PostgreSQL 是一个功能强大的开源数据库');
-- 'postgresql':1 '功能':3 '强大':4 '开源':5 '数据库':6

-- 创建中文索引
CREATE INDEX idx_articles_search_zh ON articles USING gin(to_tsvector('zh', title || ' ' || content));

-- 中文搜索
SELECT * FROM articles WHERE to_tsvector('zh', title || ' ' || content) @@ to_tsquery('zh', '数据库');

-- 如果没有 zhparser，可以使用简单方法
-- 按字符分词（不推荐，效果差）
SELECT to_tsvector('simple', '数据库设计');
-- '数据库设计':1

-- 使用 LIKE 模糊匹配（备选方案）
SELECT * FROM articles WHERE content LIKE '%数据库%';

CREATE OR REPLACE FUNCTION search_articles(search_text TEXT)
RETURNS TABLE (
    id INTEGER,
    title VARCHAR,
    content TEXT,
    rank REAL,
    headline TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        articles.id,
        articles.title,
        articles.content,
        ts_rank(search_vector, plainto_tsquery('english', search_text)) AS rank,
        ts_headline('english', content, plainto_tsquery('english', search_text),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20'
        ) AS headline
    FROM articles
    WHERE search_vector @@ plainto_tsquery('english', search_text)
    ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT * FROM search_articles('database design');

-- 带分页的搜索
SELECT 
    id,
    title,
    ts_rank(search_vector, to_tsquery('database')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('database')
ORDER BY rank DESC
LIMIT 10 OFFSET 0;

-- 获取搜索建议（前缀匹配）
SELECT DISTINCT word 
FROM ts_stat('SELECT search_vector FROM articles')
WHERE word LIKE 'data%'
ORDER BY word;

扩展阶段
├── JSON操作
├── 全文搜索（本文）
└── 常用扩展