如何在SQL Server 2017 FileTable中检查含子目录的目录是否存在

这个问题我之前搭建类似的FileTable文件归档系统时也碰到过——单独靠目录名确实没法区分不同父节点下的同名目录（比如2019和2020年的「1」月目录），核心解法是结合父目录的层级定位来做唯一性校验，利用FileTable自带的parent_path_locator和path_locator列就能完美解决。

核心思路 #

FileTable的path_locator是hierarchyid类型，用来标识目录/文件的层级位置；而parent_path_locator直接记录了当前目录的父目录的path_locator。所以要判断某个子目录是否存在，必须同时满足两个条件：

• 目录名匹配
• 该目录的parent_path_locator等于父目录的path_locator

这样就能精准区分不同父节点下的同名目录了。

具体实现方法 #

1. 单层级目录存在性检查（比如检查「2020」目录下是否有「1」月目录）

先获取父目录的path_locator，再结合子目录名查询：

DECLARE @ParentDirName NVARCHAR(255) = N'2020'; -- 父目录名（年份）
DECLARE @ChildDirName NVARCHAR(255) = N'1';     -- 要检查的子目录名（月份）
DECLARE @ParentPathLocator hierarchyid;

-- 第一步：获取父目录的path_locator
SELECT @ParentPathLocator = path_locator
FROM FILETBL
WHERE name = @ParentDirName 
  AND is_directory = 1;

-- 第二步：检查父目录下是否存在同名子目录
IF @ParentPathLocator IS NOT NULL
BEGIN
    IF EXISTS (
        SELECT 1
        FROM FILETBL
        WHERE is_directory = 1
          AND name = @ChildDirName
          AND parent_path_locator = @ParentPathLocator
    )
    BEGIN
        PRINT '目录已存在：' + @ParentDirName + '\' + @ChildDirName;
    END
    ELSE
    BEGIN
        PRINT '目录不存在，可执行创建操作';
        -- 执行插入目录语句，利用父path_locator生成子目录的path_locator
        INSERT INTO FILETBL (name, is_directory, is_archive, path_locator)
        VALUES (@ChildDirName, 1, 0, @ParentPathLocator.GetDescendant(NULL, NULL));
    END
END
ELSE
BEGIN
    PRINT '父目录不存在，请先创建父目录：' + @ParentDirName;
END

2. 多层级目录检查（比如检查「2020\1\19」是否存在）

如果要检查完整的年/月/日层级目录，可以逐层校验，确保每一级父目录存在后再检查下一级：

DECLARE @RootPath hierarchyid = hierarchyid::GetRoot(); -- FileTable根目录的path_locator
DECLARE @YearDir NVARCHAR(255) = N'2020';
DECLARE @MonthDir NVARCHAR(255) = N'1';
DECLARE @DayDir NVARCHAR(255) = N'19';

DECLARE @YearPath hierarchyid;
DECLARE @MonthPath hierarchyid;
DECLARE @DayPath hierarchyid;

-- 检查年份目录
SELECT @YearPath = path_locator
FROM FILETBL
WHERE is_directory = 1
  AND name = @YearDir
  AND parent_path_locator = @RootPath;

IF @YearPath IS NULL
BEGIN
    PRINT '年份目录不存在：' + @YearDir;
END
ELSE
BEGIN
    -- 检查月份目录
    SELECT @MonthPath = path_locator
    FROM FILETBL
    WHERE is_directory = 1
      AND name = @MonthDir
      AND parent_path_locator = @YearPath;

    IF @MonthPath IS NULL
    BEGIN
        PRINT '月份目录不存在：' + @YearDir + '\' + @MonthDir;
    END
    ELSE
    BEGIN
        -- 检查日期目录
        SELECT @DayPath = path_locator
        FROM FILETBL
        WHERE is_directory = 1
          AND name = @DayDir
          AND parent_path_locator = @MonthPath;

        IF @DayPath IS NOT NULL
        BEGIN
            PRINT '目标目录已存在：' + @YearDir + '\' + @MonthDir + '\' + @DayDir;
        END
        ELSE
        BEGIN
            PRINT '日期目录不存在，可创建';
            -- 创建日期目录
            INSERT INTO FILETBL (name, is_directory, is_archive, path_locator)
            VALUES (@DayDir, 1, 0, @MonthPath.GetDescendant(NULL, NULL));
        END
    END
END

3. 封装成可复用函数

如果需要频繁检查目录，可以把逻辑封装成函数，简化调用：

CREATE FUNCTION dbo.CheckDirectoryExists(
    @ParentPathLocator hierarchyid,
    @DirName NVARCHAR(255)
)
RETURNS BIT
AS
BEGIN
    -- 检查指定父目录下是否存在同名目录
    IF EXISTS (
        SELECT 1
        FROM FILETBL
        WHERE is_directory = 1
          AND name = @DirName
          AND parent_path_locator = @ParentPathLocator
    )
    BEGIN
        RETURN 1; -- 存在返回1
    END
    ELSE
    BEGIN
        RETURN 0; -- 不存在返回0
    END
END

调用示例：

DECLARE @YearPath hierarchyid;
SELECT @YearPath = path_locator FROM FILETBL WHERE name = N'2020' AND is_directory = 1;

IF dbo.CheckDirectoryExists(@YearPath, N'1') = 1
BEGIN
    PRINT '2020年1月目录已存在';
END
ELSE
BEGIN
    PRINT '可创建2020年1月目录';
END

关键说明 #

• hierarchyid::GetRoot()获取的是FileTable的根目录路径定位器，所有年份目录的父目录都是它
• GetDescendant(NULL, NULL)用来生成父目录下的新子目录的path_locator，确保层级正确
• 这种方式完全避免了不同父节点下同名目录的误判，精准匹配你需要的年/月/日层级结构

内容的提问来源于stack exchange，提问作者Osayed Zaheda