本文探讨了如何在SQL Server中使用FileTable存储文件，推荐云存储服务如阿里云OSS，讲解了FileTable的概念、字段操作、与Java的对应关系及注意事项。作者分享了实例和常见问题解决方法。
作者后记：
如果是做课设的小伙伴们想用sqlserver存储文件的话，一定情况下只需要在一张表里设置一个blob类型字段即可，存储文件二进制格式。当然对于文件和流之间的转换上比较麻烦。
强烈建议使用阿里云oss、腾讯云等云存储服务，看起来或许很难，实际真的很简单。
目录
一、概述
关于SqlServer的FileStream和FileTable的具体说明请看微软官网相关文档
FileTable (SQL Server) - SQL Server | Microsoft Docs
开启FileSteam的具体方法也不再赘述，若之后有空再补充。
文章中部分内容是由其他文章得来。很多概念是自己的理解以及自己尝试得出的结果，文章中有错误还请指正
二、一些概念解释
1、文件组与文件表的关系
一个文件组里的文件不一定在一个目录/文件夹中，只是逻辑上在一个组里，可分布在磁盘各个地方。
文件组中可有多个表，在文件组中不一定在文件表中有记录。
一个文件表中的文件一定在一个实际目录中。
2、注意事项
不能将现有表转换为FileTable。
必须完成上面的步骤启用FILESTREAM设置和更改FILESTRAM设置。
由于FileTable 包含一个 FILESTREAM 列，因此FileTable 需要有效的 FILESTREAM 文件组。
不能在tempdb或任何其他系统数据库中创建FileTable。
不能将FileTable作为临时表。
不能更改 FILETABLE_COLLATE_FILENAME 的值。
不能更改、删除或禁用 FileTable 系统定义的列。
不能将新的用户列、计算列或持久化计算列添加到 FileTable。
删除FileTable时，将删除 FileTable 的所有列以及与该表关联的所有对象，如索引、约束和触发器。
删除FileTable时，FileTable 目录及其子目录将从数据库的 FILESTREAM 文件和目录层次结构中消失
三、表的相关信息
1、字段解释
加粗为较常用到的字段
| 字段名 | 类型 | 含义 |
|---|---|---|
| stream_id | uniqueidentifier | 用于表示某个文件的唯一标识符的一串字符串。此字符串中可能包含 FileTable 的名称和唯一标识符。该数据类型可存储16字节的二进制值，其作用与全局唯一标记符（GUID）一样 |
| file_stream | varbinary(max) | 文件的二进制表示数据。该数据类型表示可变长度二进制数据。max 指示最大存储大小为 2^31-1 字节。存储大小为所输入数据的实际长度 + 2 个字节（0x）。所输入数据的长度可以是 0 字节 |
| name | nvarchar(255) | 文件名，包括该文件的后缀名 |
| path_locator | hierarchyid | 表明文件所在位置。该数据类型为散列值，是一种长度可变的系统数据类型（后详细解释） |
| parent_path_locator | hierarchyid | 表明文件所在目录（若文件在根目录则为NULL）。类型含义同上，该字段为系统计算自动生成，不可设置 |
| file_type | 文件类型，由文件后缀名得到 | |
| cached_file_size | int | 文件大小（字节） |
| creation_time | datetimeoffset(7) | 文件创建时间，实际应指文件放入文件表的时间。该数据类型表示一个时间点，通常以相对于协调世界时（UTC）的日期和时间来表示。（例：2021-12-25 10:27:23.4651969 +08:00) |
| last_write_time | datetimeoffset(7) | 最后修改文件的时间。数据类型含义同上。 |
| last_access_time | datetimeoffset(7) | 最后访问文件的时间。数据类型含义同上。 |
| is_directory | bit | 文件是否为目录。是为1否为0，下同 |
| is_offline | bit | 文件是否为离线文件。 |
| is_hidden | bit | 文件是否为隐藏文件。 |
| is_readonly | bit | 文件是否只读。 |
| is_archive | bit | 文件是否为档案文件。（不太明确具体含义，目前用来所有文件该字段都为1） |
| is_system | bit | 文件是否为系统文件。 |
| is_temporary | bit | 文件是否为临时文件。 |
2、索引和约束
创建新的 FileTable 时创建的索引 创建新的 FileTable 时，还会创建以下系统定义的索引：
| 列 | 索引类型 |
|---|---|
| [path_locator] ASC | 主键，非聚集 |
| [parent_path_locator] ASC， [name] ASC | 唯一，非聚集 |
| [stream_id] ASC | 唯一，非聚集 |
创建新的 FileTable 时创建的约束 创建新的 FileTable 时，还会创建以下系统定义的约束：
| 约束 | 强制执行 |
|---|---|
| 以下列的默认约束： creation_time is_archive is_directory is_hidden is_offline is_readonly is_system is_temporary last_access_time last_write_time path_locator stream_id | 系统定义的默认约束强制采用指定列的默认值。 |
| 检查约束 | 系统定义的检查约束强制执行下列要求： 有效的文件名。 有效的文件属性。 父对象必须是目录。 命名空间层次结构在文件操作过程中锁定。 |
系统定义的约束的命名约定 上述系统定义的约束采用 <constraintType><tablename>[<columnname>]<uniquifier> 格式命名，其中：
-
<constraint_type> 为 CK（检查约束）、DF（默认约束）、FK（外键）、PK（主键）或 UQ（唯一约束）。
-
<uniquifier> 是系统生成的字符串以使名称唯一。 此字符串中可能包含 FileTable 的名称和唯一标识符。
其他请见下文FileTable的sql的操作
3、使用限制
一个目录中的文件不能重名。
文件夹或目录中有文件则该目录或文件不能删除。
4、关于path_locator的解释
path_locator表示文件位置
一级
0xFF4FA1235C97BBCFF69172C3B521B6F852F62037A0
toString后：
/233000124968013.240005291034250.347972750/
二级
0xFF4FA1235C97BBCFF69172C3B521B6F852F62037BF9CC80110DA037DFE524E82353D471F4158318254
toString后：
/233000124968013.240005291034250.347972750/63295004574862.222430459949851.2192603746/
toString后每一级目录的组成为
/15个数字.14个数字.10个数字/
可以看出利用toString后的目录进行拼接更方便
由于path_locator是hierarchyid类型，系统自动生成比较方便。因此如果需要利用sql语句在指定文件夹插入文件的话，先将文件放入根目录，然后取父目录的path_locator，再与该文件的path_locator进行拼接较为方便。具体操作见4.2
其他可参考sqlserver：数据类型Hierarchyid的介绍和用法_火焰-CSDN博客
四、FileTable的sql的操作
以下包含mybatis格式，sql语句中sqlserver中操作将表名、目录名、#{}部分替换后即可使用
文件和文件夹实际操作差不多，主要在于字段值不同
1、一些函数（有大小写要求）
①newID()
——用于生成唯一的stream_id
②OffsetDateTime.now()
——获得当前时间，用于填充三个数据类型为datetimeoffset(7)的time字段
③<column-name>.GetFileNamespacePath(is_full_path, @option)
——传回 FileTable 中档案和目录的UNC路径
获得所有‘/表名/xxx‘目录：
select file_stream.GetFileNamespacePath() from 表名获取该文件夹信息：
SELECT * FROM 表名 WHERE file_stream.GetFileNamespacePath() = '目录名'（目录名如\FileStore\新建文件夹）
④GetPathLocator(filenamespace_path)
——传回 FileTable 中指定之档案或目录的路径定位器识别码值
注意，参数中要为全路径
⑤ToString()
——将hierarchyid转为nvarchar(max)
其他请看Filestream 和 FileTable 函數 (Transact-SQL) | Microsoft Docs
2、插入操作
根目录插入文件
最低要求：file_stream和name，其他字段默认生成。
file_stream至少需为0x，不得为null
Insert into 表名 (stream_id,name,file_stream) values(#{fileID}*,#{name},#{stream}) 
根目录插入文件夹
最低要求：name和is_directory，其他字段默认生成。
file_stream字段默认为null
某目录下插入文件
利用3.4的思想，获取父目录path_locator的toString格式，并截取去掉最后一个斜杠，与该文件path_loctor进行拼接。
该方法感觉不算特别好，如有其他方法欢迎交流
BEGIN
Insert into *表名* (stream_id,name,file_stream) values(#{fileID},#{name},#{stream});
DECLARE @path nvarchar(255)
DECLARE @length int
DECLARE @pathID HIERARCHYID
SELECT @pathID=path_locator FROM 表名 WHERE file_stream.GetFileNamespacePath() = '目录名'
select @length=len(@pathID.ToString()) --获取长度用于截取
select @path=substring(@pathID.ToString(), 1,@length-1)
UPDATE 表名 SET path_locator = @path + path_locator.ToString() where stream_id=#{fileID}
END某目录下插入文件夹
与某目录下插入文件相似不再赘述
BEGIN
Insert into 表名(stream_id,name,file_stream,is_directory)values(#{fileID},#{name},null,1)
DECLARE @path nvarchar(255)
DECLARE @length int
DECLARE @pathID HIERARCHYID
SELECT @pathID=path_locator FROM 表名 WHERE file_stream.GetFileNamespacePath() = '目录名'
select @length=len(@pathID.ToString())--获取长度用于截取
select @path=substring(@pathID.ToString(), 1,@length-1)
UPDATE GroupFile SET path_locator = @path + path_locator.ToString() where stream_id=#{fileID}
END利用newID()插入文件
stream_id字段值用newID()函数替换即可。若需记录该ID的值，先利用变量赋值，后续再利用这个变量。
2、删除文件或文件夹
删除文件
比较简单，利用ID或名字等字段即可
delete from 表名 where stream_id = #{fileID}删除文件夹
由于含文件的文件夹不能删除，因此删除文件夹前应先处理里面的文件。
目录名应该是要求是反斜杠的，目录格式应与与file_stream.GetFileNamespacePath()查找出的结果相同
下代码表示找出目录包含该文件夹的文件并删除，即删除该文件夹内的文件或文件夹以及该文件夹本身。
BEGIN
DECLARE @pathID HIERARCHYID
SELECT @pathID = 表名.path_locator FROM GroupFile WHERE file_stream.GetFileNamespacePath() = '目录名'
delete from GroupFile where path_locator.ToString() like @pathID.ToString()+'%'
END3、查询操作
利用stream_id查询
Select * from 表名 where stream_id = 'ID字符串'（java中对应使用或可能需要类型转换，见五部分）
利用其他字段查询省略不赘述
查询某目录下所有文件
BEGIN
DECLARE @path HIERARCHYID
SELECT @path=path_locator FROM 表名 WHERE file_stream.GetFileNamespacePath() = '目录名'
SELECT stream_id,name,file_stream,is_directory from GroupFile WHERE parent_path_locator=@path
END4、修改操作
普通的修改修改操作比较简单不再赘述
修改目录
与上文某目录下插入操作类似，不赘述。
BEGIN
DECLARE @path nvarchar(255)
DECLARE @length int
DECLARE @pathID HIERARCHYID
SELECT @pathID=path_locator FROM 表名 WHERE file_stream.GetFileNamespacePath() = '目录名'
select @length=len(@pathID.ToString())
select @path=substring(@pathID.ToString(), 1,@length-1)
UPDATE GroupFile SET path_locator = @path + path_locator.ToString() where stream_id=#{fileID}
END五、sql与java的对应关系
1、重要字段对应关系
| 字段名 | sqlserver类型 | java类型 |
|---|---|---|
| stream_id | uniqueidentifier | java.util.UUID |
| file_stream | varbinary(max) | byte[] |
2、字段值生成
UUID
String fileID=UUID.randomUUID().toString();byte[]
对数据库中字段值直接赋值即可，在上传下载时需另外处理。具体看上传和下载的处理，以下只是举例。
上传
byte[] stream;
FileInputStream fis = new FileInputStream(dest);
ByteArrayOutputStream bos = new ByteArrayOutputStream(1000);
byte[] b = new byte[1024];
int n;
while ((n = fis.read(b)) != -1) {
bos.write(b, 0, n);
}
fis.close();
bos.close();
stream = bos.toByteArray();3、其他
由于数据类型不同，利用stream_id查询文件或可能需要利用类型转换如下：
Select * from 表名 where CAST(stream_id as varchar(36)) = #{fileID}六、问题和经验
利用sql插入file_stream不足位好像会进行补位，若文件为图像会造成一点问题。
中文名文件获取时可能因为编码问题文件名会变。
利用FileTable对文件进行处理说实话速度应该还是有点点慢而且操作不太方便。现在可能更多的是使用云数据库。图片上利用云数据库存储获得图片下载地址及外链会更方便。
但希望我的文章还是有点点用的。
240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
