可嵌套JSON结构对应关系型数据库表设计方案咨询

核心采用邻接表模型实现，完全适配你提到的可嵌套节点结构，支持后续扩展新节点类型，所有字段可索引可检索，查询效率远高于直接存储原始JSON。

表结构设计 #

主表：content_nodes（存储所有节点的全量信息） #

• node_id：主键，可选用自增INT或者UUID，唯一标识每个节点
• parent_id：外键，关联当前节点父节点的node_id，根节点统一设置为NULL
• node_type：VARCHAR类型，存储节点类型，比如示例中的HEADER/LIST/ITEM，后续新增节点类型直接存入新值即可，无需修改表结构
• value：TEXT类型，存储节点的文本值，比如HEADER的标题、ITEM的名称，无对应值的节点可设为NULL
• description：TEXT类型，存储节点的描述信息，比如ITEM的补充说明，无对应值的节点可设为NULL
• sort_order：INT类型，存储同一父节点下子节点的排序顺序，保证查询结果和JSON原顺序一致
• 可选扩展字段：可按需添加is_deleted（逻辑删除标记）、create_time、update_time等通用字段

可选辅助表：content_groups（用于关联根节点所属的业务分组/页面） #

如果你的节点是按页面/业务模块分组的，可以新增该表简化管理：

• group_id：主键，自增INT/UUID
• group_name：VARCHAR类型，存储分组名称，比如「用户端首页公告区」
• root_node_id：外键，关联content_nodes表的根节点node_id

示例数据映射 #

你给出的JSON示例映射到content_nodes表的记录如下：

常用操作说明 #

整树查询 #

MySQL 8.0+、PostgreSQL、SQL Server均支持递归CTE，可一次性查询整棵树的所有节点，示例语句如下：

WITH RECURSIVE node_tree AS (
    SELECT node_id, parent_id, node_type, value, description, sort_order
    FROM content_nodes
    WHERE parent_id IS NULL -- 可替换为指定根节点的node_id，查询特定子树
    UNION ALL
    SELECT c.node_id, c.parent_id, c.node_type, c.value, c.description, c.sort_order
    FROM content_nodes c
    INNER JOIN node_tree nt ON c.parent_id = nt.node_id
)
SELECT * FROM node_tree ORDER BY parent_id, sort_order;

查询后按parent_id和sort_order组装即可还原为原始嵌套JSON结构。

增删改操作 #

• 新增节点：仅需指定对应父节点parent_id和排序值即可，不受嵌套层级限制
• 修改节点：直接更新对应node_id的字段值，不影响其他节点
• 删除节点：可先删除所有子节点再删除当前节点，也可通过is_deleted字段做软删除，避免误操作

可选替代方案 #

如果你的数据库版本不支持递归CTE，可改用物化路径方案：在content_nodes表新增path字段，存储从根节点到当前节点的ID路径，比如/1/2/3/，查询子树时通过LIKE '/1/2/%'匹配路径前缀即可，缺点是节点移动时需要同步更新所有子节点的路径值，灵活性低于邻接表方案。

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