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：建议存储过程

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：建表和初始化数据

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

步骤3：读入

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

步骤4：读出成文件

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方法：

--用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组件，对二进制数据或文件流进行读写操作。

--如何启用 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：创建示例表

CREATE TABLE [Thumbnail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [varbinary](max) NULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ) ON [PRIMARY]

步骤2：导入

INSERT [Thumbnail] ( Data )
SELECT * FROM OPENROWSET (BULK ‘C:\Test\TestPic1.jpg‘, SINGLE_BLOB)

步骤3：导出

bcp "select Data from B2B.dbo.Thumbnail where Id=1" queryout D:\T\TestImage1_out2.dds -T -L 1

参考文档：

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

方法四：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：创建用于存储图像的目标表

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。

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

步骤5：双击Data Flow Task

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

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

步骤8：在Flat file connection manager，点击New…

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

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

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

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

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

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

步骤15：点击Input and Output Properties。

步骤16：打开Import Column Output树，选择Output Columns。

步骤17：点击Add Column，命名新列Image。

步骤18：获取该列的ID属性。（此例ID为42）

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

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

步骤21：双击OLE DB Destination。

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

步骤23：在Configure OLE DB Connection Manager，点击New…。

步骤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。

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

步骤29：在OLE DB Destination Editor，点击Mappings页面，点击OK。

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

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

步骤32：打开myImages表。

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

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

本文出自 “SQL Server Deep Dives” 博客，请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1585598

BLOB数据存储方案