单纯的树

应用实例：

评论列表，可以无限制的回复和评论。

目标：分层存储与查询

存在递归关系的数据很常见，数据常会像树或者层级方式组织。在树形结构中，实例被称为节点（node），每个节点有多个子节点和一个父节点。最上层的节点叫根（root）节点，它没有父节点。最底层的没有子节点的节点叫叶（leaf），而中间的节点简单称为非叶（nonleaf）

应用场景：

组织架构图、话题型讨论。

反模式：总是依赖父节点

最常见的简单解决方案是添加 parent_id 字段，引用同一张表中的其他回复。可以建一个外键来维护这种关系。

CREATE TABLE Comments(
	comment_id SERIAL PRIMARY KEY,
  parent_id BIGINT UNSIGNED,
  bug_id  BIGINT UNSIGNED NOT NULL
	...
)

这样的设计叫做邻接表，这可能是程序员用来存储分层数据中最普通的方案了。

使用邻接表查询树

使用关联查询获取一条评论和它的直接后代

SELECT c1.*, c2*.
FROM Comments c1 LEFT OUTER JOIN Comments c2
	ON c2.parent_id = c1.comment_id

当你使用邻接表的时候，这样的查询会变得很不优雅，因为每增加一层的查询都会需要额外扩展一个联结，而 SQL 查询中联结的次数是有上限的。

合理使用反模式

不要过度设计，如果邻接表刚好适用，它的优势在于能快速地获取一个给定自己的直接父子节点，它也很容易插入新节点。

解决方案：使用其他树模型

路径枚举

临接表的缺点之一是从树中获取一个给定节点的所有祖先节点的开销很大。

路径枚举是一个由连续的直接层级关系组成的完整路径，如

usr/local/lib 的 UNIX 路径是文件系统的一个路径枚举。

在 Comments 表中，我们使用类型为 VARCHAR 的 path 字段来代替原来的 parent_id 字段，

CREATE TABLE Comments(
	comment_id SERIAL  PRIMARY KEY,
	path       VARCHAR(100)
	bug_id     BIGINT UNSIGNED NOT NULL,
	author     BIGINT UNSIGNED NOT NULL,
	comment_date DATETIME NOT NULL,
	comment    TEXT NOT NULL,
	FOREIGN KEY (bug_id) REFRENCES Bugs(bug_id)
	FOREIGN KEY (author) REFRENCES Accounts(account_id)
)

你可以通过比较每个节点的路径来查询一个节点的祖先，比如，要找到评论#7——路径是1/4/6/7——的祖先，可以这样做

SELECT *
FROM Comments AS c
WHERE '1/4/6/7' LIKE c.path || '%'

这句查询语句会匹配到路径为 1/4/6/%， 1/4/% 以及1/%的节点，而这些节点就是评论#7的祖先。

插入一个节点

可以插入一个叶子节点而不用修改任何其他的行。你所需要做的只是复制一份要插入节点的逻辑上的父亲节点的路径，并将这个新节点的ID追加到路径末尾就行了。如果这个ID是在插入时自动生成的，你可能需要先插入这条记录，然后获取这条记录的 ID，并更新它的路径。比如，你使用的是 MySQL，它的内置函数 LAST_INSERT_ID() 会返回当前会话的最新一条插入记录的 ID，通过调用这个函数，便可以获得你所需要的 ID，然后就可以通过新节点的父亲节点来获取完整的路径了。

INSERT INFO Comments (author, comment) VALUES ('Ollie', 'Good job!');

UPDATE Comments
	SET path = (SELECT path FROM Comments WHERE comment_id = 7)
		|| LAST_INSERT_ID() || '/'
WHERE comment_id = LAST_INSERT_ID()

缺点：

如第一章乱穿马路中描述的缺点，数据库不能确保路径的格式总是正确或者路径中的节点确定存在。依赖于应用程序的逻辑代码来维护路径的字符串，并且验证字符串的正确性的开销很大，无论将 VARCHAR 的长度设定为多大，依旧存在长度限制。

嵌套集

嵌套集解决方案是存储子孙节点的相关信息，而不是节点的直接祖先。我们使用两个数组来编码每个节点，从而表示这一信息。

闭包表

闭包表是解决分级存储的一个简单而优雅的解决方案，它记录了树中的所有节点间的关系，而不仅仅只有那些直接的父子关系。

# create-table.sql
CREATE TABLE Comments(
	comment_id SERIAL PRIMARY KEY,
	bug_id     BIGINT UNSIGNED NOT NULL,
	author     BIGINT UNSIGNED NOT NULL,
	comment_date DATETIME NOT NULL,
	comment    TEXT NOT NULL,
	FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
	FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

CREATE TABLE TreePaths(
	ancestor     BIGINT UNSIGNED NOT NULL,
	descendant   BIGINT UNSIGNED NOT NULL,
	PRIMARY KEY(ancestor, descendant)
	FOREIGN KEY(ancestor) REFERENCES Comments(comment_id)
	FOREIGN KEY(descendant) REFERENCES Comments(comment_id) 
)

将树中任何具有祖先-后代关系的节点对都存储在 TreePaths 表的一行中，即使这两个节点之间不是直接的父子关系，同时，我们还增加一行指向节点自己。

通过 TreePaths 表来获取祖先和后代比使用嵌套集更加直接。例如要获取评论#4的后代：

# descendants.sql
SELECT c.*
FROM Comments AS c
	JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;

要获取评论#6的最先，只需要在 TreePaths 表中搜索后代未评论#6的行就可以了。

SELECT c.*
FROM Comments AS c
	JOIN TreePaths AS t ON c.comment_id = t.ancestor
WHERE t.descendant = 6;

要插入一个新的叶子节点，比如评论#5的一个子节点，应首先插入一条自己到自己的关系，然后搜索 TreePaths 表中后代是评论#5的节点，增加该节点和新插入即诶但的“”祖先-后代关系

INSERT INTO TreePaths(ancestor, descendant)
	SELECT t.ancestor, 8
	FRON TreePaths AS t
	WHERE t.descendant = 5,
UINON ALL
	SELECT 8, 8;

要删除一个叶子节点，比如评论#7，应删除所有TreePaths表中后代为评论#7的行：

DELETE FRON TreePaths WHERE descendant = 7;

要删除一棵完整的子树，比如评论#4和它所有的后代，可删除所有在 TreePaths 表中后代为#4的行，以及那些评论#4的后代为后代的行：

DELETE FROM TreePaths
WHERE descendant IN (
	FROM Treepaths
	WHERE ancestor = 4
)

请注意，如果你删除了 TreePaths 中的一条记录，并不是真正删除了这条评论，这对于评论系统这个例子来说可能很奇怪，但它在其他类型的树形结构的设计中会变得比较有意义。

要从一个地方移动一棵子树到另一个地方，首先要断开这棵子树和它的祖先们的关系，所需要做的就是找到这棵子树的顶点，删除它的所有子节点和它的所有祖先节点间的关系。

比如将评论#6从它现在的位置（评论#4的孩子）移动到评论#3下，首先做如下的删除（确保别把评论#6的自我引用删掉）

DELETE FROM TreePaths
WHERE descendant IN (SELECT descendant 
    FROM TreePaths
    WHERE ancestor = 6)
AND ancestor IN (SELECT ancestor
    FROM TreePaths
    WHERE descendant = 6 
    AND ancestor != descendant);

查询评论#6的祖先（不包含评论#6自身），以及评论#6的后代（包括评论#6自身），然后删除它们之间的关系，这将正确地移除所有评论#6的祖先到评论#6和它后代之间的路径。

然后将这棵孤立的树和新节点及它的祖先建立关系。可以使用 CROSS JOIN 语句来创建一个新节点及其祖先和这棵孤立的树中所有节点间的笛卡尔积来建立所有需要的关系。

INSERT INTO TreePaths (ancestor, descendant)
	SELECT supertree.ancestor, subtree.descendant
	FROM TreePaths AS supertree
		CROSS JOIN TreePaths AS subtree
	WHERE supertree.descendant = 3
		AND subtree.ancestor = 6;

可以优化闭包表来使它更方便地查询直接父亲节点或子即诶但：在 TreePaths 表中增加一个 path_length 字段，一个节点的自我引用的 path_length 为0，到它直接子节点的path_length 为1，再下一层为2，以此类推，查询评论#4的子节点就变得很直接。

SELECT *
FORM TreePaths
WHERE ancestor = 4 AND path_length = 1;

《SQL 反模式 》笔记（二）