将Varbinary转换为Char，并再次转换为主键的Varbinary

我发现了一个sql代码，它使用hashbytes函数和md5算法创建主键。代码如下：

SELECT
CONVERT(VARBINARY(32),
CONVERT( CHAR(32),
HASHBYTES('MD5', 
(LTRIM(RTRIM(COALESCE(column1,'')))+';'+LTRIM(RTRIM(COALESCE(column2,''))))
),
2)
) 
FROM database.schema.table

当我们直接从hashbytes函数获得varbinary时，我发现很难理解hashbytes函数转换为char然后转换为varbinary的结果是什么。有什么好的理由这样做吗？

短版本

这段代码用0x20字节填充了一个散列，这很奇怪，很可能是由于最初作者的误解。无论如何，使用散列作为密钥是一个糟糕的想法

长版本

哈希完全不适合生成主键。事实上，由于相同的哈希可以从不同的原始数据中生成，因此保证此代码生成重复的值，最多会导致冲突。

最坏的情况是，您最终更新或删除了错误的行，导致数据丢失。事实上，考虑到MD5在20多年前就被破坏了，人们可以计算出会导致冲突的值。早在2008年，这个就被用来入侵系统，甚至生成流氓CA证书。

更糟糕的是，串联表达式：

(LTRIM(RTRIM(COALESCE(column1,'')))+';'+LTRIM(RTRIM(COALESCE(column2,''))))

将为多个不同的列值创建相同的初始字符串。

除此之外，考虑到哈希值的随机性，这会导致表碎片和不能用于范围查询的索引。主键大多数时候也是集群键，这意味着它们指定了存储在磁盘上的行的顺序。对PK使用本质上随机的值意味着可以在表的数据页的中间甚至开头添加新行。

这也损害了缓存，因为数据是在页面中从磁盘加载的。有了有意义的聚集键，加载特定行很可能也会加载很快需要的行。在分页时加载例如50行可能只需要加载一个页面。使用一个基本上随机的密钥，您最终可能会加载50页。

使用用NEWID()生成的GUID将提供没有冲突的键值。使用NEWSEQUENTIALID()将生成顺序GUID值，从而消除碎片并再次允许范围搜索。

一个更好的解决方案是只从两列创建PK：

ALTER TABLE ThatTable ADD PRIMARY KEY (Column1,Column2);

或者只添加一个IDENTITY生成的ID列。bigint足够大，可以处理所有场景：

Create ThatTable (
ID bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
...
)

如果打算忽略列值中的空格，则有更好的选择：

最简单的解决方案是在插入值时清理
可以向每列添加CHECK约束，以确保列不能有前导空格或尾随空格
INSTEAD OF触发器可用于修剪它们
可以添加计算的持久化列来修剪原始列，例如Column1_Cleaned as TRIM(Column1) PERSISTED。持久化列可以用于索引和主键

至于它的作用：

它会生成弃用警告(MD5已弃用(
它用0x20字节填充MD5散列。一个相当。。。填充数据的不同寻常的方式。我怀疑，无论是谁第一次写这篇文章，都想将散列填充到32字节，但在不理解其含义的情况下使用了一些复制pasta代码

您可以通过散列任何值来检查结果。以下查询

select hashbytes('md5','banana')
----------------------------------
0x72B302BF297A228A75730123EFEF7C41

select cast(hashbytes('md5','banana') as char(32))
--------------------------------
r³¿)z"Šus#ïï|A

ASCII中的一个空格是字节0x20。强制转换为二进制将空间替换为0x20，而不是0x00

select cast(cast(hashbytes('md5','banana') as char(32)) as varbinary(32))
------------------------------------------------------------------
0x72B302BF297A228A75730123EFEF7C4120202020202020202020202020202020

如果想要将16字节的值填充到32字节，那么使用0x00会更有意义。虽然的结果并不比原来的好

select cast(hashbytes('md5','banana') as binary(32))
------------------------------------------------------------------
0x72B302BF297A228A75730123EFEF7C4100000000000000000000000000000000

要获得真正的32字节散列，可以使用SHA2_256：

select hashbytes('sha2_256','banana')
------------------------------------------------------------------
0xB493D48364AFE44D11C0165CF470A4164D1E2609911EF998BE868D46ADE3DE4E

相关内容

最新更新

热门标签：