SQL Server 2012中EncryptByPassPhrase数值截断问题解决咨询

这个问题我之前也碰到过，根源出在float转varchar的默认转换上——SQL Server对float类型转字符串时，会自动用科学计数法或者截断的格式输出，导致加密的字符串并不是完整的原始数值，解密后自然就出现了截断或精度丢失的情况。

给你几个靠谱的解决办法：

方法1：用STR()函数生成完整的数值字符串 #

STR()函数可以指定数值的总长度和小数位数，强制输出完整的数字格式，不会触发科学计数法。对于你的整数float值，设置小数位为0即可：

DECLARE @EncryptedValue varbinary(8000); 
DECLARE @MyFloat float; 
SET @MyFloat = 1204787; 
SELECT @EncryptedValue = EncryptByPassPhrase('x12#', STR(@MyFloat, 15, 0)); 
SELECT 
    @MyFloat as 'before', 
    @EncryptedValue, 
    CAST(CAST(decryptbypassphrase('x12#', @EncryptedValue) as varchar(8000)) as float) as 'after';

方法2：转成decimal类型再转字符串 #

float是近似数值类型，先转成精确的decimal类型，再转字符串就能得到完整的数字表示，避免精度丢失：

DECLARE @EncryptedValue varbinary(8000); 
DECLARE @MyFloat float; 
SET @MyFloat = 1204787; 
SELECT @EncryptedValue = EncryptByPassPhrase('x12#', CAST(CAST(@MyFloat AS decimal(18,0)) AS varchar(8000))); 
SELECT 
    @MyFloat as 'before', 
    @EncryptedValue, 
    CAST(CAST(decryptbypassphrase('x12#', @EncryptedValue) as varchar(8000)) as float) as 'after';

方法3：直接加密float的二进制表示（最高效） #

绕开字符串转换的环节，直接把float转成varbinary加密，解密后再转回float，完全不会有精度损失：

DECLARE @EncryptedValue varbinary(8000); 
DECLARE @MyFloat float; 
SET @MyFloat = 1204787; 
SELECT @EncryptedValue = EncryptByPassPhrase('x12#', CAST(@MyFloat AS varbinary(8))); 
SELECT 
    @MyFloat as 'before', 
    @EncryptedValue, 
    CAST(decryptbypassphrase('x12#', @EncryptedValue) as float) as 'after';

这三种方法都能解决你遇到的数值截断问题，其中方法3的性能最优，因为省去了字符串转换的开销。

内容的提问来源于stack exchange，提问作者Dobin