继承关系
采用一张邻接表,存储每一个结点的父元素。
create table foods (
id integer,
name varchar(15),
parent integer
);
可以使用 WITH 通用表表达式
with recursive tree as ( select id, name from foods where id = 1 union all select origin.id, tree.name || ‘>’ || origin.name from tree join foods origin on origin.parent = tree.id )
优点是简单直观 缺点是递归查询压力较大
路径枚举 Path Enumeration
设置一个 path 属性,用来存储从根节点到当前结点的路径,用分隔符隔开。
获取子节点的下一级子节点 比如获取前端开发(id 为 9)下的所有子节点
SELECT * FROM sys_org WHERE path = ‘0/1/5/9/’ 获取子节点的子树 获取研发部(id 为 5)的子树
SELECT * FROM sys_org WHERE path LIKE ‘0/1/5/%’ 删除节点 首先判断这个节点有没有子树存在。如果有,要先把子树删除,最后再删除节点本身
现在删除子树对于 Oracle 和 MySQL 的实现都是一样的
DELETE FROM sys_org WHERE id IN (SELECT id FROM sys_org WHERE path LIKE ‘0/1/5/%’) 新增节点 麻烦之处在于 path 这个字段如何生成?比如给后台开发节点(id 为 6)添加一个子节点
首先获取到父节点的 path 与 id 拼接后的字符串
SELECT CONCAT(path, id, ‘/’) FROM sys_org WHERE id = 6 得到结果
0/1/5/6/ 然后再插入新节点
INSERT INTO sys_org (path, name) VALUES (‘0/1/5/6/’, ‘Go开发’) 修改指向的父节点 首先获取到父节点的 path 与 id 拼接后的字符串
SELECT CONCAT(path, id, ‘/’) FROM sys_org WHERE id = 6 得到结果
0/1/5/6/ 然后再修改
UPDATE sys_org SET path = ‘0/1/5/6/’ WHERE id = 10
path的大小是指定的,所以理论上是不能进行无限层级的存储的,path值设置的越大,浪费的空间就越多。
左右值编码
参考文章: https://fugangqiang.github.io/posts/database/postgresql/%E7%94%A8postgresql%E5%AE%9E%E7%8E%B0%E6%A0%91%E5%BD%A2%E7%BB%93%E6%9E%84.html http://www.jasongj.com/sql/cte/ https://docs.microsoft.com/zh-cn/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15