mysql树状查询

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

WITH RECURSIVE parent_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为你要查询的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN parent_categories pc ON c.id = pc.parent_id
)
SELECT * FROM parent_categories;

WITH RECURSIVE child_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ? -- 替换为你要查询的节点ID
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN child_categories cc ON c.parent_id = cc.id
)
SELECT * FROM child_categories;

WITH RECURSIVE parent_categories AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = ?
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN parent_categories pc ON c.id = pc.parent_id
    LIMIT 10 -- 设置最大递归深度
)
SELECT * FROM parent_categories;