PostgreSQL中citext类型下LIKE与'='查询结果不一致问题

核心原因推测：Unicode字符等价性差异 #

citext类型的=匹配依赖PostgreSQL的字符排序规则（collation）做语义等价判断，而LIKE是逐字节/逐字符的字面匹配——这就导致视觉上完全相同的字符串，可能因为底层Unicode编码不同，出现两种匹配结果不一致的情况。

具体排查步骤 #

1. 对比字符串的原始字节编码
   直接查看数据库中目标值和查询字面量的十六进制字节，能精准定位差异：

-- 获取数据库中匹配LIKE的字段字节码
SELECT encode(my_field::bytea, 'hex') AS field_hex FROM my_table WHERE my_field LIKE 'ABC-123a';

-- 获取查询字面量的字节码
SELECT encode('ABC-123a'::bytea, 'hex') AS literal_hex;

常见的坑：字符串中的-不是普通短横线（U+002D），而是EN Dash（U+2013）或EM Dash（U+2014），视觉上几乎一致但编码完全不同，=会判定为不同字符，而LIKE因字面匹配会命中。

2. 检查citext字段的排序规则
   citext的匹配行为受字段绑定的collation影响，部分collation会将特定字符视为等价，部分则不会。先查询字段的collation：

SELECT collation_name 
FROM information_schema.columns 
WHERE table_name = 'my_table' AND column_name = 'my_field';

如果不是默认的C或en_US.utf8，可以尝试强制用严格字节匹配测试：

SELECT my_field FROM my_table WHERE my_field = 'ABC-123a' COLLATE "C";

若这个查询能返回结果，说明原collation的等价规则导致了匹配失败。

3. 排查隐形控制字符
   零宽空格（U+200B）、软连字符（U+00AD）这类隐形控制字符不会被TRIM识别，但会占据字节位置，导致=匹配失败但LIKE（无通配符）因逐字符匹配命中。可以用以下语句排查：

-- 检查是否包含控制字符
SELECT my_field FROM my_table WHERE my_field ~ '[\x00-\x1F\x7F-\x9F]';

-- 对比字符长度和字节长度（不一致说明存在非单字节隐形字符）
SELECT char_length(my_field) AS char_len, length(my_field::bytea) AS byte_len 
FROM my_table WHERE my_field LIKE 'ABC-123a';

解决方法 #

• 若为字符编码差异：找到差异字符后更新字段为正确编码，比如把EN Dash替换成普通短横线：

UPDATE my_table SET my_field = replace(my_field, E'\u2013', '-') WHERE my_field LIKE 'ABC-123a';

• 若为collation问题：可以修改字段的collation为更严格的类型，或者查询时显式指定collation。

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