mysql 查询所有子节点

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

WITH RECURSIVE category_tree AS (
    -- Anchor member: select the root node(s)
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    -- Recursive member: select children of the current node
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
    WHERE NOT c.id = ct.id -- 防止循环引用
)
SELECT * FROM category_tree;