mysql 查找所有子节点

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

WITH RECURSIVE category_tree AS (
    -- Anchor member: select the root node
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1

    UNION ALL

    -- Recursive member: select all children of the current node
    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;

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 1

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE c.id NOT IN (SELECT id FROM category_tree)
)
SELECT * FROM category_tree;