sql-server - SQL Server 和 PostgreSQL 之间的 MD5 值不匹配

Question

为了编写一些代码来对存储在SQL Server 和PostgreSQL 中的数据进行一致性检查，我打算对两个数据库的表数据进行MD5 计算，并验证它们是否相等。只要数据是纯文本 (ANSI) 就可以正常工作，如下所示:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', 'a'));
0x0cc175b9c0f1b6a831c399e269772661


postgres=# select MD5('a');
0cc175b9c0f1b6a831c399e269772661

现在，如果我尝试使用一些韩语(韩语)字符，MD5 匹配失败:

sql-server> SELECT master.dbo.fn_varbintohexstr(HashBytes('MD5', '무'));
0x7827b52f65d9f7777d37071cbbbf7f2d


postgres=# select MD5('무');
cb3e9be1a3a28b355eabae1fa1e291b3

据我了解，不匹配的原因是 unicode 字符在 SQL Server 中存储为 UCS-2 编码(固定 16 位编码)，在 PostgreSQL 中存储为 UTF-8 编码。由于 MD5 作用于字符位，因此 SQL Server 和 PostgreSQL 中的字符位序列会有所不同。

由于我主要处理韩文字符集，我在 PostgreSQL 中使用的解决方法是在计算哈希之前将编码从 UTF-8 转换为 UHC(通用韩文字符集)，如下所示:

postgres=# select MD5(CONVERT('무'::bytea,'UTF8','UHC'));
7827b52f65d9f7777d37071cbbbf7f2d

如您所见，上面的哈希值与 SQL Server 的哈希值相同。

只要我处理的是韩文字符，一切都很好。但是有些表格包含韩文和汉字的混合，在这种情况下转换失败:

postgres=# select MD5(CONVERT('무么'::bytea,'UTF8','UHC'));
ERROR:  character 0xe4b988 of encoding "UTF8" has no equivalent in "UHC"
postgres=# 

这个错误是有道理的，因为在 UHC 字符集中没有对应的汉字。

我怎样才能让它发挥作用？基本上，我需要找到在 SQL Server 中将 UCS-2 转换为 UTF-8 的方法，或者在计算 MD5 之前将 UTF-8 转换为 PostgreSQL 中的 UCS-2。我想在数据库引擎中执行所有这些操作，而不是在外部应用程序中加载数据来计算 MD5，因为有些表有巨大的数据集。

SQL 服务器版本 2005PostgreSQL 版本 9.1

Answer 1

不幸的是，PostgreSQL 也不支持 UTF-16/UCS-2。

但是，您可以编写一个函数，将 utf8 text 转换为 ucs2 二进制数据 (bytea):

create or replace function text_to_ucs2be(input_in_utf8 text)
  returns bytea
  immutable
  strict
  language sql
as $$
  select decode(string_agg(case
           when code_point < 65536
           then lpad(to_hex(code_point), 4, '0')
         end, ''), 'hex')
  from   regexp_split_to_table(input_in_utf8, '') chr,
         ascii(chr) code_point
$$;

create or replace function text_to_ucs2le(input_in_utf8 text)
  returns bytea
  immutable
  strict
  language sql
as $$
  select decode(string_agg(case
           when code_point < 65536
           then lpad(to_hex(code_point & 255), 2, '0')
             || lpad(to_hex(code_point >> 8), 2, '0')
         end, ''), 'hex')
  from   regexp_split_to_table(input_in_utf8, '') chr,
         ascii(chr) code_point
$$;

注意:上面的这些函数将去除任何非 BMP 代码点(因此名称为 ucs2)。

下面的语句应该给你相同的结果:

-- on PostgreSQL
select md5(text_to_ucs2le('무'));

-- on SQL server
select master.dbo.fn_varbintohexstr(HashBytes('MD5', N'무'));