TreeOne

一个适用性功能性极佳的多层级树型数据库存储结构设计模型最佳实际，可宽泛用于我的项目开发工作中。

（以下示例环境默认均为MySQL）

表的字段数据结构

CREATE TABLE `tree` (  `id` int(11) NOT NULL,  `name` varchar(30) NOT NULL,   `level` mediumint(4) NOT NULL DEFAULT '1', // 层级地位  `branch` mediumint(4) NOT NULL DEFAULT '1', // 分支code  `path` varchar(50) NOT NULL DEFAULT '1' // 先辈门路) ENGINE=InnoDB DEFAULT CHARSET=utf8;

个性

  • 十分精美的外围数据表构造
  • 很强的灵便扩展性
  • 高效性能
  • 不便简略的SQL查问
  • 简直反对全副的树状分类构造的利用场景

数据记录示例

idnamelevelbranchpath
1----cate-1111
3--------cate-1-1211.1
4------------cate-1-1-1311.1.1
5--------cate-1-2211.2
6------------cate-1-2-1311.2.1
2----cate-2122
7--------cate-2-1222.1
9------------cate-2-1-1322.1.1
10------------cate-2-1-2322.1.2
8--------cate-2-2222.2

查问实例

  • 一条查问渲染整颗树

    SELECT * FROM `tree` ORDER BY `branch`, `path`;

  • 获取树的最大深度/层级

    SELECT MAX(`level`) FROM `tree`;

  • 获取某个分支的根

    SELECT * FROM `tree` WHERE `branch` = 1 ORDER BY `path` ASC LIMIT 1;

  • 获取某节点的下级父节点

    SELECT * FROM `tree` WHERE `branch` = 1 AND `path` = '1.1';

  • 获取某节点的儿子节点(与 儿子节点的所有兄弟节点 等同)

    SELECT * FROM `tree` WHERE `level` = 3 AND `branch` = 1 AND `path` > '1.1' AND `path` < '1.2';

  • 获取某节点的所有后辈节点（始终到叶子节点）

    SELECT * FROM `tree` WHERE `branch` = 1 AND `path` > '1.1' AND `path` < '1.2' ORDER BY `path` ASC;

  • 获取某节点的所有先辈节点 (模式如 "面包屑" 门路的场景)

    SELECT * FROM `tree` WHERE `level` < 3 AND `path` IN ('1.2', '1') ORDER BY `path` ASC

  • 获取前一个兄弟节点

    SELECT * FROM `tree` WHERE `level` = 2 AND `branch` = 2 AND `path` < '2.2' ORDER BY `path` DESC LIMIT 1;

  • 获取后一个兄弟节点

    SELECT * FROM `tree` WHERE `level` = 2 AND `branch` = 2 AND `path` > '2.2' ORDER BY `path` ASC LIMIT 1;