哈希冲突比你想象的多

哈希函数是映射函数，它把输入的数据值经过一定的转换算法，映射成为新的数据值，哈希算法质量的好坏，是由产生的数据值的精确度决定的，理想的哈希函数有两个特性：对于同一个输入值，产生相同的哈希值；对于不同的输入值，产生不同的哈希值。对于不同的输入值，产生相同的哈希值，这就叫冲突，冲突越少，哈希算法的质量越高。SQL Server内置三个哈希函数，2个校验和函数（checksum 和 binary_checksum），以及一个冲突更少的哈希函数HashBytes，这三个函数都无法提供100%的精确度，如果业务逻辑要求不允许有误差，那么不要使用任何哈希函数，只要是哈希函数，就会存在冲突。

一，校验和

在一些数据表中，如果存在多个字符串字段，并且字符串非常长，在比较字符串是否相同时，使用校验和函数，将其转换成 int 类型，能够提高数据比较的速度。但是不要忽略官方文档的警告（Caution）：使用校验和函数，有时不能探测到数据的更新，除非应用程序能够容忍偶尔的丢失更新。一旦将校验和函数用于实际的项目中，你会发现，不是偶尔，而是经常会发生探测不到数据更新的异常：

[blockquote]

BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change.

[/blockquote]

由于存在侥幸心理，在项目中使用了校验函数对字符串字段进行比较，代码大致如下：url，why 和 description 字段都是挺长的字符串字段，特别是description，平均长度是1163字符。

;merge target as t 
using source as s 
    on t.id=s.id
when matched 
    and binary_checksum(t.url,t.why,t.description)
            <>binary_checksum(s.url,s.why,s.description)
....

大多数情况下，函数工作正常，但是，常在河边走，哪能不湿鞋，在一个周末，领导一个短信，我回来加班了，校验函数不能探测出更新的数据，导致数据同步出现问题，对ScoreCard和绩效影响较大，必须及时修改。

如图，Source 和 target 的Description 字段不一致，但是校验函数：binary_checksum(url,description,why) 返回的值是相同的，痛定思痛，为了避免以后的数据更新被异常丢失，决定在ETL中不再使用校验和函数，而是直接使用字符串进行比对。

校验和函数对长字符串产生的校验值质量不是很高，那对短的字符串了？经过多次尝试，发现校验函数在短的字符串上更容易出现更新丢失的情况，鉴于校验函数的这些缺点，强烈建议不要在项目中使用校验和函数。直接使用字符串进行比较，保证万无一失，相比数据更新出错，数据同步稍稍慢点，更能被容忍。

在比较长的字符串时，直接比较会比较慢，使用校验和比较速度会比较高，但是 binary_checksum 和 checksum 产生的校验和的质量比较低，建议不要再项目中使用校验函数。当字符串比较短时，很大可能产生Hash 冲突，例如：下面的脚本返回两行数据，每行的两个数据列结果相同，相比较而言，使用 checksum 比 binary_checksum效果更好。

--database collation is  SQL_Latin1_General_CP1_CI_AS
select checksum(N'us',N'FL',N'Land O Lakes'),checksum(N'us',N'FL',N'Land O'' Lakes')
select binary_checksum(N'gb',N'c6',N'bude'),binary_checksum(N'no',N'',N'myre')

二，HashBytes函数

HashBytes 函数使用不同级别的加密算法，能够产生高质量的哈希值，大幅度提高识别数据差异的准确性，但是HashBytes函数无法提供100%的准确度，如果业务逻辑要求不允许有误差，那么不要使用任何Hash 函数，只要是Hash函数，就会存在冲突。HashBytes 函数对于相同的文本，有时会产生不同的哈希值。

[blockquote]

When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

[/blockquote]

HashBytes 只能有一个输入参数，参数的数据类型是varchar、nvarchar 或 varbinary，并且输入字符的最大有效字节数量是8000Bytes，产生的哈希值跟输入值的数据类型相关。

1，指定Hash算法

使用 HashBytes需要指定算法，SQL Server内置7种计算HashValue的算法，推荐使用MD5来计算数据的差异。

　　HASHBYTES ( '<algorithm>', { @input | 'input' } ) 
　　<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

输入参数的数据类型为 varchar、nvarchar 或 varbinary，输入参数的有效字节数量最大是8000bytes。HashBytes函数的返回值的数据类型是 varbinary ，不同的算法，返回的字节长度是不同的。

[blockquote]

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.

[/blockquote]

三，影响HashBytes函数的因素

1，HashBytes函数受输入数据类型的影响

select HASHBYTES('SHA1',N'123abc') as HashNChar, HASHBYTES('SHA1','123abc') as HashChar;

2，HashBytes函数受输入字符大小写的影响

select hashbytes('SHA1','eric') as UpperCase,hashbytes('SHA1','Eric') as LowerCase

3，有效输入是8000Bytes

如果输入字符串的字节数量大于8000Bytes，那么HashBytes 把超过8000Bytes的字符舍弃，只对前8000Bytes进行Hash计算。SQL Server 不会提供任何提示或warning，在使用时，必须保证，输入字符串的字节数量不能超过8000。

如果是Unicode字符，那么输入字符串字符数量不能超过4000个；如果是varchar字符，那么输入字符串的数量不能超过8000个。

select hashbytes('SHA1',replicate('eric',2000)) ,hashbytes('SHA1',replicate('eric',4000))

参考文档:

HASHBYTES (Transact-SQL)