sql-server - SQL Server 2014 nvarchar(max) 结果的哈希字节为 nvarchar(max)

Question

使用 SQL Server 2014，我有一个表，其中有 nvarchar(max)名为 [ASCII File] 的列它可以包含许多 K 的 ASCII 文本文件。然后我想对该文件执行 MD5 哈希字节，并且生成的哈希值应始终为 20 字节。

好吧，当我选择 hashbytes('MD5', [ASCII File]) 时我的查询已完成，但有错误

Msg 8152, Level 16, State 10, Line 4
String or binary data would be truncated.

当我尝试时，我收到同样的消息

left(hashbytes('MD5', [ASCII File]), 50)

当我尝试时，我收到同样的消息

convert(varchar(50), hashbytes('MD5', [ASCII File]))

似乎因为我正在执行哈希字节的列是 nvarchar(max) ，hashbytes 函数的结果也是 nvarchar(max) .

你能告诉我如何才能使结果达到预期的 20 长，而不是太长以至于必须被截断吗？

Answer 1

It seems like since the field I am doing the hashbytes on is nvarchar(max) the result of the hashbytes is nvarchar(max).

不，这是不可能的，特别是因为返回值 HASHBYTES是一个 VARBINARY。另外，由于您的测试只是 SELECT 语句而不是 INSERT 语句，因此返回值无法获得截断错误。截断错误来自输入值。如 HASHBYTES 的链接 MSDN 页面所述(适用于 SQL Server 2012 和 2014):

Allowed input values are limited to 8000 bytes. The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

这确实说明了一切:输入限制为 8000 字节，输出是固定数量的字节，具体取决于指定的算法。

SQL Server 2016 的更新文档(已删除 8000 字节限制)指出:

For SQL Server 2014 and earlier, allowed input values are limited to 8000 bytes.

您可以运行一个简单的测试:

DECLARE @Test NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000);
SELECT LEN(@Test);
SELECT HASHBYTES('MD5', @Test);

返回:

50000

Msg 8152, Level 16, State 10, Line 3
String or binary data would be truncated.

如果要在 2016 年之前的 SQL Server 版本中向哈希函数传递超过 8000 个字节，则需要使用 SQLCLR。您可以编写自己的函数，也可以下载并安装 SQL# 的免费版本。 SQLCLR 库(我创建的)，并使用 Util_Hash 和 Util_HashBinary 函数:

DECLARE @Test NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000);
SELECT LEN(@Test);
SELECT SQL#.Util_Hash('MD5', CONVERT(VARBINARY(MAX), @Test));
SELECT SQL#.Util_HashBinary('MD5', CONVERT(VARBINARY(MAX), @Test));

返回:

50000
40752EB301B41EEAEB309348CE9711D6
0x40752EB301B41EEAEB309348CE9711D6

<小时/>

更新

如果使用 VARCHAR(MAX) 列或变量，但字符数不超过 8000 个(或者 NVARCHAR(MAX) 列或变量，字符数不超过 4000 个字符)，不会有任何问题，一切都会按预期进行:

DECLARE @Test VARCHAR(MAX) = REPLICATE('t', 5000);
SELECT LEN(@Test) AS [Characters], 
       HASHBYTES('MD5', @Test) AS [MD5];

返回:

5000    0x6ABFBA10B49157F2EF8C85862B6E6313