一、前言

中国省市区还是不少的,省有34个,市有391个,区有1101个,这是以小编的库里的,可能不是最新的,但是个数也差不了多少。

当一次返回所有的数据,并且还要组装成一个三级树,一般的for,会循环34*391*1101次。这样就是千万级的,加上与数据库交互,你跑半天也跑不完。

最后的处理是组长提供的思路,果然很快。

二、思路

首先:小编的省市区是三张表

第一:我们通过三次IO从数据库中查询出所有省市区的数据,下面在进行for循环组装树形效率就很快了!

第二:为了减少IO交互,我们把刚刚取出来的市和区构建为map<provinceCode,List<City>>map<districtCode,List<District>>,这样效率就上来了!

三、数据库表设计

1. 省

CREATE TABLE `address_province`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `short_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址,0否1是',
  `remarks` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `created_at` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_deleted` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '删除标记,0:正常;1:删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_code`(`code`) USING BTREE,
  INDEX `idx_address_province_name`(`name`) USING BTREE,
  INDEX `idx_address_province_short_name`(`short_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址省' ROW_FORMAT = Dynamic;

2. 市:

CREATE TABLE `address_city`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `province_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址,0否1是',
  `remarks` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `created_at` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_deleted` tinyint(3) NULL DEFAULT 0 COMMENT '删除标记,0正常1删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_code`(`code`) USING BTREE,
  INDEX `idx_address_city_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 392 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址市' ROW_FORMAT = Dynamic;

3. 区

CREATE TABLE `address_district`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `city_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `province_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址,0否1是',
  `remarks` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `created_at` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  `updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `is_deleted` tinyint(3) NULL DEFAULT 0 COMMENT '删除标记,0正常1删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_code`(`code`) USING BTREE,
  INDEX `idx_address_district_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3110 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址县/区' ROW_FORMAT = Dynamic;

数据量太大了,就不给大家了!

四、具体实现

省市区的实体类就不展示了,大家根据自己公司的设计稍微改动即可使用!

1. 树形VO

/**
 * 地址业务父子
 *
 * @author wangzhenjun
 * @date 2022/9/2 16:26
 */
@Data
public class AddressVO implements Serializable {

    /**
     * ID
     */
    @ApiModelProperty(value = "ID")
    private Integer id;
    /**
     * 编码
     */
    @ApiModelProperty(value = "")
    private String code;
    /**
     * 名称
     */
    @ApiModelProperty(value = "")
    private String name;
	
	/**
	 * 父编码
	 */
    private String parentCode;
    /**
     * 市区
     */
    private List<AddressVO> children;
}

2. 具体实现

@Override
public Result address() {
    long l = System.currentTimeMillis();
    // 获取省市区
    List<AddressProvince> provinceList = addressProvinceMapper.selectList(Wrappers.<AddressProvince>lambdaQuery().eq(AddressProvince::getIsDeleted, 0).eq(AddressProvince::getIsLatest, 1));
    List<AddressCity> cityList = addressCityMapper.selectList(Wrappers.<AddressCity>lambdaQuery().eq(AddressCity::getIsDeleted, 0).eq(AddressCity::getIsLatest, 1));
    List<AddressDistrict> districtList = addressDistrictMapper.selectList(Wrappers.<AddressDistrict>lambdaQuery().eq(AddressDistrict::getIsDeleted, 0).eq(AddressDistrict::getIsLatest, 1));
    // 按照省code进行分组
    Map<String, List<AddressCity>> cityMap = cityList.stream().collect(Collectors.groupingBy(AddressCity::getProvinceCode));
    // 按照市code进行分组
    Map<String, List<AddressDistrict>> districtMap = districtList.stream().collect(Collectors.groupingBy(AddressDistrict::getCityCode));

    List<AddressVO> result = new ArrayList<>();
    for (AddressProvince province : provinceList) {
        // 获取某个省下的所有市
        List<AddressCity> addressCityList = cityMap.get(province.getCode());
        // 给树形对象赋省的数据
        AddressVO addressProvinceVO = new AddressVO();
        addressProvinceVO.setId(province.getId());
        addressProvinceVO.setCode(province.getCode());
        addressProvinceVO.setName(province.getName());
        List<AddressVO> cityResult = new ArrayList<>();
        for (AddressCity addressCity : addressCityList) {
            // 获取某个市下的所有区
            List<AddressDistrict> addressDistrictList = districtMap.get(addressCity.getCode());
            // 给树形对象赋市的数据
            AddressVO addressCityVO = new AddressVO();
            addressCityVO.setId(addressCity.getId());
            addressCityVO.setCode(addressCity.getCode());
            addressCityVO.setName(addressCity.getName());
            addressCityVO.setParentCode(province.getCode());
            List<AddressVO> districtResult = new ArrayList<>();
            // 便利每个市下面的所有区
            for (AddressDistrict addressDistrict : addressDistrictList) {
                // 给树形对象赋区的数据
                AddressVO addressDistrictVO = new AddressVO();
                addressDistrictVO.setId(addressDistrict.getId());
                addressDistrictVO.setCode(addressDistrict.getCode());
                addressDistrictVO.setName(addressDistrict.getName());
                addressDistrictVO.setParentCode(addressCity.getCode());
                districtResult.add(addressDistrictVO);
            }
            addressCityVO.setChildren(districtResult);
            cityResult.add(addressCityVO);
        }
        addressProvinceVO.setChildren(cityResult);
        result.add(addressProvinceVO);
    }
    System.out.println(System.currentTimeMillis() - l);
    return Result.success(result);

}

4. 计算时长

我们可以看到,一共297毫秒,小编的机器比较卡,支持cpu在60%情况下,200ms应该问题不大,可以放到redis缓存起来,这样减少IO交互,减少数据库的压力!!
在这里插入图片描述

五、前端vue

此时address为数组,按序放了选择的value,
props:可以指定显示和选择的名称,默认是value和label

<el-cascader
   v-model="addReceiverForm.address"
    placeholder="请选择收货人地址"
    :options="options"
    :props="{checkStrictly: true,value:'code',label:'name'}"></el-cascader>

========data:============
addReceiverForm:{
	address:[]
}
options: [],
========method:============

// 获取省市区
initAddress() {
  listAddressAndChild().then(data => {
    this.options = data.data
  }).catch(() => {

  });
},

六、总结

如果大家有比小编更加快的方式,欢迎留言交流哈!

如果觉得有用,还请动动大家的发财小手点点关注哈!!谢谢大家了!!


有缘人才可以看得到的哦!!!

点击访问!小编自己的网站,里面也是有很多好的文章哦!

Q.E.D.