您正在使用SQL Antipatterns这本书称为“邻接表”的设计。 这是大多数程序员选择在关系数据库中表示层次结构数据的方式,因为它相当健壮,存储经济,易于理解并且可以使用外键约束来防止创建无效的父子关系。 但是,它的最大缺点是您无法在单个查询中检索整个树或子树。 除非使用的数据库不支持递归查询,否则您只能获取节点的直接子节点,其直接父节点或其兄弟姐妹。 递归仅由某些数据库实现,并且由于没有标准,它们都以不同的方式实现。
本书提供了许多用于处理层次结构的替代方案,我最喜欢的是Closure Table。 用这种方法,您无需将父级的行存储在类别表中,而是将关系数据存储在一个完全独立的表中。 您的类别表将具有唯一的类别ID,类别名称等,然后您的关闭表将存储以下信息:
祖先ID:给定节点的父级之一的ID
后代ID:给定节点的子代之一的ID
深度:从祖先节点到子节点向下多少层。
因此,您的类别表将如下所示:
CREATE TABLE categories (
cat_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
cat_name VARCHAR(64),
# Other columns...
) ENGINE=InnoDB;
和您关联的关闭表看起来像...
CREATE TABLE category_relations (
ancestor_id INT(11) UNSIGNED,
descendant_id INT(11) UNSIGNED,
depth INT(11) UNSIGNED
PRIMARY KEY (ancestor_id, descendant_id),
FOREIGN KEY (ancestor_id) REFERENCES categories.cat_id,
FOREIGN KEY (descendant_id) REFERENCES categories.cat_id
) ENGINE=InnoDB;
祖先和后代ID的组合是闭合表的主键。 深度字段将在以后用于优化某些查询类别,但是我们已经超越了自己。
现在,无论何时将新值插入类别表中,现在都将始终始终将至少一个节点插入类别关系表中。
INSERT INTO categories (
cat_name
) VALUES (
&