mysql 树形菜单语句

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

INSERT INTO menu (id, name, parent_id) VALUES
(1, 'Home', NULL),
(2, 'Products', 1),
(3, 'Electronics', 2),
(4, 'Clothing', 2),
(5, 'Smartphones', 3);

WITH RECURSIVE cte_menu AS (
    SELECT id, name, parent_id
    FROM menu
    WHERE id = 3 -- 查询ID为3的节点
    UNION ALL
    SELECT m.id, m.name, m.parent_id
    FROM menu m
    INNER JOIN cte_menu cm ON m.parent_id = cm.id
)
SELECT * FROM cte_menu;