SQL Server 與 Oracle 中多字節字元的字節排序

我目前正在將數據從 Oracle 遷移到 SQL Server，並且在嘗試驗證遷移後的數據時遇到了問題。

環境細節：

• Oracle 12 - AL32UTF8 字元集
• 客戶端 - NLS_LANG - WE8MSWIN1252
• VARCHAR2 欄位

SQL 伺服器 2016

• Latin1_General_CI_AS collation
• NVARCHAR 欄位

我在 Oracle 上使用 DBMS_CRYPTO.HASH 生成整行的校驗和，然後復製到 SQL 並使用 HASHBYTES 生成整行的校驗和，然後進行比較以驗證數據匹配。

校驗和與所有行匹配，但具有多字節字元的行除外。

例如，具有以下字元的行： ◦ 在校驗和中不匹配，即使數據傳輸正確。當我在 Oracle 中使用 DUMP 或在 SQL Server 中轉換為 VARBINARY 時，數據完全匹配，但該字元的字節除外。

在 SQL Server 中，字節為 0xE625，在 Oracle 中為 0x25E6。

為什麼它們的順序不同，是否有可靠的方法將一個轉換為另一個以確保另一端的校驗和與多字節字元的字元串匹配？

NVARCHAR//列的排序規則與NCHAR用於NTEXT在該列中儲存數據的編碼無關。NVARCHAR數據始終為UTF-16 Little Endian (LE)。數據的整理NVARCHAR只影響排序和比較。排序規則確實會影響VARCHAR數據的編碼，因為排序規則確定了用於將數據儲存在該列/變數/文字中的程式碼頁，但我們在這裡不處理。

正如sepupic 所提到的，當您以二進制形式查看數據時，您看到的是字節序的差異（Oracle 使用 Big Endian，而 SQL Server 使用 Little Endian）。然而，當您在 Oracle 中查看二進制形式的字元串時，您看到的並不是數據的實際儲存方式。您使用AL32UTF8的是 UTF-8，它將該字元編碼為 3 個字節，而不是 2 個字節，如：E2, 97, A6.

此外，只有“a”的行的雜湊值不可能相同，但當它們包含“◦”時則不然，除非 Oracle 中的雜湊值是在沒有轉換的情況下完成的，因此使用 UTF-8 編碼，並且SQL Server 中的散列不小心轉換為VARCHAR第一個。否則，沒有雜湊算法會像您描述的那樣執行，您可以通過在 SQL Server 中執行以下命令來驗證：

DECLARE @Algorithm NVARCHAR(50) = N'MD4';
SELECT HASHBYTES(@Algorithm, 0x3100), HASHBYTES(@Algorithm, 0x0031);
SET @Algorithm = N'MD5';
SELECT HASHBYTES(@Algorithm, 0x3100), HASHBYTES(@Algorithm, 0x0031);
SET @Algorithm = N'SHA1';
SELECT HASHBYTES(@Algorithm, 0x3100), HASHBYTES(@Algorithm, 0x0031);
SET @Algorithm = N'SHA2_256';
SELECT HASHBYTES(@Algorithm, 0x3100), HASHBYTES(@Algorithm, 0x0031);
SET @Algorithm = N'SHA2_512';
SELECT HASHBYTES(@Algorithm, 0x3100), HASHBYTES(@Algorithm, 0x0031);

在 Oracle 中，您應該使用該CONVERT函式將字元串放入AL16UTF16LE編碼中，然後對該值進行雜湊處理。這應該與 SQL Server 所擁有的相匹配。例如，您可以看到White Bullet (U+25E6)的不同編碼形式以及如何在dbfiddle及以下內容中使用CONVERTwithAL16UTF16LE來糾正此問題：

SELECT DUMP(CHR(14849958), 1016) AS "UTF8",
      DUMP(CHR(9702 USING NCHAR_CS), 1016) AS "UTF16BE",
      DUMP(CONVERT(CHR(9702 USING NCHAR_CS), 'AL16UTF16LE' ), 1016) AS "UTF16LE"
FROM DUAL;

SELECT DUMP('a' || CHR(14849958), 1016) AS "UTF8",
      DUMP('a' || CHR(9702 USING NCHAR_CS), 1016) AS "UTF16BE",
      DUMP(CONVERT('a' || CHR(9702 USING NCHAR_CS), 'AL16UTF16LE' ), 1016) AS "UTF16LE"
FROM DUAL;

返回：

UTF8:     Typ=1 Len=3 CharacterSet=AL32UTF8: e2,97,a6
UTF16BE:  Typ=1 Len=2 CharacterSet=AL16UTF16: 25,e6
UTF16LE:  Typ=1 Len=2 CharacterSet=AL16UTF16: e6,25


UTF8:     Typ=1 Len=4 CharacterSet=AL32UTF8: 61,e2,97,a6
UTF16BE:  Typ=1 Len=4 CharacterSet=AL16UTF16: 0,61,25,e6
UTF16LE:  Typ=1 Len=4 CharacterSet=AL16UTF16: 61,0,e6,25

正如您在第 3 列中看到的那樣，當字元集基於兩個字節的順序明顯是 Little Endian 時，它被誤報為 Big Endian。您還可以看到，在 UTF-16 中這兩個字元都是兩個字節，並且它們的順序在Big Endian 和 Little Endian 之間是不同的，而不僅僅是 UTF-8 中大於 1 字節的字元。

鑑於所有這些，由於數據被儲存為 UTF-8，但您通過該DUMP函式將其視為 UTF-16 Big Endian，您似乎已經將其轉換為 UTF-16，但可能沒有意識到預設值Oracle 中的 UTF-16 是大端。

查看Oracle 文件詞彙表頁面上的“UTF-16”定義，它指出（我將以下句子分成兩部分，以便更容易區分 BE 和 LE）：

AL16UTF16 實現了 UTF-16 編碼形式的大端編碼方案（每個程式碼單元的更重要的字節在記憶體中首先出現）。AL16UTF16 是有效的國家字元集。

和：

AL16UTF16LE 實現 little-endian UTF-16 編碼方案。它是一個僅轉換字元集，僅在 SQLCONVERT或 PL/SQL等字元集轉換函式中有效UTL_I18N.STRING_TO_RAW。

PS 由於您AL32UTF8在 Oracle 中使用，因此您應該Latin1_General_100_CI_AS_SC在 SQL Server 中使用排序規則，而不是Latin1_General_CI_AS. 您使用的那個較舊並且不完全支持補充字元（如果存在，則不會失去數據，但內置函式將它們作為 2 個字元而不是單個實體處理）。

引用自：https://dba.stackexchange.com/questions/229103