作者后记:
如果是做课设的小伙伴们想用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()+'%'
END
3、查询操作
利用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
END
4、修改操作
普通的修改修改操作比较简单不再赘述
修改目录
与上文某目录下插入操作类似,不赘述。
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对文件进行处理说实话速度应该还是有点点慢而且操作不太方便。现在可能更多的是使用云数据库。图片上利用云数据库存储获得图片下载地址及外链会更方便。
但希望我的文章还是有点点用的。