PostgreSQL中to_tsquery搜索如何忽略特殊字符？

问题根源 #

当前查询直接通过regexp_replace拼接to_tsquery参数，未处理PostgreSQL全文搜索的保留字符（如&、|、!、(、)、*等）。当用户输入包含这类字符的内容（比如cats &）时，会被to_tsquery误判为语法逻辑符号，导致查询报错或结果异常。

解决方法 #

1. Java端预处理转义 #

在将搜索参数传入SQL前，先转义所有tsquery保留字符，确保它们被当作普通字面量处理。

编写工具方法：

public static String escapeTsQuery(String input) {
    if (input == null || input.isBlank()) {
        return "";
    }
    // 转义tsquery特殊字符：& | ! ( ) : * '
    String escaped = input.replaceAll("([&|!():*'])", "\\\\$1");
    // 按非单词字符分割并拼接前缀匹配规则
    return escaped.replaceAll("\\W+", ":* & ") + ":*";
}

使用时先处理参数再传入：

String escapedSearch = escapeTsQuery(search);
Page<Content> result = contentRepository.searchContentByTitle(escapedSearch, pageable);

修改后的Repository查询（移除SQL内的转义逻辑）：

@Query(value = "SELECT * " +
        "FROM content, " +
        "to_tsquery(:search) as q " +
        "WHERE make_tsvector(title, title_original, title_other, title_jp) @@ q " +
        "ORDER BY ts_rank(make_tsvector(title, title_original, title_other, title_jp), q) DESC", 
        nativeQuery = true)
Page<Content> searchContentByTitle(@Param("search") String search, Pageable pageable);

2. PostgreSQL端直接处理 #

利用PostgreSQL内置的ts_escape函数转义特殊字符，无需Java端额外处理：

修改后的查询语句：

@Query(value = "SELECT * " +
        "FROM content, " +
        "to_tsquery(regexp_replace(ts_escape(trim(:search)), '\\s+', ':* & ', 'g') || ':*') as q " +
        "WHERE make_tsvector(title, title_original, title_other, title_jp) @@ q " +
        "ORDER BY ts_rank(make_tsvector(title, title_original, title_other, title_jp), q) DESC", 
        nativeQuery = true)
Page<Content> searchContentByTitle(@Param("search") String search, Pageable pageable);

说明：

• ts_escape(trim(:search))：转义输入中的所有tsquery特殊字符，例如将&转为\&，确保被当作字面量解析。
• regexp_replace(..., '\\s+', ':* & ', 'g')：将连续空白字符替换为:* & ，实现多词之间的AND前缀匹配逻辑。
• || ':*'：给最后一个词添加前缀匹配规则。

额外优化建议 #

• 预先生成tsvector字段：避免每次查询都调用make_tsvector，可在表中新增title_tsv字段，通过触发器自动更新该字段的tsvector值，查询时直接用该字段匹配，提升性能。
• 尝试ts_rank_cd排序：如果需要更精准的结果排序，可替换ts_rank为覆盖度排序函数ts_rank_cd。

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