none
varbinary 存储16进制末尾的"0"丢失 RRS feed

  • 问题

  • 当前版本:
    Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   
    Copyright (c) 1988-2008 Microsoft Corporation  
    Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build : ) 
    
    
    表结构:
    CREATE TABLE [dbo].[Parameter](
        [Guid] [uniqueidentifier] NOT NULL,
        [SID] [varbinary](85) NULL,
        [Meno] [nvarchar](500) NULL,
        CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED ([Guid] ASC)
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Parameter] ADD CONSTRAINT [DF_Parameter_Guid]  DEFAULT (newsequentialid()) FOR [Guid]
    GO
    
    字段 [SID] 为 user_sid(),该值是从其他表插入过来的。
    最近处理数据时发现,该表的 [SID] 后两位为0的数都没有了!其实一直以来都没有!
    
    如 SID:0x010500000000000515000000169149CF26DB3E36F846EB9290E602
    本应为:0x010500000000000515000000169149CF26DB3E36F846EB9290E60200
    
    虽然少了后两位数,但是用 [SID] 关联和查询都正常,因此也没被发现。
    该表除了上面定义的主键、默认值外,没有其他约束、没有触发器,没有视图。而类似的其他表都是正常的!把表结构导出测试,也正常!
    
    开始以为是约束问题、或者程序问题。但是手动更改之后,发现问题还是在数据库这边,如下测试:
    
    UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E60200  
    FROM MyDB.dbo.Parameter T1  
    WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602
    
    (因为在生产库,没有增加触发器将 inserted.sid 输出。)
    当直接更新表的时候,结果还是一样,似乎后两位数值0被截取了或忽略了!更新执行计划如下:
    
    |--Clustered Index Update(OBJECT:([MyDB].[dbo].[Parameter].[PK_Parameter] AS [T1]), OBJECT:([MyDB].[dbo].[Parameter].[IX_Parameter] AS [T1]), SET:([MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = [Expr1002]))
      |--Compute Scalar(DEFINE:([Expr1014]=[Expr1014]))
        |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1005] THEN (0) ELSE (1) END))
          |--Compute Scalar(DEFINE:([Expr1002]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602))
            |--Table Spool
              |--Top(ROWCOUNT est 0)
                |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = 0x010500000000000515000000169149CF26DB3E36F846EB9290E602 THEN (1) ELSE (0) END))
                  |--Index Seek(OBJECT:([MyDB].[dbo].[Parameter].[IX_Parameter] AS [T1]), SEEK:([T1].[SID]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602) ORDERED FORWARD)
    
    
    看第一行和第四行:
    SET:([MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = [Expr1002]
    Compute Scalar(DEFINE:([Expr1002]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602))
    
    [SID] 更新表表达式 [Expr1002],而表达式 [Expr1002]的只为:0x010500000000000515000000169149CF26DB3E36F846EB9290E602
    也就是该标量计算的定义,直接把末尾两位数给去掉了!这是为什么??!!
    
    
    如果末尾不为0,可以正常!
    UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E60201 
    FROM MyDB.dbo.Parameter T1  
    WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602
    
    
    如果末尾为0,后面的0都丢失.
    UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9200000000 
    FROM MyDB.dbo.Parameter T1  
    WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602
    
    
    
    请问是什么原因呢?数据库中很多表都有这个字段SID,但是都是正常的.唯独这张表更新或者插入末尾的0没有.
    

    2015年10月22日 7:03

答案

  • 好像是bug。

    你试试alter table,改下这个字段的定义。


    想不想时已是想,不如不想都不想。

    • 已标记为答案 Hello.KK 2015年10月23日 17:24
    2015年10月22日 10:14
    版主
  • 存储上的问题,这个尾部空格是截断,还是存储在表中,取决于 SET ANSI_PADDING 设置,为 ON 时存储,为OFF时截断,这个选项在添加列(包括建表)时确定(不是存储数据的时候)

    下面是演示脚本

    SET ANSI_PADDING ON
    GO
    CREATE TABLE #t(
    	id int identity primary key,
    	varchar_on varchar(50),
    	varbinary_on varbinary(50)
    );
    GO
    
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE #t ADD
    	varchar_off varchar(50),
    	varbinary_off varbinary(50)
    GO
    
    SET ANSI_PADDING OFF
    GO
    INSERT #t( varchar_on, varbinary_on, varchar_off, varbinary_off)
    VALUES( 'A  ', 0x0200, 'A  ', 0x0200)
    GO
    SET ANSI_PADDING ON
    GO
    INSERT #t( varchar_on, varbinary_on, varchar_off, varbinary_off)
    VALUES( 'A  ', 0x0200, 'A  ', 0x0200)
    GO
    
    SELECT * FROM #t
    SELECT DATALENGTH(varchar_on), DATALENGTH(varbinary_on), DATALENGTH(varchar_off), DATALENGTH(varbinary_off) FROM #t
    GO
    DROP TABLE #t
    

    • 已标记为答案 Hello.KK 2015年10月24日 11:33
    2015年10月23日 2:42

全部回复

  • 好像是bug。

    你试试alter table,改下这个字段的定义。


    想不想时已是想,不如不想都不想。

    • 已标记为答案 Hello.KK 2015年10月23日 17:24
    2015年10月22日 10:14
    版主
  • 尾随空格的问题

    换作字符串 varchar/nvarchar 类型,应该是都清楚的事情, ‘a ' = 'a' 的, varbinary 类似,你可以用 select 0x02 union select 0x0200 union select 0x02000000, 测试,结果是一条记录

    所以在楼主的查询中,尾部多两个 00 对结果是没有影响的,能够匹配得上

    2015年10月23日 2:38
  • 存储上的问题,这个尾部空格是截断,还是存储在表中,取决于 SET ANSI_PADDING 设置,为 ON 时存储,为OFF时截断,这个选项在添加列(包括建表)时确定(不是存储数据的时候)

    下面是演示脚本

    SET ANSI_PADDING ON
    GO
    CREATE TABLE #t(
    	id int identity primary key,
    	varchar_on varchar(50),
    	varbinary_on varbinary(50)
    );
    GO
    
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE #t ADD
    	varchar_off varchar(50),
    	varbinary_off varbinary(50)
    GO
    
    SET ANSI_PADDING OFF
    GO
    INSERT #t( varchar_on, varbinary_on, varchar_off, varbinary_off)
    VALUES( 'A  ', 0x0200, 'A  ', 0x0200)
    GO
    SET ANSI_PADDING ON
    GO
    INSERT #t( varchar_on, varbinary_on, varchar_off, varbinary_off)
    VALUES( 'A  ', 0x0200, 'A  ', 0x0200)
    GO
    
    SELECT * FROM #t
    SELECT DATALENGTH(varchar_on), DATALENGTH(varbinary_on), DATALENGTH(varchar_off), DATALENGTH(varbinary_off) FROM #t
    GO
    DROP TABLE #t
    

    • 已标记为答案 Hello.KK 2015年10月24日 11:33
    2015年10月23日 2:42
  • 好像是bug。

    你试试alter table,改下这个字段的定义。


    想不想时已是想,不如不想都不想。

    开始想到了,不过白天没执行。后来 ALTER TABLE 再更改值,可以了!

    ALTER TABLE [dbo].[Parameter] ALTER [SID] [varbinary](85) NULL

    2015年10月23日 17:27
  • 没有被截断,后面多家几个不是0的数都可以插入正常。而且其他表都是这样定义的,是正常的;把该表定义拿出来操作也是正常的。只是生产库中唯独这表末尾的0无法存储。 alter table 正常了,不过还是不知道原因。
    2015年10月23日 17:30
  • 没有被截断,后面多家几个不是0的数都可以插入正常。而且其他表都是这样定义的,是正常的;把该表定义拿出来操作也是正常的。只是生产库中唯独这表末尾的0无法存储。 alter table 正常了,不过还是不知道原因。
    不是告诉你原因了么?
    2015年10月24日 10:39