mysql 向上查询父节点

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

WITH RECURSIVE category_path AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE id = 5 -- 假设我们要查询id为5的节点的父节点
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_path cp ON c.id = cp.parent_id
)
SELECT * FROM category_path;

CREATE TABLE category_paths (
    id INT PRIMARY KEY,
    path VARCHAR(255)
);

SELECT * FROM category_paths WHERE id = 5;