应该使用哪种数据类型来存储散列?

I understand that hashes will be different based on different datatypes in SQL Server. One support Unicode another not .... so on (also collation)

我知道，基于SQL Server中不同的数据类型，散列将是不同的。一个支持Unicode的另一个不是....等等(排序)

I am using char(32) as a datatype but the output is weird. Using this

我使用char(32)作为数据类型，但是输出是奇怪的。使用这个

select HASHBYTES('MD5','MD5Text')

gives this ouput:

给这个输出:

0xA891DB2DA259280A66FD5F35201CAB6A

and when

当

declare @h char(32)
select @h=HASHBYTES('MD5','MD5Text')
select @h,LEN(@h)

output:

输出:

Ё‘Ы-ўY( fэ_5 «j

Ё'Ы-ўY(fэ_5«j

So I am new to SQL Server.
Could anyone, please, tell me what datatype should I use to store hashes ??

所以我是SQL Server的新手。请问有谁能告诉我应该使用什么数据类型来存储散列吗?

1 个解决方案

#1

111

You should use the binary datatype. You can use binary instead of varbinary because the hash function will always return the same number of bytes for the same type of hash (e.g. MD5, SHA1, etc.). This will cut down on the (slight) overhead required to manage a variable length binary (varbinary) column.

您应该使用二进制数据类型。您可以使用二进制而不是varbinary，因为哈希函数总是返回相同类型哈希的相同字节数(例如MD5、SHA1等)。这将减少管理可变长度二进制(varbinary)列所需的(少量)开销。

In terms of what size to make it, you can run this query to check the length of each hash type:

至于它的大小，您可以运行这个查询来检查每个哈希类型的长度:

SELECT  DATALENGTH(HASHBYTES('MD2', 'Testing')) AS [MD2Length],
        DATALENGTH(HASHBYTES('MD4', 'Testing')) AS [MD4Length],
        DATALENGTH(HASHBYTES('MD5', 'Testing')) AS [MD5Length],
        DATALENGTH(HASHBYTES('SHA', 'Testing')) AS [SHALength],
        DATALENGTH(HASHBYTES('SHA1', 'Testing')) AS [SHA1Length],
        /* 2012 only: */
        DATALENGTH(HASHBYTES('SHA2_256', 'Testing')) AS [SHA2_256Length],
        DATALENGTH(HASHBYTES('SHA2_512', 'Testing')) AS [SHA2_512Length];

And it should come out with this:

它应该是这样的:

MD2Length MD4Length MD5Length SHALength SHA1Length SHA2_256Length SHA2_512Length
--------- --------- --------- --------- ---------- -------------- --------------
16        16        16        20        20         32             64

#1

111