主页 / dba / 问题 / 229103
Accepted
HandyD
Asked: 2019-02-07 19:12:28 +0800 CST

SQL Server 与 Oracle 中多字节字符的字节排序

  • 772

我目前正在将数据从 Oracle 迁移到 SQL Server，并且在尝试验证迁移后的数据时遇到了问题。

环境细节：

  • Oracle 12 - AL32UTF8 字符集
  • 客户端 - NLS_LANG - WE8MSWIN1252
  • VARCHAR2 字段

SQL 服务器 2016

  • Latin1_General_CI_AS 排序规则
  • NVARCHAR 字段

我在 Oracle 上使用 DBMS_CRYPTO.HASH 生成整行的校验和，然后复制到 SQL 并使用 HASHBYTES 生成整行的校验和，然后进行比较以验证数据匹配。

校验和与所有行匹配，但具有多字节字符的行除外。

例如，具有以下字符的行： ◦ 在校验和中不匹配，即使数据传输正确。当我在 Oracle 中使用 DUMP 或在 SQL Server 中转换为 VARBINARY 时，数据完全匹配，但该字符的字节除外。

在 SQL Server 中，字节为 0xE625，在 Oracle 中为 0x25E6。

为什么它们的顺序不同，是否有可靠的方法将一个转换为另一个以确保另一端的校验和与多字节字符的字符串匹配？

sql-server oracle

2 个回答

  1. Best Answer

    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 个字符而不是单个实体处理）。

    • 5

  2. 您所看到的是用于存储字符的Little-Endian编码（更准确地说，它使用）。SQL ServerUnicodeUCS-2 LE

    更多Little-Endian信息：Big Endian 和 Little Endian 字节顺序之间的区别

    我不知道怎么可能

    当我在 Oracle 中使用 DUMP 或在 SQL Server 中转换为 VARBINARY 时，数据完全匹配，但该字符的字节除外

    Unicode存储在SQL Server、 转换为的所有binary字符都是“反转的”，我的意思是，要查看真正的代码，您应该将它们分成 2 组bytes并反转每对中的顺序。

    例子：

    declare @str varchar(3) = 'abc';
declare @str_n nvarchar(3) = N'abc';

select cast(@str as varbinary(3));
select cast(@str_n as varbinary(6));

    结果是

    0x616263

0x610062006300

    如您所见，在Unicode字符字节被反转的情况下：“a”表示为0x6100而不是表示为0x0061.

    同样的故事是关于0x25E6那是真实的Unicode代码，而binary在SQL Server你看来它是真实的代码0xE625，即inverted。

    • 4

相关问题