Mysql树状模型

Mysql没有Oralce中的Connect By层次查询方法，下面介绍一种方法可以供大家参考

例如:要建立一个如下的树状模型，如果使用子父表left join的方式需要套好几层，使用左右节点方法就来的比较容易；

 

先建立表结构:

/*创建百科分类表*/
/*==============================================================*/
/* DBMS sClassifyName:      MySQL 5.0                                    */
/*==============================================================*/
drop table if exists TB_BAIKE_CLASSIFY;

/*==============================================================*/
/* Table: TB_BAIKE_CLASSIFY                                     */
/*==============================================================*/
create table TB_BAIKE_CLASSIFY
(
   iClassifyID_FK       int(20) primary key auto_increment,
   sClassifyName       varchar(20),
   sClassifyCode       varchar(20),
   iLft                         int(10) not null,
   iRgt                        int(10) not null       
);

 

/*初始化值*/

INSERT INTO TB_BAIKE_CLASSIFY VALUES
    (1,'百科分类','BaiKeAllType',1,20),(2,'自然','Nature',2,9),(3,'植物','Plant',3,4),
    (4,'动物','Animal',5,6),(5,'宇宙天文','Astronomy',7,8),(6,'历史','History',10,19),
    (7,'各国历史','AllCountryHistory',11,14),(8,'中国历史','ChinaHistory',12,13),
    (9,'历史学','HistorySubject',15,16),(10,'历史文人','HistoryCulture',17,18);

 

 

    模型就算是建好了，接下来的就是怎么有层次的显示数据，如果插入新节点，删除节点；

    再给一张直观一点的图，

 

 

/*列出全部元素*/
SELECT node.iClassifyID_FK,node.sClassifyName FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt AND parent.sClassifyName = '百科分类' ORDER BY node.iLft;

/*列出所有叶子节点*/
SELECT sClassifyName FROM TB_BAIKE_CLASSIFY WHERE iRgt = iLft + 1;

/*列出元素路径*/
SELECT parent.sClassifyName FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt AND node.sClassifyName = '中国历史' ORDER BY parent.iLft;

/*查询节点深度*/
SELECT node.sClassifyName, (COUNT(parent.sClassifyName) - 1) AS depth FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt GROUP BY node.sClassifyName ORDER BY node.iLft;

/*分层展示   powerDesigner中没有实现此视图*/
SELECT CONCAT( REPEAT('-----', COUNT(parent.sClassifyName) - 1), node.sClassifyName) AS sClassifyName FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt GROUP BY node.sClassifyNameORDER BY node.iLft;

/*分层视图 为了方便查询目录*/
create view BaiKeClassifyList As SELECT CONCAT( REPEAT('-----', COUNT(parent.sClassifyName) - 1), node.sClassifyName) AS sClassifyName FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt GROUP BY node.sClassifyName ORDER BY node.iLft;

/*子树的深度*/
SELECT node.sClassifyName, (COUNT(parent.sClassifyName) - (sub_tree.depth + 1)) AS depth
FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent,TB_BAIKE_CLASSIFY AS sub_parent,
(      SELECT node.sClassifyName, (COUNT(parent.sClassifyName) - 1) AS depth
        FROM TB_BAIKE_CLASSIFY AS node,
        TB_BAIKE_CLASSIFY AS parent
        WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt
        AND node.sClassifyName = '历史'
        GROUP BY node.sClassifyName
        ORDER BY node.iLft
 )AS sub_tree
WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt
    AND node.iLft BETWEEN sub_parent.iLft AND sub_parent.iRgt
    AND sub_parent.sClassifyName = sub_tree.sClassifyName GROUP BY node.sClassifyName
ORDER BY node.iLft;


/*查找子树中叶节点 纵向同一深度的节点*/
SELECT node.sClassifyName, (COUNT(parent.sClassifyName) - (sub_tree.depth + 1)) AS depth
FROM TB_BAIKE_CLASSIFY AS node,TB_BAIKE_CLASSIFY AS parent,TB_BAIKE_CLASSIFY AS sub_parent,
(      SELECT node.sClassifyName, (COUNT(parent.sClassifyName) - 1) AS depth
        FROM TB_BAIKE_CLASSIFY AS node,
        TB_BAIKE_CLASSIFY AS parent
        WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt
        AND node.sClassifyName = '历史'
        GROUP BY node.sClassifyName
        ORDER BY node.iLft
 )AS sub_tree
WHERE node.iLft BETWEEN parent.iLft AND parent.iRgt
    AND node.iLft BETWEEN sub_parent.iLft AND sub_parent.iRgt
    AND sub_parent.sClassifyName = sub_tree.sClassifyName
GROUP BY node.sClassifyName HAVING depth >= 1        /*可以通过depth查找指定深度的所有节点*/
ORDER BY node.iLft;

/*增加节点  在根节点上增加*/
LOCK TABLE TB_BAIKE_CLASSIFY WRITE;
SELECT @myRight := iRgt FROM TB_BAIKE_CLASSIFY
WHERE sClassifyName = '自然';
UPDATE TB_BAIKE_CLASSIFY SET iRgt = iRgt + 2 WHERE iRgt > @myRight;
UPDATE TB_BAIKE_CLASSIFY SET iLft = iLft + 2 WHERE iLft > @myRight;
INSERT INTO TB_BAIKE_CLASSIFY(sClassifyName,sClassifyCode,iLft, iRgt) VALUES('生物','ShengWu', @myRight + 1, @myRight + 2);
UNLOCK TABLES;

/*增加节点  在叶子上增加节点*/
LOCK TABLE TB_BAIKE_CLASSIFY WRITE;
SELECT @myLeft := iLft FROM TB_BAIKE_CLASSIFY
WHERE sClassifyName = '历史文人';
UPDATE TB_BAIKE_CLASSIFY SET iRgt = iRgt + 2 WHERE iRgt > @myLeft;
UPDATE TB_BAIKE_CLASSIFY SET iLft = iLft + 2 WHERE iLft > @myLeft;
INSERT INTO TB_BAIKE_CLASSIFY(sClassifyName, iLft, iRgt) VALUES('斯大林', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;

/*删除节点  节点在根节点上*/
LOCK TABLE TB_BAIKE_CLASSIFY WRITE;
SELECT @myLeft := iLft, @myRight := iRgt, @myWidth := iRgt - iLft + 1
    FROM TB_BAIKE_CLASSIFY
    WHERE sClassifyName = '生物';
DELETE FROM TB_BAIKE_CLASSIFY WHERE iLft BETWEEN @myLeft AND @myRight;
UPDATE TB_BAIKE_CLASSIFY SET iRgt = iRgt - @myWidth WHERE iRgt > @myRight;
UPDATE TB_BAIKE_CLASSIFY SET iLft = iLft - @myWidth WHERE iLft > @myRight;
UNLOCK TABLES;
/

/*删除节点 不删除此节点下的子节点*/
LOCK TABLE TB_BAIKE_CLASSIFY WRITE;
SELECT @myLeft := iLft, @myRight := iRgt, @myWidth := iRgt - iLft + 1
FROM TB_BAIKE_CLASSIFY
WHERE sClassifyName = '历史';
DELETE FROM TB_BAIKE_CLASSIFY WHERE iLft = @myLeft;
UPDATE TB_BAIKE_CLASSIFY SET iRgt = iRgt - 1, iLft = iLft - 1 WHERE iLft BETWEEN @myLeft AND @myRight;
UPDATE TB_BAIKE_CLASSIFY SET iRgt = iRgt - 2 WHERE iRgt > @myRight;
UPDATE TB_BAIKE_CLASSIFY SET iLft = iLft - 2 WHERE iLft > @myRight;
UNLOCK TABLES;

如果只是显示二层目录结构，不需要使用上面方式，实现比较麻烦；此时可以使用子父表连接的方式，或用程序分两次从数据库中获取目录记录的方式；