Oracle与Postgres含特殊字符字段排序结果不一致问题求解

跨Oracle和Postgres字符串排序一致的解决方案 #

两个数据库排序结果存在差异的核心原因是默认使用的本地化排序规则（collation）不同，导致特殊符号的排序优先级有区别，以下是可直接落地的解决方案：

• 方案1：统一使用二进制排序（最推荐，实现成本最低，结果确定性最高）
  二进制排序直接按照字符的ASCII编码值进行排序，不受数据库本地化规则影响，两个数据库的排序结果完全一致。
  Oracle侧修改排序语句：

  SELECT * FROM (
  SELECT '01.00 01.12' AS "COLUMN_VALUE" FROM DUAL
  UNION ALL
  SELECT '01.00^01.13' AS "COLUMN_VALUE" FROM DUAL
  UNION ALL
  SELECT '01.00 01.15' AS "COLUMN_VALUE" FROM DUAL) 
  ORDER BY NLSSORT("COLUMN_VALUE", 'NLS_SORT = BINARY');
  

  Postgres侧修改排序语句：

  SELECT * FROM (
  SELECT '01.00 01.12' AS "COLUMN_VALUE"
  UNION ALL
  SELECT '01.00^01.13' AS "COLUMN_VALUE" 
  UNION ALL
  SELECT '01.00 01.15' AS "COLUMN_VALUE") T1 
  ORDER BY "COLUMN_VALUE" collate "C";
  

  调整后两边排序结果会统一为：空格（ASCII 32）优先级高于^（ASCII 94），最终排序结果和原Postgres默认返回结果一致。
• 方案2：统一替换特殊字符后排序
  如果业务要求不能使用二进制排序，可以预先将两个库中会出现排序优先级差异的特殊字符，统一替换为提前确认过排序优先级一致的占位字符后再排序，示例逻辑：
  Oracle侧：ORDER BY REPLACE("COLUMN_VALUE", '^', '#')
  Postgres侧：ORDER BY REPLACE("COLUMN_VALUE", '^', '#')
• 方案3：导出数据后用外部工具统一排序对比
  不想修改数据库查询语句的情况下，可以将两个库的查询结果分别导出为纯文本文件，使用支持指定排序规则的外部工具对两个文件统一排序后再做逐行对比，也可以规避数据库排序规则差异的影响。

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