解决MS SQL Server 使用HashBytes函数乱码问题

HASHBYTES 语法（参考MSDN）：

HASHBYTES ( '<algorithm>', { @input | 'input' } ) 

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 

作用：返回其在 SQL Server 中的输入的 MD2、MD4、MD5、SHA、SHA1 或 SHA2 哈希值。

 

T_User表中pwd列为varchar类型，执行sql：  

INSERT INTO T_User (pwd) VALUES(HASHBYTES('SHA1','m6go123123'))

结果实际存进去的是乱码，如： ?硼{?鸇Sⅲ膙M9

这是由于 HASHBYTES 函数返回的是 varbinary 类型的数据，隐式转换为varchar类型后就出现了乱码。

解决1：设计pwd类型为varbinary，则存储的将是0x开头的十六进制的二进制类型；

解决2. 往往pwd列都设计为varchar类型，因此需要显式转换，如下：

INSERT INTO T_User (pwd_varchar) VALUES(CONVERT(VARCHAR(50),HASHBYTES('SHA1','m6go够123123'),1))

这样存储的值就不是乱码，实际存储的值为： 0xAC5C5715768872B6152F723F503CC8E73169D6F5 

如果值不需要0x开头，可以设置 CONVERT() 的style参数为2，如下：

INSERT INTO T_User (pwd_varchar) VALUES(CONVERT(VARCHAR(50),HASHBYTES('SHA1','m6go够123123'),2))

保存的值为： AC5C5715768872B6152F723F503CC8E73169D6F5

这里Convet函数的用法，参考 MSDN 的“二进制样式”部分。

 

Convert()函数是Sql Server2008及以上版本支持，2008以下版本可以使用下面的方法：

 

sys.fn_VarBinToHexStr() 或 sys.fn_sqlvarbasetostr() 函数转换也可以避免乱码，但是转换后的值带有0x开头，并且值为小写形式，如下：

SELECT master.dbo.fn_VarBinToHexStr(HASHBYTES('SHA1','m6go够123123'))    --0xac5c5715768872b6152f723f503cc8e73169d6f5
          
SELECT master.dbo.fn_sqlvarbasetostr(HASHBYTES('SHA1','m6go够123123'))    --0xac5c5715768872b6152f723f503cc8e73169d6f5

如果不需要开头的0x，使用substring()截取：

SELECT SUBSTRING(master.dbo.fn_VarBinToHexStr(HASHBYTES('SHA1','m6go够123123')),3,40)     --ac5c5715768872b6152f723f503cc8e73169d6f5

SELECT SUBSTRING(master.dbo.fn_SqlVarBaseToStr(HASHBYTES('SHA1','m6go够123123')),3,40)    --ac5c5715768872b6152f723f503cc8e73169d6f5

ps. SHA1算法结果为40位，MD5为32位或16位，根据实际长度截取。

 也可使用 fn_varbintohexsubstring() ：

SELECT master.dbo.fn_varbintohexsubstring(0,HASHBYTES('SHA1','m6go够123123'),1,0)    --ac5c5715768872b6152f723f503cc8e73169d6f5

 fn_SqlVarBaseToStr() 第1个参数表示是否保留0x前缀，1为保留，0为不保留。fn_VarBinToHexStr()内部调用的就是fn_SqlVarBaseToStr()，且第1个参数传的1。

参考：

http://stackoverflow.com/questions/2120/convert-hashbytes-to-varchar