数据库对层次结构的处理模型有好多种,可以根据自己的需求来设计模型,当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面,其他数据库比如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. |
在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 已用时间: 00: 00: 00.03 |
2). 查看叶子节点
代码如下 | 复制代码 |
select name from NAME 已选择13行。 已用时间: 00: 00: 00.01 |
3) 查看ROOT节点
代码如下 | 复制代码 |
select connect_by_root b.name CONNECT_BY_ROOTB.NAME 已用时间: 00: 00: 00.01 |
4). 查看路径
代码如下 | 复制代码 |
select sys_connect_by_path(b.name,'/') "path" path 已选择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 |
3). 查看叶子节点
代码如下 | 复制代码 |
mysql> SELECT b.`name` AS leaf_node FROM mysql> |
4) 查看路径
这一块没有简单的SQL实现,不过可以用MySQL的存储过程来实现同样的功能。
存储过程代码如下:
代码如下 | 复制代码 |
DELIMITER $$ USE `t_girl`$$ DROP PROCEDURE IF EXISTS `sp_show_list`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`() 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> |