mysql树状结构实现

CREATE TABLE tree (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES tree(id)
);

CREATE TABLE tree (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    path VARCHAR(255)
);

CREATE TABLE tree (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    lft INT,
    rgt INT
);

CREATE TABLE tree_nodes (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE tree_paths (
    ancestor_id INT,
    descendant_id INT,
    depth INT,
    PRIMARY KEY (ancestor_id, descendant_id),
    FOREIGN KEY (ancestor_id) REFERENCES tree_nodes(id),
    FOREIGN KEY (descendant_id) REFERENCES tree_nodes(id)
);

SELECT * FROM tree WHERE parent_id = ?;

SELECT * FROM tree WHERE path LIKE ? || '%';

SELECT * FROM tree WHERE lft > ? AND rgt < ?;

SELECT tn.* 
FROM tree_nodes tn 
JOIN tree_paths tp ON tn.id = tp.descendant_id 
WHERE tp.ancestor_id = ?;

WITH RECURSIVE ancestors AS (
    SELECT * FROM tree WHERE id = ?
    UNION ALL
    SELECT t.* FROM tree t JOIN ancestors a ON t.id = a.parent_id
)
SELECT * FROM ancestors;

SELECT * FROM tree WHERE ? LIKE path || '%';

SELECT * FROM tree WHERE lft < ? AND rgt > ?;

SELECT tn.* 
FROM tree_nodes tn 
JOIN tree_paths tp ON tn.id = tp.ancestor_id 
WHERE tp.descendant_id = ?;