MySQL树形结构表设计

两个字段：

• pid：父级ID
• parent_ids：所有经过的路径节点ID

这样设计有个好处是，可以查任意节点的所有子节点，从任意节点开始既可以向上查，也可以向下查

select * from enterprise where find_in_set(4, parent_ids); 

构造菜单树（Java实现）

@Data
@NoArgsConstructor
public class Menu {
    private Integer id;
    private Integer pid;    //  父菜单ID
    private String pids;    //  所有父菜单ID集合（按顺序，逗号分隔）
    private String name;
    private String code;
    private int sort;
    List<Menu> children;

    public Menu(Integer id, Integer pid, String name, int sort) {
        this.id = id;
        this.pid = pid;
        this.name = name;
        this.sort = sort;
    }
}

第一种写法

@Test
void contextLoads() throws Exception {
    List<Menu> allMenuList = new ArrayList<>();
    allMenuList.add(new Menu(1, 0, "一级菜单A", 1));
    allMenuList.add(new Menu(2, 0, "一级菜单B", 2));
    allMenuList.add(new Menu(3, 1, "二级菜单AA", 1));
    allMenuList.add(new Menu(4, 2, "二级菜单BB", 1));
    allMenuList.add(new Menu(5, 3, "三级菜单AAA", 2));
    allMenuList.add(new Menu(6, 4, "三级菜单BBB", 2));
    allMenuList.add(new Menu(7, 1, "二级菜单AC", 2));
    
    //  找到所有一级菜单
    List<Menu> parentList = allMenuList.stream().filter(e->e.getPid().equals(0)).collect(Collectors.toList());
    for (Menu menu : parentList) {
        menu.setChildren(getChild(menu.getId(), allMenuList));
    }
}

/**
 * 递归查找子菜单
 */
public List<Menu> getChild(Integer pid, List<Menu> allMenuList) {
    List<Menu> childList = new ArrayList<>();
    for (Menu menu : allMenuList) {
        if (pid.equals(menu.getPid())) {
            menu.setChildren(getChild(menu.getId(), allMenuList));
            childList.add(menu);
        }
    }
    childList.sort(Comparator.comparing(Menu::getSort).reversed());
    return childList;
}

第二种写法

@Test
void contextLoads() throws Exception {
    List<Menu> allMenuList = new ArrayList<>();
    allMenuList.add(new Menu(1, 0, "一级菜单A", 1));
    allMenuList.add(new Menu(2, 0, "一级菜单B", 2));
    allMenuList.add(new Menu(3, 1, "二级菜单AA", 1));
    allMenuList.add(new Menu(4, 2, "二级菜单BB", 1));
    allMenuList.add(new Menu(5, 3, "三级菜单AAA", 2));
    allMenuList.add(new Menu(6, 4, "三级菜单BBB", 2));
    allMenuList.add(new Menu(7, 1, "二级菜单AC", 2));

    //  第二种写法
    List<Menu> list = allMenuList.stream()
            .filter(e->e.getPid().equals(0))
            .peek(e->e.setChildren(getChild2(e.getId(), allMenuList)))
            .sorted(Comparator.comparing(Menu::getSort))
            .collect(Collectors.toList());
}

/**
 * 递归查找子菜单（简写版）
 */
public List<Menu> getChild2(Integer pid, List<Menu> allMenuList) {
    return allMenuList.stream()
            .filter(e->pid.equals(e.getPid()))
            .peek(e->e.setChildren(getChild2(e.getId(), allMenuList)))
            .sorted(Comparator.comparing(Menu::getSort))
            .collect(Collectors.toList());
}