MySql层级关系表构建

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

存在多级上下级关系，构建竖向层级表。

---

一、为什么要构建层级表？

为了方便穿透查询及不使用递归查询。

二、怎样构建

1.用户样例表和层级样例表

代码如下（示例）：

#用户信息表：维护了用户信息及对应的上级信息。
create table user_info(
    log_id         bigint(11)      NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id        varchar(30)     DEFAULT NULL COMMENT '本级用户编号',
    up_user_id     varchar(30)     DEFAULT NULL COMMENT '上级用户编号',
    primary key(log_id)
) comment='用户信息表';

#用户层级信息表：维护了用户的所有上级信息记录。
create table user_lv_info(
    log_id         bigint(11)      NOT NULL AUTO_INCREMENT COMMENT '主键',
    user_id        varchar(30)     DEFAULT NULL COMMENT '本级用户编号',
    up_user_id     varchar(30)     DEFAULT NULL COMMENT '上级用户编号',
    primary key(log_id)
) comment='用户层级信息表';

2.层级样例表构建

代码如下（示例）：

为了方便查询所有下级的数据，避免使用递归查询，即所有的上级都在user_lv_info。
    user_info中的数据为：
        user_id  up_user_id
        用户1    用户2
        用户2    用户3
        用户3    null
    
    user_lv_info层级表中的上下级关系存储为：
        user_id  up_user_id
        用户1    用户1       (自己也认为是自己的上级)
        用户1    用户2
        用户1    用户3
        
        用户2    用户2       (自己也认为是自己的上级)
        用户2    用户3
        
        用户3    用户3       (自己也认为是自己的上级)

#初始化测试数据
insert into user_info(user_id,up_user_id) values('user1','user2');
insert into user_info(user_id,up_user_id) values('user2','user3');
insert into user_info(user_id,up_user_id) values('user3',null);

​​​​​​​#先插入本级
insert into user_lv_info(
    user_id   ,
    up_user_id   
) select 
    user_id,
    user_id
  from user_info;

#再插入本级与直属上级
insert into user_lv_info(
    user_id   ,
    up_user_id   
) select 
    user_id,
    up_user_id
  from user_info;

#再插入本级与最顶层上级
#此Sql需要执行多次，直至无新增记录为止
insert into user_lv_info(
    user_id    ,
    up_user_id 
) select
    rel.user_id    ,
    user3.up_user_id
from
    user_lv_info AS rel,
    (
        select
            user_id ,
            max(log_id) log_id
        from
            user_lv_info
        group by
            user_id
    ) AS user2,
    user_info AS user3
where rel.log_id = user2.log_id
  and rel.up_user_id = user3.user_id
  and user3.up_user_id is not null;

​​​​​​​

---

总结

例如：以上就是今天要讲的内容，简单介绍了下层级表的构建。