当前位置：首页 > 问答 > 正文

SQL Server里那个hierarchyid类型，原生支持分层数据，简单聊聊它是啥和怎么用

雪和泽
问答
2025-12-25 23:08:00
1

想象一下，你需要在数据库里存一个公司的组织结构图：有董事长，下面有几位副总，每位副总下面又有几个经理，经理下面还有员工，或者存一个家族的家谱：爷爷有多个孩子，每个孩子又组成自己的家庭，这种数据就是典型的分层数据,像一棵倒过来的树。

在早期，要处理这种数据挺麻烦的，常用的方法是“邻接表”，就是每个记录里存一个字段指向它父节点的ID，虽然存储简单，但当你需要查询一个节点的所有子孙，或者计算某个节点在第几层时，就得写递归查询，性能可能会成为问题,尤其是层级很深的时候。

SQL Server 2008引入的hierarchyid数据类型，就是微软为了原生支持这种树形结构而提供的解决方案，它不像邻接表那样只存储一个简单的父ID，而是用一种特殊的方法，直接记录每个节点在整棵树中的“位置路径”。

hierarchyid到底是什么？

简单说，hierarchyid是一个CLR数据类型，它的值本质上是一个“路径”，这个路径描述了从树根到该节点所经过的路线，它内部用一种紧凑的二进制格式存储这个路径信息，但对用户来说，我们通常用一个可读的字符串来表示它，、/1/、/1/1/、/1/2/、/2/。

举个例子,就拿公司组织架构来说：

董事长可能是根节点，他的hierarchyid是。
两位副总，可以分别是 /1/ 和 /2/,斜杠之间的数字表示兄弟节点间的顺序。
在第一位副总 (/1/) 下面，有三个经理，他们的hierarchyid可以是 /1/1/、/1/2/、/1/3/。
在第二位副总 (/2/) 下面，有两个经理，是 /2/1/ 和 /2/2/。

你看，通过这种路径表示法，任何一个节点的“身世”一目了然。/1/2/ 的意思就是：它是根节点的第一个子节点(/1/)的第二个子节点。

hierarchyid怎么用？

建表你需要在表中定义一个hierarchyid类型的列,通常我们还会配合一个计算列来显示可读的路径字符串。

CREATE TABLE EmployeeOrg (
    EmployeeID int PRIMARY KEY,
    EmployeeName nvarchar(50) NOT NULL,
    Position nvarchar(50),
    -- 核心：定义hierarchyid列
    OrgNode hierarchyid NOT NULL,
    -- 可选：创建一个计算列，方便查看节点路径
    OrgLevel AS OrgNode.GetLevel(),
    -- 可选：创建一个计算列，将路径转换为字符串，方便阅读
    OrgPath AS OrgNode.ToString()
);

这里的 GetLevel() 是hierarchyid类型的一个方法，它返回节点在树中的深度，根节点是0，下一层是1,依此类推。

插入数据（建立层次关系） 插入数据的关键在于如何生成正确的hierarchyid值，SQL Server提供了一些方法来帮助你。
- 插入根节点：根节点通常用 hierarchyid::GetRoot() 表示，它的路径是。
```
INSERT INTO EmployeeOrg (EmployeeID, EmployeeName, Position, OrgNode)
VALUES (1, '董事长', 'CEO', hierarchyid::GetRoot());
```
- 插入子节点：这需要知道父节点的hierarchyid，使用父节点的 GetDescendant 方法。 GetDescendant 方法需要两个参数，用于指定新节点在两个兄弟节点之间的位置,这让你可以灵活地控制插入位置。
  - GetDescendant(NULL, NULL)：作为父节点的最后一个子节点插入。
  - GetDescendant(@child1, NULL)：插入在@child1之后。
  - GetDescendant(NULL, @child2)：插入在@child2之前。
  - GetDescendant(@child1, @child2)：插入在@child1和@child2之间。
  假设我们要在董事长()下面插入第一个副总：
```
DECLARE @CEO hierarchyid = hierarchyid::GetRoot();
INSERT INTO EmployeeOrg (EmployeeID, EmployeeName, Position, OrgNode)
VALUES (2, '张副总', 'VP', @CEO.GetDescendant(NULL, NULL));
```
  这会生成路径 /1/。
  
  再插入第二个副总,也是在董事长下面：
```
INSERT INTO EmployeeOrg (EmployeeID, EmployeeName, Position, OrgNode)
VALUES (3, '李副总', 'VP', @CEO.GetDescendant((SELECT OrgNode FROM EmployeeOrg WHERE EmployeeID = 2), NULL));
```
  这会生成路径 /2/，因为它被插入到了第一个副总(/1/)之后。
  
  要在张副总(/1/)下面插入他的第一个经理：
```
DECLARE @VPZhang hierarchyid = (SELECT OrgNode FROM EmployeeOrg WHERE EmployeeID = 2);
INSERT INTO EmployeeOrg (EmployeeID, EmployeeName, Position, OrgNode)
VALUES (4, '王经理', 'Manager', @VPZhang.GetDescendant(NULL, NULL));
```
  这会生成路径 /1/1/。

查询数据（利用层次关系） 这是hierarchyid的优势所在,查询变得非常直观和高效。

查询整棵树：按OrgNode排序,就能得到深度优先遍历的顺序。

SQL Server里那个hierarchyid类型，原生支持分层数据，简单聊聊它是啥和怎么用

SELECT EmployeeID, EmployeeName, Position, OrgNode.ToString() AS Path, OrgLevel
FROM EmployeeOrg
ORDER BY OrgNode;

查询直接下属：使用 IsDescendantOf 方法，查询张副总的所有直接下属（即层级只比他深一级）。

DECLARE @Manager hierarchyid = (SELECT OrgNode FROM EmployeeOrg WHERE EmployeeID = 2);
SELECT *
FROM EmployeeOrg
WHERE OrgNode.IsDescendantOf(@Manager) = 1 -- 是@Manager的后代
AND OrgLevel = @Manager.GetLevel() + 1; -- 并且层级只深一级

查询所有子孙（整个子树）：去掉层级限制即可,查询李副总及其所有手下。

DECLARE @VPLi hierarchyid = (SELECT OrgNode FROM EmployeeOrg WHERE EmployeeID = 3);
SELECT *
FROM EmployeeOrg
WHERE OrgNode.IsDescendantOf(@VPLi) = 1;

查询祖先路径：查询某个员工的所有上级，比如查询王经理的上级链,直到根节点。

DECLARE @Employee hierarchyid = (SELECT OrgNode FROM EmployeeOrg WHERE EmployeeID = 4); -- 假设是王经理
SELECT *
FROM EmployeeOrg
WHERE @Employee.IsDescendantOf(OrgNode) = 1; -- 反转一下逻辑：查找是当前节点祖先的节点
ORDER BY OrgLevel;

移动子树：这是hierarchyid另一个强大功能，如果你想将王经理的整个部门从张副总手下移动到李副总手下，使用 GetReparentedValue 方法可以一次性更新整个子树,无需复杂的递归操作。
```
-- 假设要移动的节点是 /1/1/（王经理），他的新父节点是 /2/（李副总）
DECLARE @oldParent hierarchyid = '/1/';
DECLARE @newParent hierarchyid = '/2/';
UPDATE EmployeeOrg
SET OrgNode = OrgNode.GetReparentedValue(@oldParent, @newParent)
WHERE OrgNode.IsDescendantOf(@oldParent) = 1;
```
执行后，王经理的路径会从 /1/1/ 变成 /2/1/，如果他下面还有员工，路径也会自动跟着变,非常方便。

总结一下hierarchyid的优缺点：

优点：
- 查询效率高：对于祖先/后代查询,比递归查询邻接表性能好很多。
- 维护方便：内置方法使得插入、移动子树等操作非常简单。
- 语义清晰：路径表示法直观地反映了节点在树中的位置。
缺点：
- 并发插入挑战：在并发环境下，多个会话同时向同一个父节点下插入子节点时，需要小心处理 GetDescendant 的参数,或者使用事务和锁来避免冲突。
- 深度优先存储：它默认的存储顺序是深度优先，如果需要按广度优先顺序查询,可能需要额外处理。
- 非SQL标准：它是SQL Server特有的，如果数据库迁移到其他系统（如MySQL或PostgreSQL）,需要重写相关逻辑。