Varchar字段用=查询无结果但LIKE可匹配的原因及解决方法

• 90%以上的这类场景都是code字段存储的值中包含不可见非打印控制字符：这类字符没有可视化字形，不管是直接看查询结果、复制粘贴到Excel都不会显示，常规排查前后空格的方法根本检测不到。常见的隐藏字符包括换行符\n、回车符\r、制表符\t、零宽空格、UTF-8 BOM头、零宽连接符等。
• =做精确匹配时要求两个字符串逐字符完全一致，值里多一个隐藏字符就会匹配失败；而LIKE '%2NLX%'是模糊匹配规则，只要求字符串中存在连续的2NLX子串即可，不管子串前后带什么隐藏字符都能命中，所以会出现你遇到的精确匹配查不到、模糊匹配能查到的现象。
• 剩余不到10%的可能是字段关联的排序规则（Collation）配置异常，比如配置了自动忽略特定特殊字符的规则，但这类情况一般不会出现=和LIKE行为完全背离的表现，优先级远低于隐藏字符问题。

1. 先确认字段实际长度验证隐藏字符存在：执行如下语句，看返回的长度值：

   SELECT id, code, length(code) AS char_length, octet_length(code) AS byte_length 
   FROM 你的表名 
   WHERE id = 9135;
   

   正常2NLX的字符长度、字节长度都是4，如果返回结果大于4，就可以确定存在额外隐藏字符。
2. 定位具体隐藏字符：把字段值转为十六进制查看实际存储字节，正常2NLX对应的十六进制值为324e4c58，如果返回结果更长，多出来的字节段就是隐藏的字符，对照编码表即可确认具体字符类型：

   SELECT encode(code::bytea, 'hex') AS actual_hex_value
   FROM 你的表名 
   WHERE id = 9135;
   

• 临时查询适配：如果只是单次查询需要命中对应数据，可以先过滤掉字段里的非打印字符再做精确匹配：

  SELECT * FROM 你的表名 
  WHERE regexp_replace(code, '[^[:print:]]', '', 'g') = '2NLX';
  

• 修复存量脏数据：执行更新语句清理全表存量数据里的不可见字符：

  UPDATE 你的表名 
  SET code = regexp_replace(code, '[^[:print:]]', '', 'g')
  WHERE code ~ '[^[:print:]]';
  

• 长期预防：修复完存量数据后，可以给字段加检查约束，从数据库层面阻止带非打印字符的脏数据写入；同时业务侧写入数据前也加一层字符校验，过滤掉非法控制字符：

  ALTER TABLE 你的表名 
  ADD CONSTRAINT ck_code_valid CHECK (code !~ '[^[:print:]]');
  

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