八千字超硬核树形表结构设计，中级开发必看

最新推荐文章于 2024-03-19 10:17:32 发布

夜猫nightcat

最新推荐文章于 2024-03-19 10:17:32 发布

阅读量2.3k

点赞数 64

分类专栏： MySQL基础实战专栏文章标签： java 开发语言

本文链接：https://blog.csdn.net/m0_69661182/article/details/124462269

版权

MySQL基础实战专栏专栏收录该内容

1 篇文章 6 订阅

订阅专栏

问题背景

设计表结构对于Java开发人员来说是必备的技能，表结构设计的好，可以提升我们的开发效率。设计表结构对于有一定开发经验的朋友来说并不困难，而优雅的设计表结构则是对工程师能力的一种考察，对于同一种E-R模型，根据不同的业务场景，设计出对应的好的表结构，则是对工程师的能力提出了更高要求。这篇文章，我们将以树形表结构设计为例子，引出针对不同的业务场景和变化多端的业务需求，我们怎么将树形结构的威力发挥到最大化。

基础准备

我们为什么需要树形结构？

考虑这样一个场景：我们现在面临一个这样的问题：我们现在拿到了全国所有省市区县的资料，现在要统计某个省或者某个市的生产总值，这个时候我们怎么来设计表结构呢？

刚开始不要对自己要求太高，我们先来看一种初级做法，假设我们要统计浙江省的生产总值，而我们拿到的数据，最底层的节点为区（县）即：只有在区（县）节点是有生产总值数据的，其它的节点需要汇总计算。我们可以这样设计表结构：（表中数据简化实际模型）

tb_province
province_id	province_name
1	北京
2	上海
3	广东
4	浙江

tb_city
city_id	city_name	parent_id	total_gross
11	东城区	1	20
21	黄浦区	2	15
32	深圳市	3
41	杭州市	4

tb_district
district_id	district_name	parent_id	total_gross
321	福田区	32	8
411	上城区	41	7
412	下城区	41	6
322	罗湖区	32	7

观察表中的数据，我们发现要计算浙江的生产总值，我们就要计算杭州的生产总值，要计算杭州的生产总值我们就得计算上城区和下城区的总值，为7+6=13.

那么我们该如何编写这条SQL呢？

首先我们要将三张表联合成一张表，然后取tb_province中provide_id为4的数据，最后对生产总值字段进行汇总.

SELECT
	t.province_id,
	t.province_name,
	SUM( t2.total_gross ) 
FROM
	tb_province t
	JOIN tb_city t1 ON t.province_id = t1.parent_id
	JOIN tb_district t2 ON t1.city_id = t2.parent_id 
WHERE
	t.province_name = '浙江' 
GROUP BY
	province_id;

存在的问题

如果要添加新的层级，那我们就得新建表，然后与已知的层级表进行关联
如果要删除一个层级，我们就得删除一张表

用树形结构改造

我们前面提到了两个问题，如何解决这两个问题呢，即让我们在增加和删除层级时，不需要新建或者删除表，这就要用到我们说的树形结构了.。

我们发现，上面的三张表都有id 作为当前记录的唯一表示 parent_id 字段，用来表示当前节点的父节点，一个业务字段total_gross,其它的信息相对来说不重要，因为我们在业务开发时主要关注total_gross字段，而parent_id是方便我们进行向下查找的。

所以我们可以把表结构改造一下：

tb_district_tree
id	name	total_gross	parent_id
0	中国
1	北京		0
2	上海		0
3	广东		0
4	浙江		0
11	东城区	20	1
21	黄埔区	15	2
32	深圳市		3
41	杭州市		4
321	福田区	8	32
322	罗湖区	7	32
411	上城区	7	41
412	下城区	6	41

经过改造，我们把原来的三张表存储数据，改成了一张表存储数据。这样一来，增加或删除层级，我们就不用新建或者删除表了。

查找数据

我们知道Oracle 原生支持递归查询，而MySQL不支持，要自己写函数。我们把这个过程放在Java里做。把返回的数据构造成树结构

准备一个SpringBoot工程

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.nightcat</groupId>
    <artifactId>tree_example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>tree_example</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.31</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.22</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
            </resource>
        </resources>
    </build>

</project>

server.port=8082
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/practice?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=root
mybatis.configuration.map-underscore-to-camel-case=true

package com.nightcat.tree_example.bean;

import lombok.Data;

import java.util.List;

@Data
public class CityBean {
    private Integer id;
    private String name;
    private Integer parentId;
    private Integer totalGross;
    private List<CityBean> children;
}

package com.nightcat.tree_example.dao;

import com.nightcat.tree_example.bean.CityBean;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface CityDao {
    List<CityBean> getCityTree(Integer parentId);

    List<CityBean> getFirstLevel();

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nightcat.tree_example.dao.CityDao">
    <select id="getFirstLevel" resultType="com.nightcat.tree_example.bean.CityBean">
         select * from tb_district_tree where parent_id is null
    </select>
    <select id="getCityTree" resultType="com.nightcat.tree_example.bean.CityBean">
        select * from tb_district_tree where parent_id =#{parentId}
    </select>
</mapper>

package com.nightcat.tree_example.service;

import com.nightcat.tree_example.bean.CityBean;
import com.nightcat.tree_example.dao.CityDao;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class CityService {
    @Resource
    private CityDao cityDao;


    public List<CityBean> getCityTree(Integer parentId) {
        List<CityBean> cityList;
        if (parentId == null) {
            cityList = cityDao.getFirstLevel();
        } else {
            cityList = cityDao.getCityTree(parentId);
        }
        for (CityBean cityBean : cityList) {
            Integer parentId1 = cityBean.getId();
            List<CityBean> cityTree = getCityTree(parentId1);
            cityBean.setChildren(cityTree);
        }

        return cityList;
    }
}

package com.nightcat.tree_example.controller;

import com.nightcat.tree_example.bean.CityBean;
import com.nightcat.tree_example.service.CityService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/hello/tree")
public class CityController {
    @Autowired
    private CityService cityService;

    @GetMapping("/list")
    public List<CityBean> getCityTree() {
        return cityService.getCityTree(null);
    }
}

返回的结果

这样我们把这样一个结构返回给前端，前端就能展示出一棵树了。

如何计算总值?

我们已经把数据加工成了一棵树返回给了前端，这样就万事大吉了么，显然不是，回到我们原来的问题：如何计算浙江省的生产总值，你会发现，如果按照我们上面的表设计来说，我们往往只能在递归的过程中去判断:

传入浙江省的id
得到浙江省的所有子节点，如果该节点的total_gross是null,把它当成零处理
如果子节点的total_gross不是null，那么我们把它加起来计算总值

public List<CityBean> getCityTree(Integer parentId) {
        List<CityBean> cityList;
        if (parentId == null) {
            cityList = cityDao.getFirstLevel();
        } else {
            cityList = cityDao.getCityTree(parentId);
        }
        for (CityBean cityBean : cityList) {
            Integer parentId1 = cityBean.getId();
            List<CityBean> cityTree = getCityTree(parentId1);
            cityBean.setChildren(cityTree);
        }

        return cityList;
    }

    public Integer getTotalIncome(Integer parentId) {
        Integer sum = 0;
        List<CityBean> cityTree = getCityTree(parentId);
        for (CityBean cityBean : cityTree) {
            Integer totalGross = cityBean.getTotalGross();
            if (totalGross == null) {
                Integer id = cityBean.getId();
                Integer totalIncome = getTotalIncome(id);
                sum += totalIncome;
            } else {
                sum += totalGross;
            }
        }
        return sum;
    }
}

你会发现getTotalIncome方法其实就是一个反向递归的过程

还有其它办法么？

关于上面这个问题，我们是在程序中处理的，那么我们能不能通过另一种表设计的方式，让这个问题的处理变得简洁呢，答案是可以的

另一种树形表结构

我们试着改变一下表结构，如下表

tb_district_path
id	path	name	total_gross
0	0/	中国
1	0/1/	北京
2	0/2/	上海
3	0/3/	广东
4	0/4/	浙江
11	0/1/11	东城区	20
21	0/2/21	黄埔区	15
32	0/3/32	深圳
41	0/4/41	杭州
321	0/3/32/321	福田区	8
322	0/3/32/322	罗湖区	7
411	0/4/41/411	上城区	7
412	0/4/41/412	下城区	6

表设计成这样之后，你会发现，计算生产总值特别简单：

SELECT
	sum(
	ifnull( t.total_gross, 0 )) 
FROM
	tb_district_path t 
WHERE
	t.path LIKE '0/4%'

以上这种方法免去了我们在应用程序中处理计算总值的繁琐之处

写在结尾

当然，设计表结构需要我们在实践当中不断积累，这篇文章也只是树形表结构设计中的一小部分而已，考虑到阅读感受和篇幅，今天就写到这里。

我是扬灵，如果你想和我一起学习更多技术，欢迎关注，点赞，评论，收藏，你们的鼓励是我创作的最大动力