我刚刚为组织此表中第70页上显示的查询层次结构数据的“关闭表”方法做了“更新/添加/删除”部分：http ://www.slideshare.net/billkarwin/sql-antipatterns- strike -背部
我的数据库如下所示：
表类别：
ID Name 1 Top value 2 Sub value1
表类别树：
child parent level 1 1 0 2 2 0 2 1 1
但是，从单个查询获取整棵树作为多维数组时，我遇到了一个问题。
这是我想回来的东西：
array ( 'topvalue' = array ( 'Subvalue', 'Subvalue2', 'Subvalue3) ); );
更新： 找到了此链接，但是我仍然很难将其转换为数组：http : //karwin.blogspot.com/2010/03/rendering- trees-with-closure-tables.html
Update2： 我现在可以为每个类别添加深度，如果有帮助的话。
好的，我编写了PHP类来扩展Zend Framework DB表，行和行集类。无论如何，我一直在开发它，因为我在PHP Tek-X上谈论了两周有关分层数据模型的内容。
我不想将我所有的代码发布到Stack Overflow，因为如果这样做，它们将隐式地获得知识共享许可。 更新： 我将代码提交给Zend Framework Extras孵化器，在幻灯片共享中，我的演示文稿是带有SQL和PHP的分层数据模型。
我将用伪代码描述解决方案。我使用的是动物学分类学作为测试数据，可从ITIS.gov下载。该表是longnames：
longnames
CREATE TABLE `longnames` ( `tsn` int(11) NOT NULL, `completename` varchar(164) NOT NULL, PRIMARY KEY (`tsn`), KEY `tsn` (`tsn`,`completename`) )
我为分类法层次结构中的路径创建了一个 封闭表 ：
CREATE TABLE `closure` ( `a` int(11) NOT NULL DEFAULT '0', -- ancestor `d` int(11) NOT NULL DEFAULT '0', -- descendant `l` tinyint(3) unsigned NOT NULL, -- levels between a and d PRIMARY KEY (`a`,`d`), CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`), CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`) )
给定一个节点的主键，您可以通过以下方式获取其所有后代：
SELECT d.*, p.a AS `_parent` FROM longnames AS a JOIN closure AS c ON (c.a = a.tsn) JOIN longnames AS d ON (c.d = d.tsn) LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1) WHERE a.tsn = ? AND c.l <= ? ORDER BY c.l;
联接要closure AS p包括每个节点的父ID。
closure AS p
该查询很好地利用了索引：
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+ | 1 | SIMPLE | a | const | PRIMARY,tsn | PRIMARY | 4 | const | 1 | Using index; Using filesort | | 1 | SIMPLE | c | ref | PRIMARY,d | PRIMARY | 4 | const | 5346 | Using where | | 1 | SIMPLE | d | eq_ref | PRIMARY,tsn | PRIMARY | 4 | itis.c.d | 1 | | | 1 | SIMPLE | p | ref | d | d | 4 | itis.c.d | 3 | | +----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
鉴于我有490,032行longnames和4,299,883行closure，它的运行时间相当不错：
closure
+--------------------+----------+ | Status | Duration | +--------------------+----------+ | starting | 0.000257 | | Opening tables | 0.000028 | | System lock | 0.000009 | | Table lock | 0.000013 | | init | 0.000048 | | optimizing | 0.000032 | | statistics | 0.000142 | | preparing | 0.000048 | | executing | 0.000008 | | Sorting result | 0.034102 | | Sending data | 0.001300 | | end | 0.000018 | | query end | 0.000005 | | freeing items | 0.012191 | | logging slow query | 0.000008 | | cleaning up | 0.000007 | +--------------------+----------+
现在，我对上述SQL查询的结果进行后处理，根据层次结构（伪代码）将行分类为子集：
while ($rowData = fetch()) { $row = new RowObject($rowData); $nodes[$row["tsn"]] = $row; if (array_key_exists($row["_parent"], $nodes)) { $nodes[$row["_parent"]]->addChildRow($row); } else { $top = $row; } } return $top;
我还为“行”和“行集”定义类。行集基本上是行的数组。行包含行数据的关联数组，还包含其子级的行集。叶节点的子行集为空。
行和行集还定义了称为的方法toArrayDeep()，这些方法以纯数组的形式递归地转储其数据内容。
toArrayDeep()
然后，我可以像这样一起使用整个系统：
// Get an instance of the taxonomy table data gateway $tax = new Taxonomy(); // query tree starting at Rodentia (id 180130), to a depth of 2 $tree = $tax->fetchTree(180130, 2); // dump out the array var_export($tree->toArrayDeep());
输出如下：
array ( 'tsn' => '180130', 'completename' => 'Rodentia', '_parent' => '179925', '_children' => array ( 0 => array ( 'tsn' => '584569', 'completename' => 'Hystricognatha', '_parent' => '180130', '_children' => array ( 0 => array ( 'tsn' => '552299', 'completename' => 'Hystricognathi', '_parent' => '584569', ), ), ), 1 => array ( 'tsn' => '180134', 'completename' => 'Sciuromorpha', '_parent' => '180130', '_children' => array ( 0 => array ( 'tsn' => '180210', 'completename' => 'Castoridae', '_parent' => '180134', ), 1 => array ( 'tsn' => '180135', 'completename' => 'Sciuridae', '_parent' => '180134', ), 2 => array ( 'tsn' => '180131', 'completename' => 'Aplodontiidae', '_parent' => '180134', ), ), ), 2 => array ( 'tsn' => '573166', 'completename' => 'Anomaluromorpha', '_parent' => '180130', '_children' => array ( 0 => array ( 'tsn' => '573168', 'completename' => 'Anomaluridae', '_parent' => '573166', ), 1 => array ( 'tsn' => '573169', 'completename' => 'Pedetidae', '_parent' => '573166', ), ), ), 3 => array ( 'tsn' => '180273', 'completename' => 'Myomorpha', '_parent' => '180130', '_children' => array ( 0 => array ( 'tsn' => '180399', 'completename' => 'Dipodidae', '_parent' => '180273', ), 1 => array ( 'tsn' => '180360', 'completename' => 'Muridae', '_parent' => '180273', ), 2 => array ( 'tsn' => '180231', 'completename' => 'Heteromyidae', '_parent' => '180273', ), 3 => array ( 'tsn' => '180213', 'completename' => 'Geomyidae', '_parent' => '180273', ), 4 => array ( 'tsn' => '584940', 'completename' => 'Myoxidae', '_parent' => '180273', ), ), ), 4 => array ( 'tsn' => '573167', 'completename' => 'Sciuravida', '_parent' => '180130', '_children' => array ( 0 => array ( 'tsn' => '573170', 'completename' => 'Ctenodactylidae', '_parent' => '573167', ), ), ), ), )
关于计算深度-或实际上每个路径的长度，发表您的评论。
假设您刚刚在表中插入了一个包含实际节点的新节点（longnames在上面的示例中），则新节点的ID由LAST_INSERT_ID()MySQL 返回，否则您可以通过某种方式获取它。
LAST_INSERT_ID()
INSERT INTO Closure (a, d, l) SELECT a, LAST_INSERT_ID(), l+1 FROM Closure WHERE d = 5 -- the intended parent of your new node UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID(), 0;
