树形层级结构的数据库表设计方案

树形层级结构，在业务开发中经常碰到，比如部门组织，用户分组等等。
将这种带层级结构的数据保存到关系型数据库中时，如何设计表结构，才能满足高效率的查询需求，是一个常见的开发设计痛点。
如下是在实际开发中可以参考的一个数据表结构DDL定义：

-- 用户分组信息信息表
CREATE TABLE `user_group` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '分组名称',
  `parent_id` bigint NOT NULL DEFAULT '0' COMMENT '组上级id',
  `level` tinyint NOT NULL DEFAULT '1' COMMENT '层级',
  `route` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '层级id列表',
  `deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除，0 否 1 是',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
);

parent_id等于0时为一级分组，level表示所在层级（1表示1级），route保存从一级分组到当前分组的id列表（层级分组id使用英文逗号分割，如：100,102,104），如此设计之后可以很方便地满足如下查询需求：

1. 查询当前分组所在的一级分组信息时，直接从route字段就可以解析出对应的一级分组id，也可以很方便地从route字段中解析出当前分组的上级分组id。
2. 使用递归方式查询指定分组节点及其所有子节点列表。

-- 查询id为404的分组节点及其所有子节点列表
SELECT DATA.* FROM (
SELECT @ids AS _ids,
(SELECT @ids := GROUP_CONCAT( id ) FROM user_group WHERE FIND_IN_SET( parent_id, @ids ) and is_deleted = 0) AS cids,
@l := @l + 1 AS level from user_group, ( SELECT @ids := 404, @l := 0 ) b
where @ids IS NOT null ) ID,
user_group DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY id

另外也需要注意：控制层级深度，比如最大层级深入为5层，如果无限制的话可能会影响查询效率。