SQL Server varbinary bigint 与 BitConverter.ToInt64 值不同 [英] SQL Server varbinary bigint with BitConverter.ToInt64 values are different

问题描述

我在 SQL Server 表中将我的 long 值保存为 varbinary(max):

I save my long value in a SQL Server table as varbinary(max):

var savedValue = BitConverter.GetBytes(longValue);

现在我需要在 T-SQL 查询中使用该值，但是当我尝试获取值时:

Now I need to work with that value in T-SQL query, but when I trying to get value:

select cast(Value as bigint) from dbo.MyValues

它返回不同的数值.例如，如果我在 .NET 中保存 -8588797048854775808，在 T-SQL 中我得到 33802181122903688

It returns different number value. For example if I saved -8588797048854775808 in .NET, in T-SQL I get 33802181122903688

请告诉我有什么问题?这个问题有解决办法吗?

Please tell me what's the problem? Have that issue any solution?

推荐答案

从 varbinary 转换到 bigint(并返回)使用网络字节顺序(big-endian).BitConverter 使用运行它的机器的字节序(x86 和 x64 的小字节序).

Casting from varbinary to bigint (and back) uses network byte order (big-endian). BitConverter uses the endian-ness of the machine it is run on (little-endian for x86 and x64).

因此 BitConverter.GetBytes 在 -8588797048854775808 (0x88CE7696E7167800) 上运行是 {0x00,0x88,0xE9,0x18,0x69,0x89,0x7}, 和 code>{0x00,0x88,0xE9,0x18,0x69,0x89,0x31,0x77} 是 0x0088E91869893177 = 38536887891734903.

Hence BitConverter.GetBytes run on -8588797048854775808 (0x88CE7696E7167800) is {0x00,0x88,0xE9,0x18,0x69,0x89,0x31,0x77}, and cast on {0x00,0x88,0xE9,0x18,0x69,0x89,0x31,0x77} is 0x0088E91869893177 = 38536887891734903.

显而易见的事情是首先将 64 位整数存储为 64 位整数.

The obvious thing to do is to just store 64-bit integers as 64-bit integers in the first place.

如果您确实需要进行此转换，则:

If you really need to do this conversion then:

var savedValue = BitConverter.GetBytes(IPAddress.HostToNetworkOrder(longValue))

将交换字节，同时还具有可移植性，因为如果在大端机器上运行，它不会交换字节.

Will swap around the bytes, while also being portable in that it won't swap the bytes if run on a big-endian machine.

或者，如果您出于某种原因不想使用 System.Net 命名空间，或者如果您想扩展到三个 IPAddress.HostToNetworkOrder 句柄以外的类型，请使用:

Alternatively, if you don't want to use the System.Net namespace for some reason, or if you want to be extensible to types other than the three IPAddress.HostToNetworkOrder handeles, use:

var savedValue = BitConverter.GetBytes(longValue);
if(BitConverter.IsLittleEndian)
  Array.Reverse(savedValue);

这篇关于SQL Server varbinary bigint 与 BitConverter.ToInt64 值不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！