SQL Server 2014 nvarchar(max)结果的哈希字节数为nvarchar

Question

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

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

消息8152，级别16，状态10，第4行
字符串或二进制数据将被截断。

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

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

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

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

似乎由于我正在执行hashbytes的列是nvarchar(max)，所以hashbytes函数的结果也是nvarchar(max)。

你能告诉我如何才能得到预期的20长的结果，而不是必须截断的长度吗？

Answer 1

似乎由于我正在进行哈希字节处理的字段是nvarchar（max），所以哈希字节的结果是nvarcher（max）。

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

允许的输入值限制为8000字节。输出符合算法标准：MD2、MD4和MD5为128位（16字节）；用于SHA和SHA1的160比特（20字节）；用于SHA2_256的256位（32字节）和用于SHA2_512的512位（64字节）。

这确实说明了一切：输入限制为8000字节，输出是基于指定算法的固定字节数。

SQL Server 2016的更新文档（已删除8000字节限制）指出：

对于SQL Server 2014及更早版本，允许的输入值限制为8000字节。

你可以运行一个简单的测试：

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

Answer 2

在SQL Server 2016中，我们不再存在HASHYTES函数的输入参数长度问题。

DECLARE @Test NVARCHAR(MAX); 
SET @Test = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000000);
SELECT LEN(@Test);
SELECT HASHBYTES('SHA2_512', @Test);

HASHYTES（Transact-SQL）

Answer 3

如果您试图转换sql中已经存在的大型varbinary或image文件，那么有一些内置函数可以做到这一点（可能从2014年起），这个简单的函数将适用于varbinary（max）和旧的image字段。。

/****** Object:  UserDefinedFunction [dbo].[MD5Bin]    Script Date: 16/07/2018 11:04:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author:    Darren Steven
-- Create date: 16/07/2018
-- Description: Hashes a binary or image field with MD5
-- ==================================================
CREATE FUNCTION [dbo].[MD5Bin](@value varbinary(max))
RETURNS varchar(32)
AS
BEGIN
  RETURN SUBSTRING(master.sys.fn_sqlvarbasetostr(master.sys.fn_repl_hash_binary(@value)),3,32);
END
GO

然后简单地用您的选择调用函数：

SELECT dbo.MD5Bin(imageFieldName) from dbo.yourTable 

Answer 4

SQL 2016 中删除了HASHBYTES（Transact-SQL）函数8000字节的输入长度限制

基于以下算法的是输出数据大小用于MD2、MD4和MD5的128位（16字节）；用于SHA和SHA1的160比特（20字节）；SHA2_256的256位（32字节）512位（64字节）。