不同數(shù)據(jù)庫(kù)對(duì)于字符值的排序規(guī)則各不相同，要達(dá)成在不同數(shù)據(jù)庫(kù)上對(duì)于同樣數(shù)據(jù)集執(zhí)行查詢語(yǔ)句的輸出結(jié)果順序一致性目標(biāo)，則必須進(jìn)行相應(yīng)的設(shè)置或改寫(xiě)，本文通過(guò)對(duì)五種數(shù)據(jù)庫(kù)的分析，對(duì)該問(wèn)題進(jìn)行了較為深入的分析。

01

概述.

在異構(gòu)數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移之后，為驗(yàn)證數(shù)據(jù)一致性，就需要比對(duì)源庫(kù)和目標(biāo)庫(kù)的同表數(shù)據(jù)是否一致。

為了提高比對(duì)效率，一般而言會(huì)將數(shù)據(jù)排序并抽取出來(lái)后進(jìn)行比對(duì)。

在實(shí)際過(guò)程中發(fā)現(xiàn)，指定了ORDER BY的同樣兩條SQL語(yǔ)句在不同數(shù)據(jù)庫(kù)執(zhí)行后，輸出結(jié)果集的順序經(jīng)常會(huì)不同，本文關(guān)注該問(wèn)題的產(chǎn)生并提供了相應(yīng)的解決方案。

02

數(shù)據(jù)準(zhǔn)備.

本文涉及的數(shù)據(jù)庫(kù)為：

• Oracle
• MySQL
• Postgres
• Gauss(華為open Gauss)
• GoldiLocks（科藍(lán)）

所有的數(shù)據(jù)庫(kù)均采用UTF8編碼，且MySQL數(shù)據(jù)庫(kù)不區(qū)分大小寫(xiě)建表。

在各數(shù)據(jù)庫(kù)中創(chuàng)建一張測(cè)試表LEXSORT，該表僅有一個(gè)字符列NAME，具體語(yǔ)句如下:

CREATE TABLE LEXSORT ( NAME VARCHAR(10) );

然后將以下數(shù)據(jù)插入該表中：

INSERT INTO LEXSORT VALUES ('0');
INSERT INTO LEXSORT VALUES ('9');
INSERT INTO LEXSORT VALUES ('a');
INSERT INTO LEXSORT VALUES ('z');
INSERT INTO LEXSORT VALUES ('A');
INSERT INTO LEXSORT VALUES ('Z');
INSERT INTO LEXSORT VALUES ('_');
INSERT INTO LEXSORT VALUES ('~');
INSERT INTO LEXSORT VALUES (NULL);

03

查詢結(jié)果.

在各個(gè)數(shù)據(jù)庫(kù)中執(zhí)行如下查詢語(yǔ)句：

SELECT * FROM LEXSORT ORDER BY NAME;

其輸出結(jié)果見(jiàn)下圖：

通過(guò)上面的結(jié)果可以發(fā)現(xiàn)：

其一，Oracle、Gauss和GoldiLocks的缺省排序保持一致，而與MYSQL和Postgres的各不相同。

其二，數(shù)據(jù)排序的不同體現(xiàn)在兩個(gè)方面上

• NULL值與非NULL字符值之間的順序
• 非NULL字符值之間的順序

那么，這背后的機(jī)制是什么呢？又該如何解決呢？

04

數(shù)據(jù)庫(kù)分析.

其實(shí)，產(chǎn)生這一現(xiàn)象的原因是各數(shù)據(jù)庫(kù)的缺省排序規(guī)則各不相同所致。要解決這一問(wèn)題，就需要從各數(shù)據(jù)庫(kù)自身出發(fā)，了解其排序規(guī)則，并分別進(jìn)行設(shè)置，才可能達(dá)到在不同數(shù)據(jù)庫(kù)之間的一致性。

具體如何操作，后文將為您逐一展開(kāi)。

Oracle數(shù)據(jù)庫(kù)

**Oracle數(shù)據(jù)庫(kù)提供了控制排序規(guī)則的參數(shù)，可以在系統(tǒng)級(jí)別和會(huì)話級(jí)別分別進(jìn)行設(shè)置，一般而言，為了不影響其他應(yīng)用，我們?cè)跁?huì)話級(jí)別進(jìn)行設(shè)置即可。

**

1. NULL值的排序規(guī)則

Oracle支持在ORDER BY字句的每個(gè)字段上進(jìn)行控制?？梢灾付镹ULLS FIRST或NULLS LAST，即NULL值排在前面還是后面，缺省為NULLS LAST，即NULL值排在其它非NULL值的后面。

Postgres、Gauss和GoldiLocks也采用了同樣的處理，后文不再贅述。

2. 非NULL值的排序規(guī)則

Oracle提供了控制參數(shù)NLS_SORT來(lái)指定排序規(guī)則，缺省的排序規(guī)則為BINARY，即按照字符串中每個(gè)字符的編碼值進(jìn)行排序，另一個(gè)常用排序規(guī)則為BINARY_CI，即按照二進(jìn)制值進(jìn)行排序，同時(shí)字母(A-Z，a-z)不區(qū)分大小寫(xiě)。

根據(jù)以上規(guī)則重新修改一下SQL語(yǔ)句或會(huì)話設(shè)置：

ALTER SESSION SET NLS_SORT=BINARY;
ALTER SESSION SET NLS_SORT=BINARY_CI;
SELECT * FROM LEXSORT ORDER BY NAME NULLS FIRST;

此時(shí)不同組合后查詢的輸出結(jié)果見(jiàn)下圖：

在上圖中我們會(huì)注意到，不區(qū)分大小寫(xiě)排序時(shí)字符“_”的位置似乎有些“飄忽不定”。為了解決這個(gè)問(wèn)題，我們把這些字符對(duì)應(yīng)的編碼數(shù)值出來(lái)看一下：

根據(jù)編碼值就會(huì)發(fā)現(xiàn)，“飄忽不定”的符號(hào)“_”的編碼正好位于大寫(xiě)字母和小寫(xiě)字母之間，與它存在同樣情況的還有5個(gè)字符。這就意味著，Oracle在采用BINARY_CI方式忽略字母大小寫(xiě)排序時(shí)，會(huì)自動(dòng)將所有的字母視為了小寫(xiě)字母。

MySQL數(shù)據(jù)庫(kù)

MySQL數(shù)據(jù)庫(kù)在排序控制方面較弱，首先對(duì)于NULL值，MySQL自動(dòng)視為NULLS FIRST，在ORDER BY字句中無(wú)相應(yīng)的控制選項(xiàng)。

再看一下字母的排序，MySQL在建表時(shí)可以指定區(qū)分大小寫(xiě)或不區(qū)分大小寫(xiě)，一旦指定無(wú)法再修改，除非重新建表。

因此對(duì)于區(qū)分大小寫(xiě)的庫(kù)，其排序規(guī)則會(huì)與Oracle的BINARY規(guī)則保持一致。

那么不區(qū)分大小寫(xiě)的呢？其實(shí)在前面的截圖中已經(jīng)有了體現(xiàn)，不過(guò)為了清晰起見(jiàn)，我們將Oracle設(shè)置為NULL FIRST和不區(qū)分大小寫(xiě)，單獨(dú)拿出來(lái)再進(jìn)行一下比較：

此時(shí)我們會(huì)發(fā)現(xiàn)Oracle和MySQL的排序依然不一致!發(fā)生問(wèn)題的依然是那個(gè)“飄忽不定”的“_”。

顯然，稍加分析后我們就會(huì)知道，在不區(qū)分大小寫(xiě)的情形下，MySQL自動(dòng)將所有字母視為了大寫(xiě)字母進(jìn)行排序，正是因?yàn)檫@個(gè)區(qū)別，位于大寫(xiě)和小寫(xiě)字母之間的那六個(gè)字符又一次給我們?nèi)橇寺闊?/strong>

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

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;