将SQL Server的HashBytes varbinary转换为Bigint

Question

我看到了几篇博客，介绍如何在生成一组值的哈希时将HashBytes的结果转换为bigint。这在数据仓库中似乎非常常见。这样做的优点是将哈希存储为整数，非常适合联接，并提供良好的分区分布。

不过我想知道这是否是个好习惯。已经有人提出，使用HashBytes生成Sha1会产生一个16字节的结果。由于bigint是一个8字节的数据类型，这是否意味着需要将16字节缩减为8字节，然后再将其转换为bigint？如果是这样的话，这似乎会增加碰撞的可能性。

我们已经将我们的varbinary哈希转换为bigint很长一段时间了，但还没有遇到碰撞的情况，但如果上述假设是正确的，这似乎只是愚蠢的运气。

为了测试这个问题，我尝试了一些哈希转换，看能否从bigint中恢复原始哈希，但却无法这样做，这可能表明存在问题（或者我进行了错误的转换）。

您的数学专家对此有何想法吗？ 提前致谢！

DECLARE @value varchar(5) = '12345'
DECLARE @hash varbinary(max) = hashbytes('SHA1', @Value)

SELECT @hash AS OriginalHash
SELECT CAST(@hash as nvarchar(max)) AS StringHash
SELECT CAST(CAST(@hash as nvarchar(max)) as varbinary(max)) AS StringBackToOriginalHash
SELECT CAST(@hash as bigint) AS BigIntHash
SELECT CAST(CAST(@hash as bigint) as varbinary(max)) AS BigIntBackToOriginalHash

最近，SQL 2016 表示他们将废弃旧的哈希算法，我们将不得不使用更长的哈希算法 Sha2_256 和 Sha2_512，这将占用更多的空间。如果 bigint 具有与旧的 Sha1 哈希算法相同的抗冲突能力，那将是另一个使用 bigint 的好处。

- Geekn

Answer 1

实际上，我认为我在这篇文章中找到了答案。 https://blogs.msdn.microsoft.com/sqlcat/2005/09/16/using-hashing-to-obscure-sensitive-data/

2) 如果你要处理数百万个社会安全号码（SSN）值，那么使用从截断哈希构建的bigint可能会产生不可接受的哈希冲突风险。可以使用估算公式计算哈希冲突的可能性，假设给定2^(#bits/2)个输入值，则存在50％的冲突概率。对于使用7个字节计算的bigint，这意味着如果您哈希2^28个不同的值-或者268百万个不同的SSN，那么就有50％的冲突概率。因此，如果您的输入值宇宙很大，您可能需要使用完整的哈希字符串，或将更长的子字符串转换为十进制值。

- Geekn

Answer 2

hashbytes函数返回的字节数超过8个字节，但我们可以使用多个bigint列来存储完整的哈希信息。

以md2为例，可以使用两个bigint列存储16个字节。请注意，第二个查询结果等于第四个查询结果。

SELECT HASHBYTES('md2 ', '0')

SELECT cast(HASHBYTES('md2 ', '0') as bigint)

SELECT cast( cast(left(HASHBYTES('md2 ', '0'), 8)as varbinary(16)) as bigint)

SELECT cast( cast(right(HASHBYTES('md2 ', '0'), 8)as varbinary(16)) as bigint)

- Xu Shaoyang