从单个表中获取mysql中的类别子类别

-- 创建表
CREATE TABLE category (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT
);

-- 插入数据
INSERT INTO category (id, name, parent_id) VALUES
(1, '电子产品', NULL),
(2, '手机', 1),
(3, '电脑', 1),
(4, '笔记本电脑', 3),
(5, '台式电脑', 3),
(6, '家用电器', NULL),
(7, '冰箱', 6),
(8, '洗衣机', 6);

-- 递归查询
WITH RECURSIVE subcategories AS (
  SELECT id, name, parent_id
  FROM category
  WHERE id = 1 -- 指定类别ID
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM category c
  JOIN subcategories s ON c.parent_id = s.id
)
SELECT * FROM subcategories;

-- 创建表
CREATE TABLE category (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT,
  path VARCHAR(100)
);

-- 插入数据
INSERT INTO category (id, name, parent_id, path) VALUES
(1, '电子产品', NULL, '/1'),
(2, '手机', 1, '/1/2'),
(3, '电脑', 1, '/1/3'),
(4, '笔记本电脑', 3, '/1/3/4'),
(5, '台式电脑', 3, '/1/3/5'),
(6, '家用电器', NULL, '/6'),
(7, '冰箱', 6, '/6/7'),
(8, '洗衣机', 6, '/6/8');

-- 树结构查询
SELECT * FROM category WHERE path LIKE '/1%';