【原创】MySQL 模拟Oracle邻接模型树形处理
程序员文章站
2022-04-06 10:18:00
...
数据库对层次结构的处理模型有好多种,可以根据自己的需求来设计模型,当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面,其他数据库比如Oracle提
数据库对层次结构的处理模型有好多种,可以根据自己的需求来设计模型,,当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面,其他数据库比如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.032). 查看叶子节点
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.013) 查看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.014). 查看路径
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。
下一篇: 关于包装类Character的实例解析