【www.bbyears.com--mysql教程】
数据库对层次结构的处理模型有好多种，可以根据自己的需求来设计模型，当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面，其他数据库比如Oracle 提供了现成的分析方法 connect by，而MySQL在这方面就显得有些薄弱了。 不过可以用MySQL的存储过程实现ORACLE类似的分析功能
这样，先来创建一个简单的数表。
create table country ( id number(2) not null, name varchar(60) not null);
create table country_relation (id number(2), parentid number(2));
插入一些数据
-- Table country.
insert into country (id,name) values (0,"Earth");
insert into country (id,name) values (2,"North America");
insert into country (id,name) values (3,"South America");
insert into country (id,name) values (4,"Europe");
insert into country (id,name) values (5,"Asia");
insert into country (id,name) values (6,"Africa");
insert into country (id,name) values (7,"Australia");
insert into country (id,name) values (8,"Canada");
insert into country (id,name) values (9,"Central America");
insert into country (id,name) values (10,"Island Nations");
insert into country (id,name) values (11,"United States");
insert into country (id,name) values (12,"Alabama");
insert into country (id,name) values (13,"Alaska");
insert into country (id,name) values (14,"Arizona");
insert into country (id,name) values (15,"Arkansas");
insert into country (id,name) values (16,"California");
-- Table country_relation.
insert into country_relation (id,parentid) values (0,NULL);
insert into country_relation (id,parentid) values (2,0);
insert into country_relation (id,parentid) values (3,0);
insert into country_relation (id,parentid) values (4,0);
insert into country_relation (id,parentid) values (5,0);
insert into country_relation (id,parentid) values (6,0);
insert into country_relation (id,parentid) values (7,0);
insert into country_relation (id,parentid) values (8,2);
insert into country_relation (id,parentid) values (9,2);
insert into country_relation (id,parentid) values (10,2);
insert into country_relation (id,parentid) values (11,2);
insert into country_relation (id,parentid) values (12,11);
insert into country_relation (id,parentid) values (13,11);
insert into country_relation (id,parentid) values (14,11);
insert into country_relation (id,parentid) values (15,11);
insert into country_relation (id,parentid) values (16,11);
在Oracle 里面，对这些操作就比较简单了，都是系统提供的。
比如下面四种情形：
1）. 查看深度，
select max(level) "level" from COUNTRY_RELATION a start with a.parentid is NULL
connect by PRIOR a.id = a.PARENTID
order by level;
level
----------
4
已用时间: 00: 00: 00.03
2）. 查看叶子节点
select name from
(
select b.name, connect_by_isleaf "isleaf"
from COUNTRY_RELATION a inner join country b on (a.id = b.id)
start with a.parentid is NULL connect by prior a.id = a.PARENTID
) T where T."isleaf" = 1;
NAME
--------------------------------------------------
Canada
Central America
Island Nations
Alabama
Alaska
Arizona
Arkansas
California
South America
Europe
Asia
Africa
Australia
已选择13行。
已用时间: 00: 00: 00.01
3） 查看ROOT节点
select connect_by_root b.name
from COUNTRY_RELATION a inner join country b on (a.id = b.id)
start with a.parentid is NULL connect by a.id = a.PARENTID
CONNECT_BY_ROOTB.NAME
--------------------------------------------------
Earth
已用时间: 00: 00: 00.01
4）. 查看路径
select sys_connect_by_path(b.name,"/") "path"
from COUNTRY_RELATION a inner join country b on (a.id = b.id)
start with a.parentid is NULL connect by prior a.id = a.PARENTID
order by level,a.id;
path
--------------------------------------------------
/Earth
/Earth/North America
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California
已选择16行。
已用时间: 00: 00: 00.01
接下来我们看看在MySQL 里面如何实现上面四种情形：
前三种都比较简单，可以很容易写出SQL。
1）查看深度
mysql> SELECT COUNT(DISTINCT IFNULL(parentid,-1)) AS LEVEL FROM country_relation
;
+-------+
| LEVEL |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
2）查看ROOT节点
mysql> SELECT b.`name` AS root_node FROM
-> (
-> SELECT id FROM country_relation WHERE parentid IS NULL
-> ) AS a, country AS b WHERE a.id = b.id;
+-----------+
| root_node |
+-----------+
| Earth |
+-----------+
1 row in set (0.00 sec)
3). 查看叶子节点
mysql> SELECT b.`name` AS leaf_node FROM
-> (
-> SELECT id FROM country_relation WHERE id NOT IN (SELECT IFNULL(parentid,
-1) FROM country_relation)
-> ) AS a, country AS b WHERE a.id = b.id;
+-----------------+
| leaf_node |
+-----------------+
| South America |
| Europe |
| Asia |
| Africa |
| Australia |
| Canada |
| Central America |
| Island Nations |
| Alabama |
| Alaska |
| Arizona |
| Arkansas |
| California |
+-----------------+
13 rows in set (0.00 sec)
mysql>
4） 查看路径
这一块没有简单的SQL实现，不过可以用MySQL的存储过程来实现同样的功能。
存储过程代码如下：
DELIMITER $$
USE `t_girl`$$
DROP PROCEDURE IF EXISTS `sp_show_list`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`()
BEGIN
-- Created by ytt 2014/11/04.
-- Is equal to oracle"s connect by syntax.
-- Body.
DROP TABLE IF EXISTS tmp_country_list;
CREATE TEMPORARY TABLE tmp_country_list (node_level INT UNSIGNED NOT NULL, node_path VARCHAR(1000) NOT NULL);
-- Get the root node.
INSERT INTO tmp_country_list SELECT 1, CONCAT("/",id) FROM country_relation WHERE parentid IS NULL;
-- Loop within all parent node.
cursor1:BEGIN
DECLARE done1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 1;
DECLARE v_parentid INT DEFAULT -1;
DECLARE v_node_path VARCHAR(1000) DEFAULT "";
DECLARE cr1 CURSOR FOR SELECT parentid FROM country_relation WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
OPEN cr1;
loop1:LOOP
FETCH cr1 INTO v_parentid;
IF done1 = 1 THEN
LEAVE loop1;
END IF;
SET i1 = i1 + 1;
label_path:BEGIN
DECLARE done2 INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
-- Get the upper path.
SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path;
-- Escape the outer not found exception.
IF done2 = 1 THEN
SET done2 = 0;
END IF;
INSERT INTO tmp_country_list
SELECT i1,CONCAT(IFNULL(v_node_path,""),"/",id) FROM country_relation WHERE parentid = v_parentid;
END;
END LOOP;
CLOSE cr1;
END;
-- Update node"s id to its real name.
update_name_label:BEGIN
DECLARE cnt INT DEFAULT 0;
DECLARE i2 INT DEFAULT 0;
SELECT MAX(node_level) FROM tmp_country_list INTO cnt;
WHILE i2 < cnt
DO
UPDATE tmp_country_list AS a, country AS b
SET a.node_path = REPLACE(a.node_path,CONCAT("/",b.id),CONCAT("/",b.name))
WHERE LOCATE(CONCAT("/",b.id),a.node_path) > 0;
SET i2 = i2 + 1;
END WHILE;
END;
SELECT node_path FROM tmp_country_list;
END$$
DELIMITER ;
调用结果：
mysql> CALL sp_show_list();
+-----------------------------------------------+
| node_path |
+-----------------------------------------------+
| /Earth |
| /Earth/North America |
| /Earth/South America |
| /Earth/Europe |
| /Earth/Asia |
| /Earth/Africa |
| /Earth/Australia |
| /Earth/North America/Canada |
| /Earth/North America/Central America |
| /Earth/North America/Island Nations |
| /Earth/North America/United States |
| /Earth/North America/United States/Alabama |
| /Earth/North America/United States/Alaska |
| /Earth/North America/United States/Arizona |
| /Earth/North America/United States/Arkansas |
| /Earth/North America/United States/California |
+-----------------------------------------------+
16 rows in set (0.04 sec)
Query OK, 0 rows affected (0.08 sec)
mysql>