SQLite递归查询以返回文件路径

Question

目前我正在开发一个需要将文件层次结构（无法区分文件夹和文件）存储在数据库中的应用程序。为此，我创建了以下表：

tbl_files
----------------------------------
|   id   |   name   |   parent   |
----------------------------------

父级字段是对tbl_files本身的外键。主目录的ID为root。现在我想获取文件路径，直到到达根目录为止。我考虑使用递归SQL查询来完成这个任务，但我不知道如何从数据库中“返回”路径。

递归查询是否可行，还是不好的做法？如何使用此查询“生成”路径？

- Cilenco

Answer 1

要获取路径条目，请使用递归CTE。要组合名称，请使用group_concat()：

WITH RECURSIVE path(level, name, parent) AS (
    SELECT 0, name, parent
    FROM tbl_files
    WHERE id = @MyFileID
    UNION ALL
    SELECT path.level + 1,
           tbl_files.name,
           tbl_files.parent
    FROM tbl_files
    JOIN path ON tbl_files.id = path.parent
),
path_from_root AS (
    SELECT name
    FROM path
    ORDER BY level DESC
)
SELECT group_concat(name, '/')
FROM path_from_root;

- CL.

---

非常感谢您。您能否也请就此查询方式的效率说一些吗？ - Cilenco

它的效率和手动查找记录一样高。但是你为什么在意，你需要每秒处理数百万个路径吗？ - CL.

好的，谢谢。我以前从未使用过这样的东西，我考虑了递归堆栈大小之类的问题，但我认为对于我的查询来说这不是很重要。 - Cilenco

如果你只选择名称，那么如何在 path 中有一个名为 parent 的列呢？对我来说，这个查询会抛出一个适当的错误：Error: no such column: path.parent。 - turbo

@mınxomaτ 抱歉，这一列确实应该在“路径”中。 - CL.