MySQL 5.1.42递归存储过程问题：无法获取父级Profile属性

问题原因 #

你的存储过程存在两个关键问题：

1. MySQL 5.1不支持递归CTE，且每次递归调用都会单独执行SELECT语句，这些SELECT会生成多个独立的结果集。大多数数据库客户端只会接收第一个结果集（当前profile的属性），后续递归产生的父级属性结果集不会被合并返回。
2. 递归过程中没有累积各层级的结果，而是各自输出，导致最终只拿到第一层的查询结果。

修正后的存储过程 #

我们需要用会话级临时表来累积所有层级的属性，递归结束后再统一返回合并结果：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_attributes`(IN profile_id INT)
BEGIN
  DECLARE parent_id INT DEFAULT NULL;
  
  -- 检查临时表是否存在，不存在则创建
  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'temp_attributes') THEN
    CREATE TEMPORARY TABLE temp_attributes (
      Id INT,
      attribut_rank INT,
      attribut_type VARCHAR(255),
      create_date DATETIME,
      editor VARCHAR(255),
      label VARCHAR(255),
      memo TEXT,
      name VARCHAR(255),
      profile INT
    );
  END IF;

  -- 将当前profile的属性插入临时表
  INSERT INTO temp_attributes
  SELECT
    Id, attribut_rank, attribut_type, create_date, editor, label, memo, name, profile
  FROM 
    marketing_profile_attribut
  WHERE 
    profile = profile_id
  ORDER BY 
    attribut_rank;

  -- 获取当前profile的父ID
  SELECT 
    parent
  INTO
    parent_id
  FROM
    marketing_profile
  WHERE 
    Id = profile_id;

  -- 递归处理父级，直到无父节点时返回所有结果
  IF parent_id IS NOT NULL THEN
    CALL get_all_attributes(parent_id);
  ELSE
    -- 合并返回所有层级的属性，按profile倒序（子节点在前）+ 排序字段
    SELECT * FROM temp_attributes ORDER BY profile DESC, attribut_rank;
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_attributes;
  END IF;
END

注意事项 #

• 临时表temp_attributes是会话级的，仅在当前数据库连接中存在，会话结束后自动销毁，不会干扰其他连接。
• 递归到最顶层父节点时才统一查询临时表，确保所有层级的属性合并为一个结果集返回。
• 若需多次调用该存储过程，临时表会在每次递归结束后自动删除，无需额外清理。

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