MySQL同表存储父子分类如何拼接分类名为「父>子」格式查询

方案1：仅适用两级分类场景 #

如果你分类最多只有两级（无更深层级的子分类），可直接使用自关联查询：

SELECT 
  c1.categoryId,
  CASE 
    WHEN c1.parentCategoryId = 0 THEN c1.categoryName
    ELSE CONCAT(c2.categoryName, ' > ', c1.categoryName)
  END AS categoryName,
  c1.categorytype,
  c1.parentCategoryId,
  c1.status
FROM categories c1
LEFT JOIN categories c2 ON c1.parentCategoryId = c2.categoryId

该方案逻辑简单，执行效率高，完全匹配你给出的示例数据输出要求。

方案2：支持无限级分类（适用MySQL 8.0+/PostgreSQL/SQL Server等支持CTE的数据库） #

如果存在三级及以上层级的分类，需要完整拼接所有上级分类名称，可使用递归公共表表达式实现：

WITH RECURSIVE category_tree AS (
  -- 初始化顶级分类
  SELECT 
    categoryId,
    categoryName AS full_name,
    categorytype,
    parentCategoryId,
    status
  FROM categories
  WHERE parentCategoryId = 0
  
  UNION ALL
  
  -- 递归拼接各级子分类名称
  SELECT 
    c.categoryId,
    CONCAT(ct.full_name, ' > ', c.categoryName) AS full_name,
    c.categorytype,
    c.parentCategoryId,
    c.status
  FROM categories c
  JOIN category_tree ct ON c.parentCategoryId = ct.categoryId
)
SELECT 
  categoryId,
  full_name AS categoryName,
  categorytype,
  parentCategoryId,
  status
FROM category_tree
ORDER BY categoryId

方案3：适配MySQL 5.x等不支持CTE的数据库 #

可通过自定义函数实现层级名称拼接：

-- 第一步：创建自定义函数
DELIMITER //
CREATE FUNCTION get_full_category_name(cat_id INT) 
RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
  DECLARE full_name VARCHAR(500);
  DECLARE current_parent_id INT;
  DECLARE current_name VARCHAR(255);
  
  SELECT categoryName, parentCategoryId INTO current_name, current_parent_id
  FROM categories WHERE categoryId = cat_id;
  SET full_name = current_name;
  
  WHILE current_parent_id != 0 DO
    SELECT categoryName, parentCategoryId INTO current_name, current_parent_id
    FROM categories WHERE categoryId = current_parent_id;
    SET full_name = CONCAT(current_name, ' > ', full_name);
  END WHILE;
  
  RETURN full_name;
END //
DELIMITER ;

-- 第二步：调用函数查询
SELECT 
  categoryId,
  get_full_category_name(categoryId) AS categoryName,
  categorytype,
  parentCategoryId,
  status
FROM categories
ORDER BY categoryId

内容的提问来源于stack exchange，提问作者Ramesh S