MySQL 树数据转树形JSON

在数据库中，树形数据结构是一种常见的数据组织方式。MySQL数据库支持多种存储树形数据的方法，如邻接列表模型、闭包表模型等。然而，当我们需要将树形数据以JSON格式展示时，就需要进行一定的转换。本文将介绍如何将MySQL中的树形数据转换为树形JSON格式。

树形数据模型

在MySQL中，树形数据通常使用邻接列表模型存储。以一个简单的部门表为例，其结构如下：

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES departments(id)
);

在这个表中，id是部门的唯一标识，name是部门的名称，parent_id是父部门的ID。如果parent_id为NULL，则表示该部门是根部门。

查询树形数据

要查询树形数据，我们可以使用递归查询（Common Table Expressions, CTE）。以下是一个查询所有部门及其子部门的示例：

WITH RECURSIVE department_tree AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT * FROM department_tree;

这个查询首先选择所有根部门，然后递归地选择每个部门的子部门。

转换为树形JSON

将查询结果转换为树形JSON，我们可以使用MySQL的JSON_OBJECT和JSON_ARRAYAGG函数。以下是一个将查询结果转换为树形JSON的示例：

WITH RECURSIVE department_tree AS (
  -- 同上
)
SELECT JSON_OBJECT(
  'id', id,
  'name', name,
  'children', JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', id,
      'name', name
    )
  )
) AS tree
FROM department_tree
GROUP BY id, name;

这个查询首先将每个部门转换为一个JSON对象，然后使用JSON_ARRAYAGG函数将所有子部门的JSON对象聚合成一个数组，并将其作为children属性添加到父部门的JSON对象中。

代码示例

以下是一个完整的示例，包括创建表、插入数据、查询树形数据和转换为树形JSON：

-- 创建表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES departments(id)
);

-- 插入数据
INSERT INTO departments (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '分公司A', 1),
(3, '分公司B', 1),
(4, '部门A1', 2),
(5, '部门A2', 2);

-- 查询树形数据并转换为树形JSON
WITH RECURSIVE department_tree AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN department_tree dt ON d.parent_id = dt.id
)
SELECT JSON_OBJECT(
  'id', id,
  'name', name,
  'children', JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', id,
      'name', name
    )
  )
) AS tree
FROM department_tree
GROUP BY id, name;

结果

执行上述查询后，您将得到一个树形JSON，如下所示：

{
  "id": 1,
  "name": "总公司",
  "children": [
    {
      "id": 2,
      "name": "分公司A",
      "children": [
        {
          "id": 4,
          "name": "部门A1"
        },
        {
          "id": 5,
          "name": "部门A2"
        }
      ]
    },
    {
      "id": 3,
      "name": "分公司B"
    }
  ]
}

总结

本文介绍了如何将MySQL中的树形数据转换为树形JSON格式。通过使用递归查询和JSON函数，我们可以轻松地实现这一转换。这种方法不仅适用于部门表，还可以应用于其他类型的树形数据。希望本文对您有所帮助。