基于PostgreSQL的中间文本模糊查询

在文献查询中，时常遇到一些无法识别的文字，此时如能通过查询即有文献的中间字符，即可提供非常有价值的参照，例如：类似“AB？D”这种模式，并能分析“问号”可能是什么字符。然而，现实是多数的官方网站并不提供这样的查询方法，那么，这种查询是否可行呢？我们结合PostgreSQL 文本搜索扩展，借助 pg_trgm（Trigram）模块，在不确定中间字符的情况下，实现中间字符模糊查询。

1、启用扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2、准备数据

CREATE TABLE test_strings (
  id SERIAL PRIMARY KEY,
  content TEXT
);

INSERT INTO test_strings (content) VALUES
  ('ABXD'),
  ('ABCD'),
  ('AB1D'),
  ('AB@D'),
  ('AB12D'),
  ('ABD'),      -- 这个不满足中间字符条件
  ('ABXYZD');   -- 太长，不符合 AB??D 模式

3、使用正则或 LIKE 实现“中间一/二个字符”匹配

3.1. 匹配“中间一个字符”：AB？D

SELECT * FROM test_strings
WHERE content ~ '^AB.D$';

结果为：

3.2. 匹配“中间两个字符”：AB？？D

SELECT * FROM test_strings
WHERE content ~ '^AB..D$';

结果为：

4、结合 regexp_matches 提取“问号对应的字符”

提取中间的字符：

SELECT
  content,
  regexp_matches(content, '^AB(.)D$') AS middle_char
FROM test_strings
WHERE content ~ '^AB.D$';

输出（middle_char 是数组）：

5、借助 pg_trgm 进行模糊搜索或索引优化

如果你要对大表使用模糊“中间不确定字符”搜索，可以创建 trigram 索引：

CREATE INDEX trgm_idx ON test_strings USING GIN (content gin_trgm_ops);

然后可支持如下模糊查询：

SELECT * FROM test_strings
WHERE content % 'ABXD';  -- 相似字符串搜索

你还可以用 similarity() 评分来找最像的：

SELECT content, similarity(content, 'AB?D') AS sim
FROM test_strings
ORDER BY sim DESC;

虽然 pg_trgm 不能直接识别 ? 为“任意字符”，但能用于近似字符串搜索，并配合正则一起使用。

小结