如何将MySQL返回的id与sponsor_id数据转换为层级结构？

核心思路是通过建立节点映射表快速关联父子节点，时间复杂度为O(n)，效率远高于递归查找父节点的方式。以下用两种常用语言实现：

JavaScript实现 #

const rawData = [
  { "id": 2723021, "sponsor_id": "2723020" },
  { "id": 2723022, "sponsor_id": "2723021" },
  { "id": 2723023, "sponsor_id": "2723021" },
  { "id": 2723024, "sponsor_id": "2723021" },
  { "id": 2723025, "sponsor_id": "2723022" },
  { "id": 2723026, "sponsor_id": "2723022" },
  { "id": 2723027, "sponsor_id": "2723022" },
  { "id": 2723028, "sponsor_id": "2723023" },
  { "id": 2723029, "sponsor_id": "2723023" },
  { "id": 2723030, "sponsor_id": "2723023" },
  { "id": 2723031, "sponsor_id": "2723024" },
  { "id": 2723032, "sponsor_id": "2723024" },
  { "id": 2723033, "sponsor_id": "2723024" },
  { "id": 2723034, "sponsor_id": "2723025" }
];

function buildHierarchy(data, rootId) {
  // 构建节点映射表，快速定位节点
  const nodeMap = {};
  data.forEach(item => {
    nodeMap[item.id] = {
      ...item,
      children: []
    };
  });

  // 手动创建根节点（原始数据中未包含该节点）
  if (!nodeMap[rootId]) {
    nodeMap[rootId] = {
      id: rootId,
      sponsor_id: null,
      children: []
    };
  }

  // 将子节点挂载到对应父节点下
  data.forEach(item => {
    const parentId = parseInt(item.sponsor_id);
    if (nodeMap[parentId]) {
      nodeMap[parentId].children.push(nodeMap[item.id]);
    }
  });

  return nodeMap[rootId];
}

// 生成树结构（根节点为2723020）
const hierarchyTree = buildHierarchy(rawData, 2723020);
console.log(JSON.stringify(hierarchyTree, null, 2));

PHP实现 #

<?php
$rawData = [
  ["id" => 2723021, "sponsor_id" => "2723020"],
  ["id" => 2723022, "sponsor_id" => "2723021"],
  ["id" => 2723023, "sponsor_id" => "2723021"],
  ["id" => 2723024, "sponsor_id" => "2723021"],
  ["id" => 2723025, "sponsor_id" => "2723022"],
  ["id" => 2723026, "sponsor_id" => "2723022"],
  ["id" => 2723027, "sponsor_id" => "2723022"],
  ["id" => 2723028, "sponsor_id" => "2723023"],
  ["id" => 2723029, "sponsor_id" => "2723023"],
  ["id" => 2723030, "sponsor_id" => "2723023"],
  ["id" => 2723031, "sponsor_id" => "2723024"],
  ["id" => 2723032, "sponsor_id" => "2723024"],
  ["id" => 2723033, "sponsor_id" => "2723024"],
  ["id" => 2723034, "sponsor_id" => "2723025"]
];

function buildHierarchy($data, $rootId) {
  $nodeMap = [];
  // 初始化所有节点并添加children字段
  foreach ($data as $item) {
    $id = $item['id'];
    $nodeMap[$id] = array_merge($item, ['children' => []]);
  }

  // 创建根节点（原始数据中不存在）
  if (!isset($nodeMap[$rootId])) {
    $nodeMap[$rootId] = [
      'id' => $rootId,
      'sponsor_id' => null,
      'children' => []
    ];
  }

  // 关联父子节点
  foreach ($data as $item) {
    $parentId = (int)$item['sponsor_id'];
    if (isset($nodeMap[$parentId])) {
      $nodeMap[$parentId]['children'][] = $nodeMap[$item['id']];
    }
  }

  return $nodeMap[$rootId];
}

$hierarchyTree = buildHierarchy($rawData, 2723020);
echo json_encode($hierarchyTree, JSON_PRETTY_PRINT);
?>

关键注意事项 #

1. 类型统一：原始数据中sponsor_id是字符串，id是数字，必须转换为相同类型才能正确匹配父节点。
2. 根节点处理：如果根节点不在原始数据集内，需要手动创建，否则会缺失顶层结构。
3. 多根场景：若存在多个无父节点的根节点，可修改代码返回根节点数组而非单个根节点。

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