mysql 无限极分类查询

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

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

SELECT 
    c1.id,
    c1.name,
    c1.parent_id,
    GROUP_CONCAT(c2.name ORDER BY c2.level SEPARATOR ' > ') AS path
FROM (
    SELECT 
        @r AS _id,
        (SELECT @r := parent_id FROM categories WHERE id = _id) AS parent_id,
        @l := @l + 1 AS level
    FROM 
        (SELECT @r := 1, @l := 0) vars,
        categories h
    WHERE @r <> 0
) c1
JOIN categories c2 ON c1._id = c2.id
GROUP BY c1.id;