mysql 怎么查所有子分类

WITH RECURSIVE category_tree AS (
    -- 基础查询：选择根节点（parent_id为NULL的节点）
    SELECT id, parent_id, name
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询：选择所有子节点
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

SELECT c1.id, c1.parent_id, c1.name
FROM categories c1
LEFT JOIN categories c2 ON c1.parent_id = c2.id
WHERE c1.parent_id IS NULL OR c2.id IS NOT NULL;

DELIMITER //

CREATE PROCEDURE GetAllSubCategories(IN rootId INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_parent_id INT;
    DECLARE v_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT id, parent_id, name FROM categories WHERE parent_id = rootId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_categories (
        id INT,
        parent_id INT,
        name VARCHAR(255)
    );

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO v_id, v_parent_id, v_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO temp_categories (id, parent_id, name) VALUES (v_id, v_parent_id, v_name);
        CALL GetAllSubCategories(v_id);
    END LOOP;

    CLOSE cur;

    SELECT * FROM temp_categories;
END //

DELIMITER ;

CALL GetAllSubCategories(NULL);