PostgreSQL中等值查询无结果，模糊/大小写不敏感查询有结果的原因

核心原因推测 #

你遇到的问题大概率是以下两种情况之一：

1. 目标字段值包含视觉不可见的额外字符：比如零宽空格（U+200B）、控制字符、全角空格等，这些字符肉眼无法分辨，但会破坏严格等值匹配的逻辑。
2. 排序规则（Collation）的差异：数据库默认的排序规则可能对字符比较做了特殊处理，导致严格等值匹配失效，但宽松匹配（如大小写转换、模糊匹配）可以生效。

针对不同查询行为的解释 #

• 无法匹配的查询：

  • source_alias = 'store'：使用字段默认排序规则做严格字符匹配，只要字段值有任何额外字符（哪怕不可见）或排序规则区分细微差异，就会匹配失败。
  • lower(source_alias) = lower('store')：若额外字符在lower转换后仍然存在，或排序规则对lower后的字符串比较有特殊逻辑，同样无法匹配。
  • source_alias like 'store'：不带通配符的like等价于严格等值匹配，行为和=一致。

• 可以匹配的查询：

  • source_alias like '%store%'：只检查字段值是否包含store子串，不管前后是否有额外字符，因此能命中目标行。
  • upper(source_alias) = upper('store')：若排序规则对大写后的字符比较更宽松，或额外字符在upper转换后被忽略（极少见），就能匹配成功。
  • source_alias ilike 'store'：ilike本身不区分大小写，若排序规则同时忽略了某些不可见字符，就会匹配成功。
  • convert_to(source_alias, 'UTF8') = 'store'：直接做字节级比较，若额外字符是UTF-8编码下不影响字节匹配的特殊情况（或你的测试场景存在特殊逻辑），会返回匹配结果。

排查与验证步骤 #

1. 检查字段实际字节内容：

   -- 查看目标行的十六进制字节码
   select encode(convert_to(source_alias, 'UTF8'), 'hex') from source_aliases where source_alias like '%store%';
   -- 对比标准'store'的字节码
   select encode(convert_to('store', 'UTF8'), 'hex');
   

   若两者结果不一致，说明字段值存在额外字符。

2. 检查字段长度：

   select length(source_alias), char_length(source_alias) from source_aliases where source_alias like '%store%';
   

   标准store的长度是5，若结果大于5，说明有额外字符。

3. 测试排序规则影响：

   select * from source_aliases where source_alias = 'store' collate "C";
   

   若能查到结果，说明是默认排序规则导致的匹配差异。

解决方案 #

1. 清理不可见字符：
   若字段存在不可见控制字符，可通过正则替换清理：

   update source_aliases 
   set source_alias = regexp_replace(source_alias, '[\x00-\x1F\x7F-\x9F\u200B-\u200F]', '', 'g')
   where source_alias like '%store%';
   

2. 调整排序规则：
   若为排序规则问题，可修改字段的排序规则为不区分大小写/重音的类型：

   alter table source_aliases 
   alter column source_alias type text collate "und-x-icu" (case_insensitive=true);
   

   或在查询时临时指定排序规则：

   select * from source_aliases where source_alias = 'store' collate "en_US.UTF8"_ci;
   

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