需求：查询file1，查询到自下而上的整个链路信息，比如：file1 - AAA - AA - A 请问使用mysql是否可以实现这样的功能呢？

Question

在关系型数据库，比如mysql里面。
我想要存放目录和文件的关系，

比如：
A目录下有：AA目录，AB目录
AA目录下有AAA，AAB，AAC目录，
AAA目录下有file1.txt, file2.txt, file3.txt等

它们呈现的是一个树的结构。

然后查询的时候，
1）需求：比如查询A目录，可以获取到整棵树（直接深层到最底层的文件级别）
2）需求：查询file1，查询到自下而上的整个链路信息，比如：file1 - AAA - AA - A

请问一下各位大佬使用mysql是否可以实现这样的功能呢？

Answer 1

数据库表设计

1. 创建表来存储目录和文件的关系

   CREATE TABLE file_system (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       parent_id INT DEFAULT NULL,
       is_file BOOLEAN NOT NULL,
       FOREIGN KEY (parent_id) REFERENCES file_system(id)
   );

示例数据插入

插入一些示例数据来表示你的目录和文件结构：

INSERT INTO file_system (name, parent_id, is_file) VALUES
('A', NULL, FALSE),
('AA', 1, FALSE),
('AB', 1, FALSE),
('AAA', 2, FALSE),
('AAB', 2, FALSE),
('AAC', 2, FALSE),
('file1.txt', 4, TRUE),
('file2.txt', 4, TRUE),
('file3.txt', 4, TRUE);

MySql8.0及以上写法

查询整棵树

使用递归查询来获取某个目录下的所有子目录和文件：

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, is_file
    FROM file_system
    WHERE name = 'A'
    UNION ALL
    SELECT fs.id, fs.name, fs.parent_id, fs.is_file
    FROM file_system fs
    INNER JOIN tree t ON fs.parent_id = t.id
)
SELECT * FROM tree;

执行查询后，结果是：

+----+-----------+------------+---------+
| id | name      | parent_id  | is_file |
+----+-----------+------------+---------+
|  1 | A         |       NULL |       0 |
|  2 | AA        |          1 |       0 |
|  3 | AB        |          1 |       0 |
|  4 | AAA       |          2 |       0 |
|  5 | AAB       |          2 |       0 |
|  6 | AAC       |          2 |       0 |
|  7 | file1.txt |          4 |       1 |
|  8 | file2.txt |          4 |       1 |
|  9 | file3.txt |          4 |       1 |
+----+-----------+------------+---------+

查询文件的完整路径

递归查询也可以用来获取某个文件的完整路径：

WITH RECURSIVE path AS (
    SELECT id, name, parent_id
    FROM file_system
    WHERE name = 'file1.txt'
    UNION ALL
    SELECT fs.id, fs.name, fs.parent_id
    FROM file_system fs
    INNER JOIN path p ON fs.id = p.parent_id
)
SELECT * FROM path;

执行查询后，结果是：

+----+-----------+------------+
| id | name      | parent_id  |
+----+-----------+------------+
|  7 | file1.txt |          4 |
|  4 | AAA       |          2 |
|  2 | AA        |          1 |
|  1 | A         |       NULL |
+----+-----------+------------+

MySql8.0以下写法

创建存储过程来查询整棵树

使用存储过程来递归查询目录和文件结构：

DELIMITER //

CREATE PROCEDURE get_tree(IN parent INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_name VARCHAR(255);
    DECLARE cur_is_file BOOLEAN;
    DECLARE cur_parent_id INT;
    
    DECLARE cur CURSOR FOR 
    SELECT id, name, is_file, parent_id FROM file_system WHERE parent_id = parent;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO cur_id, cur_name, cur_is_file, cur_parent_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 这里可以根据需要处理每一行数据
        SELECT cur_id, cur_name, cur_is_file, cur_parent_id;
        CALL get_tree(cur_id);
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

查询整棵树

调用存储过程来查询某个目录下的所有子目录和文件：

CALL get_tree(1); -- 1 是根目录的 id

执行存储过程后，结果如下：

+----+-----------+---------+------------+
| id | name      | is_file | parent_id  |
+----+-----------+---------+------------+
|  1 | A         |       0 |       NULL |
|  2 | AA        |       0 |          1 |
|  3 | AB        |       0 |          1 |
|  4 | AAA       |       0 |          2 |
|  5 | AAB       |       0 |          2 |
|  6 | AAC       |       0 |          2 |
|  7 | file1.txt |       1 |          4 |
|  8 | file2.txt |       1 |          4 |
|  9 | file3.txt |       1 |          4 |
+----+-----------+---------+------------+

创建存储过程来查询文件路径

使用存储过程来递归查询文件的完整路径：

DELIMITER //

CREATE PROCEDURE get_path(IN child INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_name VARCHAR(255);
    DECLARE cur_parent_id INT;
    
    DECLARE cur CURSOR FOR 
    SELECT id, name, parent_id FROM file_system WHERE id = child;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO cur_id, cur_name, cur_parent_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 这里可以根据需要处理每一行数据
        SELECT cur_id, cur_name, cur_parent_id;
        IF cur_parent_id IS NOT NULL THEN
            CALL get_path(cur_parent_id);
        END IF;
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

查询文件路径

调用存储过程来查询某个文件的完整路径：

CALL get_path(7); -- 7 是 file1.txt 的 id

执行存储过程后，结果如下：

+----+-----------+------------+
| id | name      | parent_id  |
+----+-----------+------------+
|  7 | file1.txt |          4 |
|  4 | AAA       |          2 |
|  2 | AA        |          1 |
|  1 | A         |       NULL |
+----+-----------+------------+

性能优化

为了优化查询性能，可以在 parent_id 列上创建索引：

CREATE INDEX idx_parent_id ON file_system(parent_id);