MySQL树形结构(多级菜单)的数据库表设计和查询

@Data
public class Menu {
    @ApiModelProperty("该菜单ID")
    private Integer id;
    @ApiModelProperty("该菜单的名称")
    private String name;
    @ApiModelProperty("该菜单的父菜单的ID")
    private Integer parentId;
    @ApiModelProperty("该菜单是否可见")
    private Integer status;
    @ApiModelProperty("该菜单的类型")
    private String type;
    @ApiModelProperty("该菜单创建时间")
    private Date createTime;
    @ApiModelProperty("该菜单最后作出修改操作的时间")
    private Date updateTime;
    @ApiModelProperty("该菜单所有的子菜单")
    private List<Menu> childMenu;
}

@Mapper
@Repository
public interface MenuMapper extends BaseMapper<Menu> {
    @Select("select id,name,type,parent_id,status from menu where parent_id = #{id}")
    List<Menu> menuList(Integer id);
}

@Override
public List<CategoryEntity> listWithTree() {
    // 查询出所有分类
    List<CategoryEntity> cs = this.getBaseMapper().selectList(null);
    // 筛选出所有一级分类
    return cs.stream()
            .filter(categoryEntity -> categoryEntity.getParentCid() == 0L)
            .peek(categoryEntity -> categoryEntity.setChildren(this.listChildren(categoryEntity,cs)))
            .sorted(Comparator.comparing(CategoryEntity::getSort))
            .collect(Collectors.toList());
}

private List<CategoryEntity> listChildren(CategoryEntity category,List<CategoryEntity> cs){
    // 递归查找子分类
    return cs.stream()
            .filter(entity -> entity.getParentCid().equals(category.getCatId()))
            .peek(c -> c.setChildren(this.listChildren(c,cs)))
            .sorted(Comparator.comparing(CategoryEntity::getSort))
            .collect(Collectors.toList());
}