mysql - 搜索嵌套集

Question

我有一个 MySQL 表，它的作用类似于嵌套集，以便包含类别层次结构。表架构如下所示:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `parent_id` int(11) default NULL,
  `lft` int(11) default NULL,
  `rgt` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_categories_on_parent_id_and_name` (`parent_id`,`name`)
)

lft 和 rgt 定义节点的左右边界(嵌套集的工作方式是每个节点的 id 落在其父节点的边界内)，并且 parent_id 指定父节点。唯一索引允许多个类别具有相同的名称，只要它们不具有相同的父级即可。

我正在尝试找出一种基于层次结构在集合中查找特定节点的正确方法。例如，如果我查找 foo/bar/baz，我想检索名为 baz 的节点，其父节点名为 bar，其父节点名为 foo。显然，我不能只按名称搜索，因为可能有多个类别具有相同的名称。

我能想到的方法是找到最顶层的类别，然后找到具有给定名称的每个后续类别，其父 ID 是先前找到的类别的父 ID，但这对我来说似乎不是很有效。有没有更好的方法来搜索嵌套集？

Answer 1

我不相信有一种非常干净和有效的方法可以用嵌套集来做到这一点。将节点的祖先列表存储在非规范化列中可以有效地提供此功能，但我不建议实现它。

虽然有一个不错的方法，它是 1 个查询，可以方便地命中您已有的索引。您正在为目标节点的每个深度级别查看一个连接。

对于你的例子 foo-bar-baz



<p>select c3.*<br/>
from categories c1<br/>
inner join categories c2 on c2.parent_id = c1.id AND c2.name = 'bar'<br/>
inner join categories c3 on c3.parent_id = c2.id AND c2.name = 'baz'<br/>
where c1.name = 'foo'<br/></p>



它不是最好的，但它可能是你最好的选择，除非你想努力存储一堆非规范化的信息。在代码中生成 SQL 也相当简单。