数据库对层次结构的处理模型有好多种，可以根据自己的需求来设计模型，当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面，其他数据库比如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 nullconnect by prior a.id = a.parentidorder by level; level---------- 4已用时间: 00: 00: 00.03
2）. 查看叶子节点
select name from (select b.name, connect_by_isleaf isleaffrom 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--------------------------------------------------canadacentral americaisland nationsalabamaalaskaarizonaarkansascaliforniasouth americaeuropeasiaafricaaustralia已选择13行。已用时间: 00: 00: 00.01
3） 查看root节点
select connect_by_root b.namefrom 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 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>