更新 SQLServer 2012 中的 varbinary(MAX) 字段丢失最后 4 位 [英] Update varbinary(MAX) field in SQLServer 2012 Lost Last 4 bits

问题描述

最近想做一些数据打补丁，尝试更新一个varbinary(MAX)类型的列，更新值是这样的:

Recently I would like to do some data patching, and try to update a column of type varbinary(MAX), the update value is like this:

0xFFD8F...6DC0676

0xFFD8F...6DC0676

但是，更新查询运行成功后，该值变为:

However, after update query run successfully, the value becomes:

0x0FFD8...6DC067

0x0FFD8...6DC067

似乎最后 4 位丢失了，或者整个值右移了一个字节...

It seems the last 4 bits are lost, or whole value right shifting a byte...

我尝试删除整行并运行插入查询，同样的事情发生了！

I tried deleting entire row and run an Insert Query, same things happen!

谁能告诉我为什么会这样&我该如何解决?谢谢！

Can anyone tell me why is this happening & how can I solve it? Thanks!

我尝试了几种不同长度的二进制文件，最大43658 个字符(每个代表 4 位，总计约 21 KB)，更新查询正常运行.再多1个字符就会出现上面的bug"...

I have tried several varying length of binary, for maximum 43658 characters (Each represents 4 bits, total around 21 KB), the update query runs normally. 1 more character will make the above "bug" appears...

PS1:对于较短长度的 varbinary 作为更新值，一切正常

PS1: For a shorter length varbinary as update value, everything is okay

PS2:如果有帮助，我可以发布整个二进制字符串，但它真的很长，我不确定是否适合在这里发布

PS2: I can post whole binary string out if it helps, but it is really long and I am not sure if it's suitable to post here

已感谢您的帮助！

正如有人建议的，插入的值可能是4位的奇数，所以在它前面附加了一个0.这是我关于该值的更新信息:

As someone suggested, the value inserted maybe of odd number of 4-bits, so there is a 0 append in front of it. Here is my update information on the value:

值是43677个字符长，不包括0x"，这意味着是的，很奇怪

The value is of 43677 characters long exluding "0x", which menas Yes, it is odd

它确实解释了为什么前面插入了0"，但没有解释为什么最后一个字符消失了...

It does explain why a '0' is inserted before, but does not explain why the last character disappears...

然后我做一个实验:

我插入了一个偶数长度值，我在原始值前手动添加了一个0"，

现在要更新的值是

0x0FFD8F...6DC0676

0x0FFD8F...6DC0676

长度为 43678 个字符，不包括0x"

which is of 43678 characters long, excluding "0x"

结果不走运，更新后的值还在

The result is no luck, the updated value is still

0x0FFD8...6DC067

0x0FFD8...6DC067

推荐答案

您用于更新的二进制常量 0xFFD8F...6DC0676 似乎包含奇数个十六进制数字.并且SqlServer在模式的开头添加了半字节，使其代表整数字节.

It seems that the binary constant 0xFFD8F...6DC0676 that you used for update contains odd number of hex digits. And the SqlServer added half-byte at the beginning of the pattern so that it represent whole number of bytes.

运行以下简单查询可以看到相同的效果:

You can see the same effect running the following simple query:

select 0x1, 0x104

这将返回 0x01 和 0x0104.

截断可能是由于 SSMS 中的一些限制，可以在以下实验中观察到:

The truncation may be due to some limitaions in SSMS, that can be observed in the following experiment:

declare @b varbinary(max)
set @b = 0x123456789ABCDEF0
set @b = convert(varbinary(max), replicate(@b, 65536/datalength(@b)))
select datalength(@b) DataLength, @b Data

返回的结果是 65536 和 0x123456789ABCDEF0...EF0123456789ABCD，但是如果在 SSMS 中我复制数据列我得到 43677 个字符长度的模式(这是没有前导 0x)，有效地为 21838.5 字节.因此，您似乎不应该(如果这样做)依赖通过在 SSMS 中复制/粘贴获得的长二进制数据值.

The results returned are 65536 and 0x123456789ABCDEF0...EF0123456789ABCD, however if in SSMS I copy Data column I'm getting pattern of 43677 characters length (this is without leading 0x), which is 21838.5 bytes effectively. So it seems you should not (if you do) rely on long binary data values obtained via copy/paste in SSMS.

可靠的替代方法是使用中间变量:

The reliable alternative can be using intermediate variable:

declare @data varbinary(max)
select @data = DataXXX from Table_XXX where ID = XXX
update Table_YYY set DataYYY = @data where ID = YYY

这篇关于更新 SQLServer 2012 中的 varbinary(MAX) 字段丢失最后 4 位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！