mysql中有没有递归查询

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

WITH RECURSIVE department_hierarchy AS (
    -- Anchor member: select the root department(s)
    SELECT id, name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    -- Recursive member: select child departments
    SELECT d.id, d.name, d.parent_id, dh.level + 1
    FROM departments d
    INNER JOIN department_hierarchy dh ON d.parent_id = dh.id
)
SELECT * FROM department_hierarchy;

DELIMITER //

CREATE PROCEDURE GetDepartmentHierarchy(IN department_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE _id INT;
    DECLARE _name VARCHAR(255);
    DECLARE _parent_id INT;
    DECLARE _level INT DEFAULT 0;
    
    -- 创建一个临时表来存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_hierarchy (
        id INT,
        name VARCHAR(255),
        parent_id INT,
        level INT
    );
    
    -- 递归查询
    REPEAT
        SELECT id, name, parent_id INTO _id, _name, _parent_id
        FROM departments
        WHERE parent_id = _parent_id OR (_parent_id IS NULL AND department_id IS NULL);
        
        IF NOT done THEN
            SET _level = _level + 1;
            INSERT INTO temp_hierarchy (id, name, parent_id, level) VALUES (_id, _name, _parent_id, _level);
            SET department_id = _id;
        END IF;
    UNTIL done END REPEAT;
    
    -- 输出结果
    SELECT * FROM temp_hierarchy;
    
    -- 删除临时表
    DROP TEMPORARY TABLE IF EXISTS temp_hierarchy;
END //

DELIMITER ;