如何在PostgreSQL中对含非英文字符的列执行子字符串搜索？

让pg_trgm识别并保留非单词字符 #

pg_trgm默认忽略非单词字符，根源是依赖数据库的字符分类规则。要适配泰文及非字母数字字符的搜索，可按以下方式处理：

1. 配置泰文本地化环境 #

创建数据库时指定泰文的LC_COLLATE和LC_CTYPE，让PostgreSQL正确识别泰文字符为单词字符：

CREATE DATABASE mydb WITH LC_COLLATE='th_TH.UTF8' LC_CTYPE='th_TH.UTF8' TEMPLATE=template0;

注：已存在的数据库无法直接修改该配置，需导出数据后重建数据库。

2. 自定义trigram提取函数 #

若无法重建数据库，可编写自定义函数，强制将所有字符（包括非字母数字）纳入trigram计算：

CREATE OR REPLACE FUNCTION custom_trgm(text) RETURNS text[] AS $$
SELECT array_agg(substr($1, i, 3))
FROM generate_series(1, length($1) - 2) i;
$$ LANGUAGE sql IMMUTABLE;

基于该函数创建索引（推荐GIN或GIST类型）：

CREATE INDEX idx_mycol_custom_trgm ON mytable USING GIN (custom_trgm(mycol));

查询时通过自定义函数匹配：

SELECT * FROM mytable WHERE custom_trgm(mycol) @> custom_trgm('目标搜索串');

泰文列查询速度优化 #

• 确保列使用UTF8编码，避免字符转换带来的额外开销。
• 若GIN索引效率不佳，可尝试改用GIST索引，部分场景下对非英文文本的查询性能更优。
• 若仅需前缀/后缀匹配，可使用LIKE结合text_pattern_ops索引：

  CREATE INDEX idx_mycol_like ON mytable (mycol text_pattern_ops);
  

  查询语句：

  SELECT * FROM mytable WHERE mycol LIKE '%目标内容%';
  

注意事项 #

• 自定义trigram函数会将所有字符（含空格、符号）纳入计算，会增大索引体积，需根据数据量评估存储成本。
• 泰文无空格分词的特性会导致trigram匹配的精度、效率与英文有差异，建议结合业务场景调整搜索逻辑。

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