通向架构师的道路06（第六天）之漫谈基于数据库的权限系统的设计(3)

四、改进T_SYSTEM_MENU表的设计

前面我们用的是Oracle特有的递归SQL将树形菜单在从数据库中选取出来时就已经是一颗树的结构了，但是像MYSQL，SQL SERVER， DB2等可能不带有这样的特SQL，那就需我们自己动手去写递归，还有就是很多工程用的是jquery的tree或者是其它相关的ajax tree，这些tree都需要用到一个字段叫level（此处指深度、层次的意思），如果按照原来的表结构，要取得这个level，恐怕是要写递归算法了。就算有些数据库有类似的语句，那也需要你去修改你的SQL语句从未影响了性能与通用性。

我们在这边说，我们无论什么数据库，如果都用相同的SQL就能把我们需要的东西在数据库中就排好树形结构然后一次性选取出来，那应该有多好啊。答案是有的，在原来的T_SYSTEM_MENU表中改动也不大，只需要增加两个字段即可，即：lft与rgt（left, right），这种设计其实已经有了，我在此只不过结合实际例子把它应用到实际上，并且进一步详细描述如果来实现它，它就是被称为：

左右值无限分类实现算法也称为预排序遍历树算法，对于这种层次型数据（Hierarchical Data）一般我们有两种设计方法： ü 毗邻目录模式(adjacencylist model)

ü 预排序遍历树算法(modifiedpreorder tree traversal algorithm)

4.1 基于lft, rgt的无限分类算法

我们来看一个图，下面我们把我们原有的菜单画成下面这样的层次关系：

我们把原有的系统菜单画成了一个个的椭圆，最外层的就是我们的菜单，然后在每个椭园的两个端点即left与right，按照从左->右，开始用数字来标号，上面这个图中可以看到最外层这个大椭园的lft(左)为1，它的rgt(右)为24。

那么我们可以用一条标准的SQL，而非什么数据库自带的特有的、特殊的SQL来显示出这个树形菜单，来看下面的SQL： SELECT

node.menu_id menuId, node.menu_descr menuDescr, node.lft, node.rgt,

node.menu_url menuUrl,

(COUNT(parent.menu_id)-1) menuLevel, node.menu_pid pid FROM t_sys_menu node, t_sys_menu parent WHERE

node.lftBETWEEN parent.lftAND parent.rgt AND node.menu_descr!='菜单'

GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid

ORDERBY node.lft 来看显示的结果

看看上面这个结果，怎么样？

ü 树形结构也有了（可以用于dtree来显示）； ü 层次level也有了（可以用于ajax的一些tree）；

ü 我们用的SQL又是最标准的所有的数据库都能用到的SQL；

尝到甜头了是吧？那我们下面来看如何对这样的基于t, rgt的数据结构来作插入操作？

4.2 如何在现有节点中插入新的子节点

如果现在我们要在“报表查询”这个圆里加入一个菜单，假设我们就叫“周报”，那么再来看这个原有的图发生了什么样的改变，来看：

看到么，原有的最外层椭园的rgt+2，原有的报表查询这个园的右边界呢？是不是也加了2啊？而原有的“月报”这个圆的lft加了多少？也是+2！ 那么来看“周报”这个圆的lft与rgt关系： “周报”的lft= “报表查询”这个圆的lft+1 “周报”的rgt=“报表查询”这个圆的lft+2

于是我们就可以整理出在原有叶子中插入child的公式： 第一步：选取要被插入new child的外面这个圆的lft的值

第二步：原有的数据中所有的rgt如果>第一步中得到的lft的值，那么全部+2 第三步：原有的数据中所有的lft如果>第一步中得到的lft的值，那么全部+2

第四步：将插入的节点的lft与rgt的设计，新节点的lft =第一步中的lft+1，新节点的rgt=第一步中 的lft+2

来看一个具体的例子：

我们要在“报表查询”即menu_id=?101? 中插入一个新的菜单，叫“周报”，下面是按照上面四步算法的相关SQL语句： 第一步 SELECT lftFROM t_sys_menuwhere menu_id='101'; 这一步我们得到的值为：2 第二步： UPDATE t_sys_menuSET rgt = rgt +2WHERE rgt >2; 第三步： UPDATE t_sys_menuSET lft = lft +2WHERE lft >2; 第四步： INSERTINTO t_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES('113','周报','周报的url', (2+1), (2 +2)); 插完后我们运行查询SQL： SELECT node.menu_id menuId, node.menu_descr menuDescr, node.lft, node.rgt, node.menu_url menuUrl, (COUNT(parent.menu_id)-1) menuLevel, node.menu_pid pid FROM t_sys_menu node, t_sys_menu parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.menu_descr!='菜单' GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ORDERBY node.lft Look, 数据正确无误，我们来看整个t_sys_menu表里的数据：

Look，整个最外层的“圆”，右边界增加了2，从原有的24变成了26。

1.3 如何插入一个新的节点

上面讲的是在原有的节点中插入一个子节点，现在来讲，如何插入一个新的节点，比如说：

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库通向架构师的道路06（第六天）之漫谈基于数据库的权限系统的设计(3)在线全文阅读。