SqlServer利用sql语句使用FileTable

字段名	类型	含义
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的介绍和用法_火焰-优快云博客

四、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对文件进行处理说实话速度应该还是有点点慢而且操作不太方便。现在可能更多的是使用云数据库。图片上利用云数据库存储获得图片下载地址及外链会更方便。

但希望我的文章还是有点点用的。