为什么 checksum() 为不同的字符串返回相同的值 [英] Why checksum() returns the same value for different string

问题描述

以下 SQL 及其结果显示不同的字符串得到相同的 checksum 结果.为什么?

选择 str ,binary_checksum(str) binary_checksum，校验和(str)校验和，hashbytes('md5', str) md5来自(值('2Volvo Director 20')，('3Volvo Director 30')，('4Volvo Director 40'))t ( 字符串 )

<前>str binary_checksum 校验和 md5------------------ --------------- ----------- -------------------------------------2沃尔沃导演20 -1356512636 -383039272 0xB9BD78BCF70FAC36AF14FFF5897672783沃尔沃导向器 30 -1356512636 -383039272 0xF039462F3D15B162FFCDB6125D2908264沃尔沃导向器 40 -1356512636 -383039272 0xFAF315CDA6E453CCC09838CFB129EE74

解决方案

SQL CHECKSUM() 和 MD5 是 散列函数.散列是一种单向算法，它可以采用任意数量的字符/字节并返回固定数量的字符/字节.

这意味着无论您输入的是 1 个字符还是一本书(战争与和平)，您都会得到相同长度的回复.所以输入是无限的组合，而输出是有限的.基于此，对于不同的值获得相同的哈希是不可避免的.它被称为哈希冲突.好的 Hash 算法会尝试减轻这种情况，从而很难找到这些冲突值.

但是关于散列的理论已经足够了.这正是您问题的答案.什么是 CHECKSUM() 问题?

The following SQL and its result shows the different string got the same checksum result. Why?

select  str ,
        binary_checksum(str) binary_checksum,
        checksum(str) checksum,
        hashbytes('md5', str) md5
from    ( values ( '2Volvo Director 20'), ( '3Volvo Director 30'), ( '4Volvo Director 40') ) 
        t ( str ) 

str                binary_checksum checksum    md5
------------------ --------------- ----------- --------------------------------------------
2Volvo Director 20 -1356512636     -383039272  0xB9BD78BCF70FAC36AF14FFF589767278
3Volvo Director 30 -1356512636     -383039272  0xF039462F3D15B162FFCDB6125D290826
4Volvo Director 40 -1356512636     -383039272  0xFAF315CDA6E453CCC09838CFB129EE74

解决方案

SQL CHECKSUM() and MD5 are Hash functions. Hashing is a one way algorithm which can take any number of chars/bytes and return a fixed number of chars/bytes.

It mean no matter if your input is 1 character or a complete book (War and Peace) you will get back the same length of response. So the input is infinite number of combinations meanwhile the output is finite. Based on that it is inevitable to get the same Hash for different values. It is called Hash collision. Good Hash algorithms try to mitigate this to make it hard to find these colliding values.

But enough theory about hashing. Here is exactly the answer to your question. What is the issue with CHECKSUM()?

这篇关于为什么 checksum() 为不同的字符串返回相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！