PostgreSQL多词搜索：如何匹配含所有查询词的产品名称

嘿，我之前做后台系统搜索功能的时候也碰到过一模一样的问题——不想费劲写一堆AND name ~* 'xxx'的重复代码，给你几个实用又优雅的解决方案，都是PostgreSQL环境下能用的：

方案1：用PostgreSQL全文搜索（推荐，适合大数据量） #

PostgreSQL的全文搜索功能天生就是用来解决这种“包含所有关键词、不关心顺序”的场景，而且还支持词干匹配（比如能识别deoderant和deodorant是同一个词，不需要这个特性可以调整配置），最重要的是可以建索引优化性能。

代码示例：

SELECT product_name 
FROM products 
WHERE to_tsvector('english', product_name) @@ plainto_tsquery('english', 'deoderant cucumber');

解释： #

• to_tsvector('english', product_name)：把产品名称转换成全文搜索的向量格式，自动处理词干、忽略停用词（比如with这类无意义词汇）
• plainto_tsquery('english', '你的搜索词')：把空格分隔的搜索词转换成“必须包含所有词”的查询条件（相当于自动给每个词加上&逻辑）
• @@是全文搜索的匹配操作符，检查向量是否匹配查询

如果需要精确匹配每个词（不做词干处理），可以手动构造tsquery：

SELECT product_name 
FROM products,
     LATERAL (SELECT string_agg(quote_literal(word), ' & ') AS query
              FROM unnest(string_to_array('deoderant cucumber', '\s+')) AS words(word)) AS q
WHERE to_tsvector('simple', product_name) @@ to_tsquery('simple', q.query);

这里用simple配置，不会做词干处理，保证每个词的精确匹配。

方案2：动态生成正向预查正则 #

这个方法不用依赖全文搜索，直接用正则的正向预查特性，不管词序都能检查所有关键词是否存在，还能自动处理搜索词的转义（防止正则特殊字符比如.、*搞崩查询）。

代码示例：

SELECT product_name 
FROM products,
     LATERAL (
         SELECT '^' || string_agg('(?=.*' || regexp_escape(word) || ')', '') || '.*$' AS regex_pattern
         FROM unnest(string_to_array('deoderant cucumber', '\s+')) AS words(word)
     ) AS p
WHERE product_name ~* p.regex_pattern;

解释： #

• unnest(string_to_array(...))：把搜索词按空格分割成单个词的数组，再拆成独立行
• regexp_escape(word)：转义词里的正则特殊字符，比如搜索词是cucumber.时，不会被当成正则通配符
• string_agg(...)：把每个词转换成(?=.*词)的正向预查片段，拼接后整个正则会检查字符串是否包含所有关键词，完全不关心顺序

方案3：数组包含匹配（适合简单场景） #

如果你的产品名称都是用空格分隔的纯词汇（没有标点、连字符这些），可以用数组包含的方式，代码更简洁：

SELECT product_name 
FROM products 
WHERE string_to_array(lower(product_name), ' ') @> string_to_array(lower('deoderant cucumber'), ' ');

解释： #

• 把产品名称和搜索词都转成小写，再拆成数组
• @>操作符检查产品名称的数组是否包含搜索词的所有元素（也就是所有关键词都存在）

不过这个方法局限性比较大，比如产品名称里的Deodorant-with-cucumber会被当成一个数组元素，就匹配不到deodorant了，所以只适合简单的命名场景。

• 如果数据量大、需要性能优化，优先选方案1全文搜索，还能建GIN索引提升速度：CREATE INDEX idx_products_tsv ON products USING GIN (to_tsvector('english', product_name));
• 如果不想用全文搜索、需要灵活的正则匹配，选方案2，兼顾了通用性和优雅性
• 简单场景下用方案3快速实现

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