SQL 应该使用哪种数据类型来存储哈希？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14722305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What kind of datatype should one use to store hashes?
提问by Suhrob Samiev
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 的新手。
谁能告诉我应该使用什么数据类型来存储哈希？
回答by Richard Marskell - Drackir
You should use the binarydatatype. You can use binaryinstead of varbinarybecause 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.
您应该使用binary数据类型。您可以使用binary，而不是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
