mysql 获取所有子节点

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

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为你要查询的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- 创建示例表
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
);

-- 插入示例数据
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Root', NULL),
(2, 'Child1', 1),
(3, 'Child2', 1),
(4, 'Grandchild1', 2),
(5, 'Grandchild2', 2);

-- 获取所有子节点的递归查询
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1 -- 替换为你要查询的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;