不同的数据库对字符值有不同的排序规则。为了达到不同数据库上同一数据集执行查询语句的输出结果顺序一致的目的，必须进行相应的设置或重写。本文分析了五个数据库，对这个问题进行了较为深入的分析。

01

概述。

异构数据库之间进行数据迁移后，为了验证数据一致性，需要比较源数据库和目标数据库中同一张表的数据是否一致。

为了提高比较效率，一般在比较之前会对数据进行排序和提取。

在实际过程中，我们发现，在不同的数据库中执行相同的两条指定ORDER BY的SQL语句后，输出结果集的顺序往往是不同的。本文主要针对该问题的发生并提供相应的解决方案。

02

数据准备。

本文涉及的数据库有：

OracleMySQLPostgresGauss（华为开放高斯）GoldiLocks（科蓝）所有数据库均采用UTF8编码，MySQL数据库建表时不区分大小写。

在每个数据库中创建一个测试表LEXSORT。该表只有一个字符列NAME。具体说法如下：

CREATE TABLE LEXSORT ( NAME VARCHAR(10) );然后将以下数据插入表中：

插入LEXSORT 值（'0'）；插入LEXSORT 值（'9'）；插入LEXSORT 值（'a'）；插入LEXSORT 值（'z'）；插入LEXSORT 值（'A'）；插入LEXSORT 值（'Z'）；插入LEXSORT 值（'_'）；插入LEXSORT 值（'~'）；插入LEXSORT 值（NULL）；

03

查询结果。

在每个数据库中执行以下查询语句：

SELECT * FROM LEXSORT ORDER BY NAME;输出结果如下图所示：

从上面的结果我们可以发现：

首先，Oracle、Gauss和GoldiLocks的默认排序保持一致，但与MYSQL和Postgres不同。

其次，数据排序的差异体现在两个方面：

NULL值和非NULL字符值之间的顺序非NULL字符值之间的顺序那么，这背后的机制是什么呢？怎么解决呢？

04

数据库分析。

其实造成这种现象的原因是各个数据库默认的排序规则不同。要解决这个问题，我们需要从每个数据库本身出发，了解它的排序规则，并分别进行设置。只有这样才能实现不同数据库之间的一致性。

具体如何操作将在后面的文章中一一讲解。

Oracle数据库

**Oracle数据库提供了控制排序规则的参数，可以在系统级别和会话级别分别进行设置，一般而言，为了不影响其他应用，我们在会话级别进行设置即可。

**

1. NULL值的排序规则

Oracle 支持对ORDER BY 子句的每个字段进行控制。可以指定为NULLS FIRST 或NULLS LAST，即NULL 值排在第一位还是最后一位。默认为NULLS LAST，即NULL 值排在其他非NULL 值后面。

Postgres、Gauss和GoldiLocks也采用同样的流程，后面不再详细介绍。

2. 非NULL值的排序规则

Oracle提供了控制参数NLS_SORT来指定排序规则。默认的排序规则是BINARY，即根据字符串中每个字符的编码值进行排序。另一种常用的排序规则是BINARY_CI，它根据二进制值和字母进行排序。 (A-Z、a-z) 不区分大小写。

按照上述规则重新修改SQL语句或会话设置：

ALTER SESSION SET NLS_SORT=BINARY;ALTER SESSION SET NLS_SORT=BINARY_CI;SELECT * FROM LEXSORT ORDER BY NAME NULLS FIRST;不同组合后查询的输出结果如下图所示：

上图中，我们会注意到，在不区分大小写排序时，字符“_”的位置似乎有些“不稳定”。为了解决这个问题，我们先看一下这些字符对应的编码值：

根据编码值，你会发现“飘忽不定”的符号“_”的编码正好介于大写字母和小写字母之间，同样情况的还有其他5个字符。这意味着当Oracle使用BINARY_CI方法忽略大小写字母时，它会自动将所有字母视为小写字母。

MySQL数据库

MySQL数据库在排序控制方面较弱。首先，对于NULL值，MySQL自动将它们视为NULLS FIRST，并且ORDER BY子句中没有相应的控制选项。

我们再看一下字母的排序。 MySQL在创建表时可以指定区分大小写或不区分大小写。一旦指定，就无法修改，除非重建表。

因此，对于区分大小写的库，排序规则将与Oracle的BINARY规则一致。

那么不区分大小写的呢？其实在之前的截图中已经有所体现，不过为了清晰起见，我们将Oracle设置为NULL FIRST且不区分大小写，单独拿出来对比：

此时我们会发现Oracle和MySQL的排序仍然不一致！问题依然是“飘忽不定”的“_”。

显然，稍微分析一下我们就会知道，在不敏感的情况下，MySQL会自动将所有字母都视为大写字母进行排序。正是因为这个区别，大小写字母之间的六个字符又给我们带来了麻烦。

这样一来，MySQL数据库和建表不区分大小写的Oracle数据库之间的排序一致性还没有完美的解决方案！

Postgres数据库

Postgres 数据库的默认排序对我来说一直是个谜.

上图中，符号在前面，但“~”的代码比“_”大，相当于降序排列；然后是数字和字母，这次是升序。鉴于我对Postgres的研究有限，这里暂时不深究。我只关注如何解决排序一致性问题。

Postgres提供了collate语句来调整排序规则。将排序规则设置为C（必须用双引号括起来，并且必须是大写）或ucs_basic（如果用双引号括起来，必须是小写）意味着根据字符编码进行排序，这将区分大小写。

不区分大小写，必须按照编码值排序。目前尚未找到合适的方法。

首先指定collate和null时需要注意SQL语句的顺序。当两者都需要指定时，示例语句如下。具体输出结果大家可以自行测试：

SELECT * FROM LEXSORT ORDER BY NAME COLLATE ucs_basic NULLS FIRST;

Gauss数据库

大家都知道Open Gauss其实是基于Postgres定制的。它添加了一些功能，同时也删除了一些Postgres 功能。不过，Gauss 仍然保留了Postgres 对于ORDER BY 子句的能力，这意味着collate 子句也适用于Gauss 数据库，但Gauss 数据库默认的排序规则是根据字符编码值排序。

同时Gauss数据库提供了排序函数NLSSORT，解决了大小不敏感的排序问题。此时排序结果与Oracle一致。使用该功能时，需要指定排序规则。不区分大小写的规则是generic_m_ci。具体SQL示例语句如下：

SELECT * FROM LEXSORT ORDER BY NLSSORT(NAME,'nls_sort=generic_m_ci');SELECT * FROM LEXSORT ORDER BY NLSSORT(NAME,'nls_sort=generic_m_ci') NULLS FIRST;几种不同组合的查询结果如下图所示（未指定null 首先是nulls 最后）：

****金锁数据库****

除了与Oracle一致的NULLS FIRST/LAST处理外，该数据库没有可以修改排序规则的参数。但是，它的默认排序规则是根据字符编码值排序。因此，在排序一致性方面与Oracle、Postgres、Gauss还是非常兼容的。

05

摘要。

虽然本文起源于数据对比场景，但通过上面的分析，我们可以意识到排序一致性问题也是异构数据库迁移时必须考虑的问题之一。试想一下，如果不修改SQL语句，那么原业务查询语句的结果集在新数据库中的排序可能会发生变化，从而可能导致后续处理结果发生变化。

通过分析，我们还发现大多数数据库的排序一致性可以通过设置会话参数或修改SQL语句来保持不变。然而，有些数据库，比如本例中的MySQL，缺乏完美的解决方案，因此我们必须对其影响进行分析和解决。