开发者社区> 技术小甜> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

BLOB 数据存储方案

简介:
+关注继续查看

介绍

 

本文旨在介绍在SQL Server中用于存储BLOB(Binary Large Object)数据的类型和方法,例如图像、声音和视频等。从SQL Server 2000的类型和方法一直到SQL Server 2012的FileTable类型。

 

什么是BLOB

 

在实际应用中,如Web站点中展示的产品图片,客户端软件中展示的一些电子文档如PDF、Power Point、甚至一些声音和视频。换句话说,不是文本。一种处理这些电子文档的方式是将它们上传到一个文件服务器,创建目录保存。然后当客户需要访问应用的时候,输入相应的查询参数既可将包含这些文件的相应信息获取到。下面将会介绍SQL Server中是如何读写这些数据的方法。

 

文件存储的位置

 

到底是存储在文件系统中,还是存储在数据库中呢?要么应用程序存储指向到实际文档的文件路径信息,如“D:\images\pic1.jpg”,存储为varchar类型,而实际的文件存储在文件系统中;要么存储实际的JPG文件到binary或image列中。那么,哪些因素会影响到我们的选择呢?

 

性能: 是否有二进制对象的性能压力,像视频文件流?如果有,在文件系统存储二进制流会有更好的性能。


大小: 获取的二进制对象是否很大?大小是否超过1MB。如果对象很大,文件系统在显示和读取对象方面比从SQL Server更有效。如果二进制文件很小,存储在SQL Server更合适。


安全性: 是否访问二进制对象时牵涉到高安全性问题?如果对象存储在SQL Server,安全性将被通过有效的数据库访问方法所管理。如果存储在文件系统,将要设计替代的安全访问方法。


客户端连接方式: 客户端如何访问数据库,是通过ODBC,还是Native SQL Driver?对于大型的视频流,客户端采用ODBC方式可能会超时或失败。


碎片: 如果二进制文件会被频繁修改并且很大,文件系统会更好的处理碎片。


事务性: 是否需要事务控制?如果需要,SQL Server有内嵌的解决方案。

 

对于更深入的关于是在数据库或是文件系统存储Blob的讨论,以及前面所说的1MB大小的参考的由来,可以参考微软的官方文章:

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

 

Transact-SQL 数据类型

 

binary [ ( n ) ]

长度为 n 字节的固定长度二进制数据,其中 n 是从 1 到 8,000 的值。 存储大小为 n 字节。

 

varbinary [ ( n | max) ]

可变长度二进制数据。 n 的取值范围为 1 至 8,000。 max 指示最大存储大小是 2^31-1 个字节。 存储大小为所输入数据的实际长度 + 2 个字节。 所输入数据的长度可以是 0 字节。 varbinary 的 ANSI SQL 同义词为 binary varying。

 

image

长度可变的二进制数据,从 0 到 2^31-1 (2,147,483,647) 个字节。

 


方法一:textcopy.exe工具


1) 早在SQL Server 2000时,在binn目录下就提供了一个未公开的工具textcopy,用于对SQL Server的Image字段进行读取和写入。

 

步骤1:建议存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S ' + @srvname +
' /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F ' + @filename +
' /' + @direction
EXEC master..xp_cmdshell @exec_str
GO

 

步骤2:建表和初始化数据

1
2
3
4
5
create table 表名 (编号 int,image列名 image)
go
insert 表名 values(1,0x) -- 必须的,且不是null
insert 表名 values(2,0x) -- 必须的,且不是null
go

 

步骤3:读入

1
2
3
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','I' --注意条件是 编号=1
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','I' --注意条件是 编号=2
go

 

步骤4:读出成文件

1
2
3
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\图片.bmp','where 编号=1','O' --注意条件是 编号=1
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:\bb.doc','where 编号=2','O' --注意条件是 编号=2
go

 

如果报textcopy不是可执行文件的话,你就到

C:\Program Files\Microsoft SQL Server\MSSQL\Binn

目录下拷备 textcopy.exe到:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

 

开启xp_cmdshell方法:

1
2
3
4
5
--用xp_cmdshell
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
--关闭xp_cmdshell
EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;
--如果提示拒绝访问,一般是目录权限的问题,可新建一个目录,加入Everyone完全控制权限即可。

 

方法二:OLE Automation存储过程

 

2) 早期也有在SQL Server 2000中开启Ole Automation Procedures服务器配置选项,通过创建Adodb.Stream组件,对二进制数据或文件流进行读写操作。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
--如何启用 OLE Automation Procedures
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
  
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT,
@FILENAME varbinary(MAX)---new variable here
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT csl_CompanyLogo
,DesiredFileName ---column selected here
from mlm_CSCompanySettingsLocalizations
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME ---new column in cursor with new variable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @FILENAME --new variable here
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH ,@FILENAME --new variable here also
END
CLOSE IMGPATH
DEALLOCATE IMGPATH

 

方法三:OPENROWSET和BCP


3) 到了SQL Server 2005,支持通过OPENROWSET(BULK…)的Transact-SQL语句导入数据到varbinary(max)类型字段,通过BCP…QUERYOUT命令导出为图像文件。

 

步骤1:创建示例表

1
2
3
4
5
6
7
CREATE TABLE [Thumbnail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [varbinary](maxNULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ) ON [PRIMARY]

 

步骤2:导入

1
2
INSERT [Thumbnail] ( Data )
SELECT FROM OPENROWSET (BULK 'D:\Test\TestPic1.jpg', SINGLE_BLOB) ThumbnailPhoto

 

步骤3:导出

在命令行下输入如下BCP命令,根据提示一次输入I,0,0,后面都回车即可。

1
bcp "select Data from DB.dbo.Thumbnail where Id=1" queryout D:\Test\TestPic2.jpg -T -L 1

 

参考文档:

使用大值类型

http://msdn.microsoft.com/zh-cn/library/a1904w6t%28v=vs.80%29.aspx

使用 bcp 指定数据文件中的前缀长度

https://technet.microsoft.com/zh-CN/library/ms190779


方法四:CLR存储过程

 

4) SQL Server 2005还支持通过代码定制CLR存储过程来实现。

 

代码样例:

http://www.codeproject.com/Articles/16934/Using-CLR-integration-to-compress-BLOBs-CLOBs-in-S

 

方法五:SSIS

 

5) 有时,需要批量导入大量图像文件到SQL Server。可以用过SQL Server集成服务(SSIS)来导入。

 

步骤1:创建具有完整路径的文件列表到一个平面文件listImages.txt

D:\images\pic1.jpg

D:\images\pic2.jpg

D:\images\pic3.jpg

 

步骤2:创建用于存储图像的目标表

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE [dbo].[myImages](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[path] [varchar](200) NULL,
[image] [image] NULL,
CONSTRAINT [PK_myImages] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

步骤3:打开SQL Server Business Intelligence Studio,并创建新的Integration Services Project。

clip_image001

 

步骤4:拉取Data Flow Task到设计面板

clip_image002

 

步骤5:双击Data Flow Task

 

步骤6:在Data Flow面板拖拽一个Flat File Source,一个Import Column和一个OLE DB Destination。将任务用绿色箭头连接。

clip_image003

 

步骤7:Flat File Source将连接到步骤1创建的listImages.txt。双击Flat File Source编辑。

 

步骤8:在Flat file connection manager,点击New…

clip_image004

 

步骤9:在Flat File Connection Manager Editor输入一个连接管理名,此处输入imagefile。

 

步骤10:文件名处输入Browse…,选择listImages.txt文件。

clip_image005

 

步骤11:选择Advanced选项,在Name,输入Path修改列名。

clip_image006

 

步骤12:点击OK保存设置。

 

步骤13:双击Import Column转换器并点击Input Columns窗口。

 

步骤14:在Input Columns窗口选择Name。

clip_image007

 

步骤15:点击Input and Output Properties。

 

步骤16:打开Import Column Output树,选择Output Columns。

clip_image008

 

步骤17:点击Add Column,命名新列Image。

 

步骤18:获取该列的ID属性。(此例ID为42)

clip_image009

 

步骤19:在Input and Output Properties窗口,打开Import Column Input > Input Columns,选择Path。

 

步骤20:在FileDataColumnID属性,修改ID为42,点击OK,保存设置。

clip_image010

 

步骤21:双击OLE DB Destination。

 

步骤22:在OLE DB Destination编辑窗口,点击New..创建OLE DB connection manager。

clip_image011

 

步骤23:在Configure OLE DB Connection Manager,点击New…。

clip_image012

 

步骤24:在Connection Manager,在Provider,选择Native OLE DB\SQL Server Native Client。

 

步骤25:在Server name输入SQL Server实例名。

 

步骤26:选择Log on to the server的认证信息。

 

步骤27:输入步骤2中创建目标表的数据库名,点击OK。

clip_image013

 

步骤28:在OLE DB Destination Editor的name of the table or the view,选择之前创建的myImages表。

clip_image014

 

步骤29:在OLE DB Destination Editor,点击Mappings页面,点击OK。

clip_image015

 

步骤30:现在准备运行这个项目。点击绿色箭头的start debugging按钮。如果一切正常,任务会显示绿色,输出导入的行数。

clip_image016

 

步骤31:为了验证导入数据成功,打开SQL Server Management Studio。

 

步骤32:打开myImages表。

clip_image017

 

方法六:直接存储小文件到数据库

6) 最直接的方式是以字节数组的格式存储BLOB数据(例如:图像、文档)到数据库。因此,在插入到数据库之前先转换文件到字节数组,在从数据库获取之后转换为文件。

 

代码样例:

http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/0fb5fd05-3eb5-4568-9e2a-fd9ba5ed5a3e

 

方法七:存储路径到数据库

7) 然而,存储二进制对象(例如:图像、文档)到数据库,当检索数据时有些缓慢复杂。因此,最通用的方式是在数据库中只存储文件路径,并且存储文档到一个文件共享目录。Web应用通过URL请求,URL一般都是HTTP协议去请求文件流返回。数据库存储URL路径,但是存放路径一般是不建议直接存放绝对路径的,一般是存放相对路径,并且相对路径都是代理过的。是为了应对文件服务器的变更,文件目录结构的变化。

 

用ASP.NET在SQL Server中存储和获取BLOB对象:

http://www.codeproject.com/KB/database/Store_and_manipulat_BLOBs.aspx

 

方法八:FILESTREAM

8) 此外,在SQL Server 2008中,你可以使用新的FileStream数据类型,允许存储和有效的访问BLOB数据通过关联SQL Server 2008和NTFS文件系统。FileStream使用NT系统缓存来缓存数据。

 

FILESTREAM 通过将 varbinary(max) 二进制大型对象 (BLOB) 数据作为文件存储在 NTFS 文件系统中,将 SQL Server 数据库引擎与该文件系统集成在一起。 Transact-SQL 语句可插入、更新、查询、搜索和备份 FILESTREAM 数据。 通过 Win32 文件系统接口可以流式方式访问数据。

 

FILESTREAM介绍:

http://msdn.microsoft.com/zh-cn/library/gg471497.aspx

FILESTREAM白皮书:

http://msdn.microsoft.com/library/hh461480

FILESTREM代码样例:

http://rusanu.com/2011/02/06/filestream-mvc-download-and-upload-images-from-sql-server/

 

方法九:FileTable

9) 到了SQL Server 2012,对于需要在数据库中存储文件和目录的应用程序,借助 Windows API 兼容性和非事务性访问,SQL Server 现在提供一种特殊的“文件表”,也称为“FileTable”。 FileTable 是一种专用的用户表,它包含存储 FILESTREAM 数据的预定义架构以及文件和目录层次结构信息、文件属性。

 

FileTable 功能为 SQL Server 中存储的文件数据提供对 Windows 文件命名空间的支持以及与 Windows 应用程序的兼容性支持。 FileTable 使得应用程序可以集成其存储和数据管理组件,可对非结构化数据和元数据提供集成的 SQL Server 服务(包括全文搜索和语义搜索)。

 

换言之,您可以在 SQL Server 中将文件和文档存储在称作 FileTable 的特别的表中,但是从 Windows 应用程序访问它们,就好像它们存储在文件系统中,而不必对您的客户端应用程序进行任何更改。

 

FileTable介绍:

http://msdn.microsoft.com/zh-cn/library/ff929144%28v=sql.110%29.aspx

 

方法十:RBS

10) 从SQL Server 2008 R2开始,支持SQL Server 远程 BLOB 存储 (RBS) 是一个可选的附加组件,它允许数据库管理员在商用存储解决方案中存储二进制大型对象,而不是直接存储在主数据库服务器上。

 

RBS 可以从SQL Server 2008 R2功能包页下载。RBS 不包括在 SQL Server 2008 R2 安装介质上,并且 SQL Server 2008 R2 安装程序不安装它。

 

RBS介绍:

http://msdn.microsoft.com/zh-cn/library/gg638709%28v=sql.105%29.aspx

RBS白皮书:

http://go.microsoft.com/fwlink/?LinkId=210422


方法十一:PowerShell

可以使用Powershell来自动化存取二进制数据。


参见如下博文:

存储二进制数据到SQL Server

从SQL Server抽取二进制数据















本文转自UltraSQL51CTO博客,原文链接:http://blog.51cto.com/ultrasql/1585598 ,如需转载请自行联系原作者




版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
达梦数据库存储过程
存储过程数据库系统中,一组为了完成特定功能的SQL 语句集,它存储在数据库中,一次编译后永久有效,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升
144 0
分布式系统中数据存储方案实践
在项目研发的过程中,对于数据存储能力的依赖无处不在,项目初期,相比系统层面的组件选型与框架设计,由于数据体量不大,在存储管理方面通常容易被轻视,当项目发展进入到中后期阶段,系统的复杂性很大程度来源于数据层面;
36 0
数据湖存储架构选型
阿里巴巴计算平台事业部郑锴为大家带来数据湖存储架构选型的介绍
3094 0
使用 MongoDB 存储日志数据
线上运行的服务会产生大量的运行及访问日志,日志里会包含一些错误、警告及用户行为等信息。通常服务会以文本的形式记录日志信息,这样可读性强,方便于日常定位问题。但当产生大量的日志之后,要想从大量日志里挖掘出有价值的内容,则需要对数据进行进一步的存储和分析。本文以存储 web 服务的访问日志为例,介绍如何使用 MongoDB 来存储、分析日志数据,让日志数据发挥最大的价值。本文的内容同样适用于其他的日志存储型应用。
791 0
数据存储方案-左右值编码
在基于数据库的一般应用中,查询的需求总要大于删除和修改。为了避免对于树形结构查询时的“递归”过程,基于Tree的前序遍历设计一种全新的无递归查询、无限分组的左右值编码方案,来保存该树的数据。优点是查询非常的方便,缺点就是每次插入删除数据涉及到的更新内容太多,如果树非常大,插入一条数据可能花很长的时间。
1039 0
大话存储系列19——数据容灾
数据备份系统只能保证数据被安全地复制了一份,但是一旦生产系统发生故障,比如服务器磁盘损坏致使数据无法读写、主板损坏造成直接无法开机或者机房火灾等意外事件,我们必须将备份的数据尽快地恢复到生产系统中继续生产,这个动作就叫做容灾。
1297 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
存储分层企业数据存储类型选择与优化
立即下载
PostgresChina2018_黄晓涛_通过FDW对大容量非结构化文件的管理和访问
立即下载
新存储解决方案探索
立即下载