使用 Transact-SQL 访问 FILESTREAM 数据

项目
07/08/2024

适用范围：SQL Server

本文介绍如何使用 Transact-SQL INSERT、UPDATE 和 DELETE 语句来管理 FILESTREAM 数据。

注意

本文中的示例需要在创建启用 FILESTREAM 的数据库和创建表以存储 FILESTREAM 数据中创建的启用了 FILESTREAM 的数据库和表。

插入包含 FILESTREAM 数据的行

若要在支持 FILESTREAM 数据的表中插入一行，请使用 Transact-SQL INSERT 语句。在 FILESTREAM 列中插入数据时，可以插入 NULL 或 varbinary(max) 值。

插入 NULL

下面的示例说明了如何插入 NULL。如果 FILESTREAM 值为 NULL，则数据库引擎不会在文件系统中创建文件。

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 1, NULL);
GO

插入长度为零的记录

下面的示例说明了如何使用 INSERT 创建长度为零的记录。如果您要获取文件句柄，但使用 Win32 API 来操作文件，这是非常有用的。

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 2, 
      CAST ('' AS VARBINARY(MAX)));
GO

创建数据文件

下面的示例说明了如何使用 INSERT 创建包含数据的文件。数据库引擎将字符串 Seismic Data 转换为 varbinary(max) 值。 FILESTREAM 创建 Windows 文件（如果尚不存在）。然后将数据添加到数据文件。

INSERT INTO Archive.dbo.Records
    VALUES (NEWID(), 3, 
      CAST ('Seismic Data' AS VARBINARY(MAX)));
GO

如果选择 Archive.dbo.Records 表中的所有数据，则结果与下表中显示的结果类似。但是， Id 列将包含不同的 GUID。

ID	SerialNumber	图表
`C871B90F-D25E-47B3-A560-7CC0CA405DAC`	`1`	`NULL`
`F8F5C314-0559-4927-8FA9-1535EE0BDF50`	`2`	`0x`
`7F680840-B7A4-45D4-8CD5-527C44D35B3F`	`3`	`0x536569736D69632044617461`

更新 FILESTREAM 数据

可以使用 Transact-SQL 更新文件系统文件中的数据；但是，如果必须以流式处理的方式将大量数据传输到文件，你可能并不希望采用此方式。

下面的示例将文件记录中的所有文本替换为文本 Xray 1。

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' AS VARBINARY(MAX))
WHERE [SerialNumber] = 2;

删除 FILESTREAM 数据

删除包含 FILESTREAM 字段的行时，会同时删除其基础文件系统文件。删除行（从而删除文件）的唯一方式是使用 Transact-SQL DELETE 语句。

下面的示例说明了如何删除一行及其关联的文件系统文件。

DELETE Archive.dbo.Records
WHERE SerialNumber = 1;
GO

选择 Archive.dbo.Records 中的所有数据后，行将消失，且无法再使用关联的文件。

注意

基础文件是由 FILESTREAM 垃圾回收器删除的。

检查表或数据库是否包含 FILESTREAM 数据

若要了解数据库或表是否包含 FILESTREAM 数据，必须查询系统视图。

以下扩展示例显示了创建新数据库、创建包含 FILESTREAM 数据的表以及查询系统视图以查看表和数据库本身是否包含 FILESTREAM 数据的步骤。

USE [master];
GO

-- Create database with FILESTREAM
CREATE DATABASE [FileStreamTest] CONTAINMENT = NONE ON PRIMARY (
    NAME = N'FileStreamTest'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest.mdf'
    , SIZE = 204800 KB
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 65536 KB
    )
    , FILEGROUP [FileStreamFG] CONTAINS FILESTREAM DEFAULT(NAME = N'FileStreamTestFStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTestFStream', MAXSIZE = UNLIMITED) LOG ON (
    NAME = N'FileStreamTest_log'
    , FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\FileStreamTest_log.ldf'
    , SIZE = 270336 KB
    , MAXSIZE = 2048 GB
    , FILEGROWTH = 65536 KB
    )
    WITH CATALOG_COLLATION = DATABASE_DEFAULT;
GO

USE [FileStreamTest];
GO

CREATE TABLE FSTiffs (
    Guid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()
    , DocumentID INT NOT NULL
    , DocumentType VARCHAR(10) NOT NULL
    , FileContent VARBINARY(MAX) FILESTREAM NOT NULL
    , DateInserted DATETIME
);

-- Which database and files use FILESTREAM 
SELECT db_name(database_id) dbname
    , name AS file_name
    , physical_name
    , type_desc
    , *
FROM sys.master_files
WHERE type_desc = 'FILESTREAM';

-- Which tables in the database have FILESTREAM enabled
USE [FileStreamTest]
GO

SELECT *
FROM sys.tables
WHERE filestream_data_space_id IS NOT NULL;

--insert a TIFF file
INSERT INTO FSTiffs (
    DocumentID
    , DocumentType
    , FileContent
    , DateInserted
    )
SELECT 101
    , '.tiff'
    , *
    , GETDATE()
FROM OPENROWSET(BULK N'C:\Temp\Sample1.tiff', SINGLE_BLOB) rs;

-- Select data from FILESTREAM table
SELECT *
FROM FSTiffs;

-- Update a document
UPDATE FSTiffs
SET FileContent = (
        SELECT *
        FROM OPENROWSET(BULK N'C:\Temp\Sample2.tiff', SINGLE_BLOB) AS rs
        )
WHERE DocumentID = 101;

-- Delete a document
DELETE FSTiffs
WHERE DocumentID = 101;

--clean up any delete files
EXEC sp_filestream_force_garbage_collection @dbname = N'FileStreamTest'
    , @filename = N'FileStreamTestFStream';

通过