SQL 全文搜索

LIKE '%键盘%' 为什么全表扫：
B-tree 索引按"从左到右"排序。前缀已知（LIKE '无线%'）= 一个范围扫描，能走索引；
前缀未知（LIKE '%键盘%'）= 任何行都可能是答案，索引无从定位 → 逐行扫描全文。

倒排索引（Inverted Index）的思路：
写入时把文本拆成词，建"词 → 文档列表"的目录：
  '无线'  → [doc 3, doc 17, doc 42]
  '蓝牙'  → [doc 3, doc 9]
查询时：查目录拿列表求交集 → 直接得到 doc 3，不碰其余文档。

-- 表达式与索引同形是硬要求
CREATE INDEX idx_products_fts ON products
  USING gin (to_tsvector('english', name || ' ' || description));

SELECT id, name, ts_rank(...) AS score
FROM products
WHERE to_tsvector('english', name || ' ' || description)
      @@ plainto_tsquery('english', 'wireless keyboard')
ORDER BY score DESC;
-- @@@ 匹配词素集合；ts_rank 出相关度；中文需 zhparser/pg_jieba（见 PG 专篇）

CREATE FULLTEXT INDEX idx_products_fts ON products (name, description);

-- 自然语言模式：默认，按相关度自动排序
SELECT id, name,
       MATCH(name, description) AGAINST('wireless keyboard') AS relevance
FROM products
WHERE MATCH(name, description) AGAINST('wireless keyboard');

-- 布尔模式：手工控制必须/排除
SELECT id FROM products
WHERE MATCH(name, description) AGAINST('+keyboard +bluetooth -wired' IN BOOLEAN MODE);

-- 前提：建 FULLTEXT CATALOG + FULLTEXT INDEX（一次性的目录设施）
CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;
CREATE FULLTEXT INDEX ON products (name, description)
  KEY INDEX PK_products ON ft_catalog;

SELECT id, name
FROM products
WHERE CONTAINS((name, description), '"wireless" AND "keyboard"');

-- FREETEXT：宽松的"意思接近"匹配（自动词形变化）
SELECT id, name FROM products
WHERE FREETEXT((name, description), 'wireless keyboards');

-- MySQL 布尔模式：+ 必须、- 排除、"..." 短语
WHERE MATCH(body) AGAINST('+数据库 +优化 -redis' IN BOOLEAN MODE)

-- PostgreSQL：websearch_to_tsquery 直接吃用户输入（引号/or/- 与 MySQL 布尔相似）
WHERE fts @@ websearch_to_tsquery('simple', '数据库 优化 -redis')

-- SQL Server：CONTAINS 的布尔表达式
WHERE CONTAINS(body, '数据库 AND 优化 AND NOT "Redis"')
-- 短语：CONTAINS(body, '"全文 检索"')  —— 引号内按位置相邻匹配

-- MySQL 查询扩展：命中结果里的词反哺第二轮搜索
SELECT * FROM app_logs
WHERE MATCH(message) AGAINST('timeout' WITH QUERY EXPANSION);
-- 第一轮找到 "Connection timed out"，第二轮把 timed/out 也搜出来
-- 代价：可能召回大量弱相关行，日志量大时慎用

-- SQL Server FREETEXT：自动词形/同义近似
SELECT * FROM app_logs WHERE FREETEXT(message, 'timeout');

-- PostgreSQL：原生没有查询扩展，用词干还原（english 配置跑→run）+ trigram 兜底
WHERE message_tsv @@ plainto_tsquery('english', 'timeout')
   OR message % 'timeout'          -- pg_trgm 相似度，兜住拼写变体