面试官：怎么设计一个部门表？

需求

昨天面试遇到的一个问题，要构建一个部门表，基础字段有部门名、部门id。

问，怎么设计这个部门？如果要你写一条SQL，根据部门ID查询出直接子部门和非直接子部门，那要怎么设计？多少个表？

解决方案

表结构设计

CREATE TABLE dept (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    parent_id INT,
    ancestors VARCHAR(255)
);

解释，ancestors 存放的是祖宗的主键值，主要是用来存放本部门的祖宗的。

假设部门的关系如下：

- A部门: ID=1
- B部门: ID=2（父部门是A）
- C部门: ID=3（父部门是B）

在这种情况下，部门的`ancestors`字段会设置为：

- A部门的`ancestors`为空，因为它是顶级部门。
- B部门的`ancestors`为`1`，表示A是B的直接上级。
- C部门的`ancestors`为`1,2`，表示A是C的顶级上级，B是C的直接上级。

这样子做的好处是，查询直接子部门和非直接子部门只需要遍历这个ancestors 即可，无需借助其他的。如果没有ancestors的帮助，我们需要在代码中通过parent_id逐层逐级的遍历列举以及合并列表。查询的sql也只需要这样子。

select * from dept
WHERE dept_id IN ( SELECT dept_id FROM dept WHERE find_in_set('查询的ID', ancestors ) )

# FIND_IN_SET('2', ancestors) 就是在 ancestors 字段中查找是否存在 '2'，
# 如果存在，返回其位置（非零值）。

缺点

每次维护表的时候，都要专门去维护ancestors 这个节点，会加大维护成本