binary 和 varbinary 用法全解

在SQL Server中,使用数据类型 binary(n) 和 varbinary(n) 存储字节流,或称二进制数据,n是指字节数量:

  • binary(n):固定长度为 n 字节,其中 n 值从 1 到 8,000 ,存储空间为 n 字节;
  • varbinary( n | max):可变长度,n 的取值范围为 1 至 8,000,max 是指最大存储空间是 2^31-1 个字节,即最大4GB;
  • n:在表列定义或变量声明语句中没有指定 n,则默认长度为 1;在CAST 函数中没有指定 n,则默认长度为 30;

在将数据转换为二进制数据时,SQL Server会对生成的二进制数据进行填充或截断,详细的规则是:

  • 填充(或扩展)的二进制数据是16进制的0x00的整数倍,这就是说,填充的字节中每一个bit都是0;
  • 将字符类型转换成二进制数据时,在数据的右侧填充或截断数据,填充数值是0x00;
  • 将其他类型数据转换成二进制数据时,在数据的左侧填充或截断数据,填充数值是0x00;在截断数据时,保留低位的数据,将高位数值截断;

一,二进制类型的特征

存储单位是Byte,数据类型binary 和 varbinary 占用的存储空间的单位不是bit,而是 byte

declare @varBi binary(1)
set @varBi=1

输出结果是:0x01,结果以16进制编码显示,共8bit。

为binary变量赋值,可以使用10进制数据为binary类型的变量赋值,也可以直接使用16进制数值为binary变量赋值;

declare @biA binary(1)
set @biA= 0x01

字节流的逆序,可以使用reverse()函数对字节流进行逆序排列:

declare @bi varbinary(max)=123456
select @bi, cast(reverse(@bi) as varbinary(max))
--0x0001E240 0x40E20100

二,类型转换

由于数据都是用字节存储,二进制类型可以和所有其他类型的数据进行转换。

1, 数值类型隐式转换成binary类型

系统自动将 int类型隐式转换成binary类型,但是,binary必须显式强制转换成int类型。

declare @varBi binary(1)
set @varBi=1
select @varBi,cast(@varbi as int)

2, 字符类型不能隐式转换成binary类型,需要显式转换

将字符类型转换成binary类型的过程是:逐个将字符的ASCII码转换成二进制数据,末尾补0,或截断末尾多余的字符

declare @varBi binary(8)
set @varBi=cast('1h' as binary(8))
select @varBi,ascii(''),ascii('h'),cast(@varbi as varchar),DATALENGTH(cast(@varbi as varchar))

binary 和 varbinary 用法全解

输出结果分析:

  • 0x31 是16进制,换算成10进制数值是49,和字符“1”的ASCII值相同;
  • 0x68 是16进制,换算成10进制数值是104,和字符“h”的ASCII值相同;
  • 末尾补0:由于字符串的长度是2Byte,定长binary(8)的长度是8Byte,在binary(8) 类型的末尾补0,补齐8Byte;
  • 在将binary转换成字符类型时,输出结果是字符 “1h”,而不是数字,说明,按照ASCII将数值转换成字符;
  • 转换成字符串类型时,长度是8Byte,未显示的字符是不可打印的;

三,将字符串翻译(translate)成二进制类型

从字符数据类型翻译成binary或varbinary类型时,将在右侧发生截断或填充,填充的是16进制的0x00。当从字符类型(char、varchar、nchar、nvarchar)转换为不同长度的 binary 或 varbinary 数据类型时,SQL Server 将在二进制数据的右侧填充或截断数据。从字符串的左边逐字节翻译,如果字符串的长度大于binary的长度,则右边截断;如果字符串的长度小于binary的长度,则右边填充0x00。

翻译的字符必须是0-9、a-f,逐个把字符的ASCII码翻译成二进制数字,或者逐个把二进制数字对应ASCII码,转换为字符。默认情况下,对于二进制数据,0x是自动填充导结果的最左侧。

1,转换成binary或varbinary类型时,从数据的右侧截断数据 

declare @varBi binary(2)
set @varBi=cast('' as binary(3))
select @varBi,cast(@varbi as varchar)

binary 和 varbinary 用法全解

2,转换成binary或varbinary类型时,从数据的右侧填充数据,填充的数值是0x00

declare @varBi binary(8)
set @varBi=cast('' as binary(8))
select @varBi,cast(@varbi as varchar)

binary 和 varbinary 用法全解

3,使用convert的风格0(style=0)把binary(或varbinary)翻译成字符串

select convert(varchar(8), 0x4E616d65, 0),cast(0x4E616d65 as varchar(8))

binary 和 varbinary 用法全解

4,使用convert的风格0(style=0)把字符串翻译成binary(或varbinary)

select convert(varbinary(8), 'name', 0),cast('name' as varbinary(8))

binary 和 varbinary 用法全解

四,使用convert进行字符串和binary(或varbinary)的转换

如果要把二进制数据,按照显示的字符转换为字符串,那么就必须使用convert转换,convert的第三个参数是style,共有两种风格,分别是1 和 2,风格1要求0x必须在二进制数据的最左侧,风格2要求0x不能出现在二进制数据的最左侧。

1,使用convert把binary转换为字符串

分别设置style是1和2,查看转换的结果:当style为1时,字符串前面添加0x;当style为2时,字符串前面没有0x;

declare @b binary(8)
set @b=12
select convert(varchar(64),@b,1),convert(varchar(64),@b,2)

binary 和 varbinary 用法全解

2,使用convert把字符串转换为binary类型

如果字符串前面有0x字符,那么使用风格1(设置style=1);如果字符串前面没有0x字符,那么使用风格2(设置style=2);

declare @c varchar(64),@d varchar(64)
select @c='0x000000000000000C',@d='000000000000000C'
select convert(binary(8),@c,1), convert(binary(8),@d,2)

binary 和 varbinary 用法全解

五,将整数类型转换为二进制类型

从整数类型转换为 binary 或 varbinary 时,在数据的左侧填充或截断数据,填充将通过使用十六进制的零来完成。

1,从整数类型转换为二进制类型时,在数据的左侧截断数据,即保留低位的数据,将高位数值截断

declare @biToInt binary(1)
set @biToInt=300
select @biToInt,cast(@biToInt as int)

binary 和 varbinary 用法全解

分析:由于binary(1)是1Byte,最大值是255,发生上溢,左侧发生截断,舍弃左边高位数值 256,保留低位数值44

2,从整数类型转换为二进制类型时,在数据的左侧填充数据,即在高位填充数值:0x00

declare @biToInt binary(3)
set @biToInt= 12
select @biToInt,cast(@biToInt as int)

binary 和 varbinary 用法全解

如果没有发生溢出,那么不管int类型数值有多大,都能生成相同的值,如果binary长度较长,那么左侧填充为0x00。

六,二进制数据的比较,将自动提升操作数的数据类型

在SQL Server中,字节大的二进制数据类型,其类型处于“高”位。在进行二进制数值比较之前,首先匹配两个操作数的数据类型,将两个操作数提升为相同的数据类型,这体现在比较两个binary类型的变量时,将字节数低的数据类型提升,保持两个操作数的字节数相同。提升的字节使用0x00填充,并且是在右侧填充,这将增大binary的数值。

1,自动提升数据类型,在右侧填充0x00

declare @biA binary(1)
declare @biB binary(2)
set @biA=1
set @biB=2
select @biA,@biB,iif(@biA >= @biB,'>=','<')

binary 和 varbinary 用法全解

输出结果分析:在比较时,SQL Server将变量@biA 的数据类型提升为binary(2),在右侧填充1byte,变成0x0100,数值是256,所以比较的结果是 >=;

如果变量@biB的值是257,那么输出的结果将是<,因为,@biA填充之后,是0x0100,比0x0101小;

declare @biA varbinary(1)
declare @biB varbinary(2)
set @biA=1
set @biB=257
select @biA,@biB,iif(@biA >= @biB,'>=','<')

binary 和 varbinary 用法全解

参考文档:

binary and varbinary (Transact-SQL)

上一篇:binary 和 varbinary


下一篇:fenye