PostgreSQL存在的数据无法查询的异常问题求助

核心原因及处理方法： #

• 隐藏不可见字符
  表面相同的字符串，很可能藏着空格、换行、零宽度空格这类肉眼看不到的字符，导致匹配失败。用下面的SQL对比字节长度就能发现问题：

  -- 查看目标记录的字符长度和字节长度
  SELECT id, account_token, length(account_token) AS char_count, octet_length(account_token) AS byte_count FROM accounts WHERE id=2;
  -- 对比你用来查询的字符串的字节长度
  SELECT octet_length('cP3rqjN6G9bE1ixCkRoRWLjo') AS query_byte_count;
  

  如果两个字节数不一样，肯定是有隐藏字符。直接用正则清理：

  UPDATE accounts SET account_token = regexp_replace(account_token, '\s|\x00-\x1F|\x7F|\x{200B}', '', 'g') WHERE id=2;
  

• 索引数据不一致
  唯一索引也可能因为异常关机、磁盘问题出现数据与索引不匹配的情况。重建索引就能解决：

  REINDEX INDEX index_accounts_on_account_token;
  -- 嫌麻烦直接整表重建索引
  REINDEX TABLE accounts;
  

• 事务隔离导致的可见性问题
  如果查询是在未提交的事务里执行的，可能看不到最新数据。换个新会话重试，或者查下当前事务状态：

  SELECT txid_current(), txid_current_snapshot();
  

• 字符编码不匹配
  应用连接的字符编码和数据库存储编码不一致，也会导致字符串匹配失效。检查编码：

  -- 数据库编码
  SELECT datname, encoding FROM pg_database WHERE datname = current_database();
  -- 当前连接编码
  SHOW client_encoding;
  

  不一致的话调整连接编码，比如数据库是UTF8就执行：

  SET client_encoding = 'UTF8';
  

预防措施 #

• 字符串入库前统一做清洗，过滤掉不可见字符；
• 服务器异常重启后，检查关键索引的健康状态；
• 确保应用连接数据库时使用和数据库一致的字符编码。

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