sql - 将 var binary 更新到现有 SQL 列（添加到现有图像）？

Question

我有一张桌子City；我的Pic列数据类型是varbinary(max)：

我使用此代码更新我的图片列并工作（对于 1 张图片）：

UPDATE City 
SET pic = 0xwed323422222222.... 
WHERE id = 4

但是如何在Pic列中更新 3 个或更多图像？（添加到现有图像）

我的意思是我只在表中存储了where id = 41 个图像二进制文件。如何在单列中保存超过 1 张图像？

标签： sqlsql-serversql-update

Answer 1

您可以通过利用 xml（及其结构完整性）在单个列中以“半结构化”方式存储多个图像。您可以将“pic”列的数据类型更改为 xml 或将其保留为 varbinary（后者需要某种类型转换）。

以下示例假定“图像”列和“c:\testnew”文件夹（在 sql 实例上）的 varbinary 存储，其中包含 3 个图像（图像 [1..3].png）。

首先，为 cityid=1 的“图像”列加载这两个图像，然后在第三个图像上使用更新 .write() 将其附加到二进制数据（前 2 个图片）。通过使用 xml.modify() 方法可以实现在 blob 中的特定位置删除图像或插入图像。

所有这一切，如果您真的需要/必须在单行和列中存储多个图像。

create table dbo.TestImages
(
    id int identity(1,1) constraint pkidTestImages primary key clustered(id),
    CityId int,
    ImagesBlobXml varbinary(max) --or xml
)
go


--insert two images from folder c:\testnew
insert into dbo.TestImages
(
    CityId, ImagesBlobXml
)

values (
1, 
cast((
select TheImage
from 
(
select *
from openrowset(bulk N'C:\testnew\image1.png', single_blob) as i(TheImage)
union all
select *
from openrowset(bulk N'C:\testnew\image2.png', single_blob) as i(TheImage)
--union all
--select *
--from openrowset(bulk N'C:\testnew\stackoverflow.png', single_blob) as i(TheImage)
) as images
for xml path(''), type
) as varbinary(max))
);

select 'table content', *
from  dbo.TestImages;


--retrieve images (2)
select 'images in blob, initial insert', t.id, t.CityId, i.bin.value('.', 'varbinary(max)') as TheImage
from
(
select *, cast(ImagesBlobXml as xml) as ImagesBlobXmlXML
from dbo.TestImages 
) as t
cross apply t.ImagesBlobXmlXML.nodes('TheImage') as i(bin);


--append new image
update t
set ImagesBlobXml .WRITE( --note:write cannot be used on NULL values
cast((
select TheImage
from 
(
select *
from openrowset(bulk N'C:\testnew\image3.png', single_blob) as i(TheImage)
) as images
for xml path(''), type
) as varbinary(max)) , 
null, 0 --write() append
)
from dbo.TestImages as t
where CityId = 1;


--retrieve the images (3)
select 'images in blob, after update_append', t.id, t.CityId, i.bin.value('.', 'varbinary(max)') as TheImage
from
(
select *, cast(ImagesBlobXml as xml) as ImagesBlobXmlXML
from dbo.TestImages 
) as t
cross apply t.ImagesBlobXmlXML.nodes('TheImage') as i(bin);

--check for any diff
select i.bin.value('.', 'varbinary(max)') as TheImage
from
(
select *, cast(ImagesBlobXml as xml) as ImagesBlobXmlXML
from dbo.TestImages 
) as t
cross apply t.ImagesBlobXmlXML.nodes('TheImage') as i(bin)
except
select TheImage
from 
(
select *
from openrowset(bulk N'C:\testnew\image1.png', single_blob) as i(TheImage)
union all
select *
from openrowset(bulk N'C:\testnew\image2.png', single_blob) as i(TheImage)
union all
select *
from openrowset(bulk N'C:\testnew\image3.png', single_blob) as i(TheImage)
) as images;
go

--cleanup
drop table dbo.TestImages;
go