mysql 遍历树

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

WITH RECURSIVE ancestors AS (
    SELECT * FROM tree_nodes WHERE id = 1
    UNION ALL
    SELECT tn.* FROM tree_nodes tn INNER JOIN ancestors a ON tn.id = a.parent_id
)
SELECT * FROM ancestors;

DELIMITER //

CREATE PROCEDURE bfs(IN start_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id INT;
    DECLARE queue CURSOR FOR SELECT id FROM tree_nodes WHERE parent_id = start_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN queue;

    read_loop: LOOP
        FETCH queue INTO current_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 处理当前节点，例如打印节点ID
        SELECT current_id;

        -- 将当前节点的子节点加入队列
        INSERT INTO temp_queue (id) SELECT id FROM tree_nodes WHERE parent_id = current_id;
    END LOOP;

    CLOSE queue;
END //

DELIMITER ;