在数据库中存储目录结构

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

INSERT INTO directories (name, parent_id) VALUES
    ('/', NULL),
    ('/home', 1),
    ('/home/user1', 2),
    ('/home/user2', 2),
    ('/home/user3', 2),
    ('/home/user1/documents', 3),
    ('/home/user1/pictures', 3),
    ('/home/user2/documents', 4),
    ('/home/user2/music', 4),
    ('/home/user3/documents', 5),
    ('/home/user3/videos', 5);

SELECT t1.name AS directory, t2.name AS parent_directory
FROM directories t1
LEFT JOIN directories t2 ON t1.parent_id = t2.id
ORDER BY t1.id;

+--------------+----------------+
| directory    | parent_directory |
+--------------+----------------+
| /            | NULL            |
| /home        | NULL            |
| /home/user1  | /home           |
| /home/user2  | /home           |
| /home/user3  | /home           |
| /home/user1/documents | /home/user1 |
| /home/user1/pictures  | /home/user1 |
| /home/user2/documents | /home/user2 |
| /home/user2/music     | /home/user2 |
| /home/user3/documents | /home/user3 |
| /home/user3/videos    | /home/user3 |
+--------------+----------------+