如何处理MySQL/Oracle邻接模型树形结构问题
发表于:2024-11-29 作者:千家信息网编辑
千家信息网最后更新 2024年11月29日,下文给大家带来关于如何处理MySQL/Oracle邻接模型树形结构问题,感兴趣的话就一起来看看这篇文章吧,相信看完如何处理MySQL/Oracle邻接模型树形结构问题对大家多少有点帮助吧。数据库对层次
千家信息网最后更新 2024年11月29日如何处理MySQL/Oracle邻接模型树形结构问题
下文给大家带来关于如何处理MySQL/Oracle邻接模型树形结构问题,感兴趣的话就一起来看看这篇文章吧,相信看完如何处理MySQL/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 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 "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--------------------------------------------------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 < 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>
看了以上关于如何处理MySQL/Oracle邻接模型树形结构问题详细内容,是否有所收获。如果想要了解更多相关,可以继续关注我们的行业资讯板块。
模型
结构
节点
树形
问题
数据
过程
存储
功能
叶子
情形
数据库
深度
路径
分析
设计
选择
薄弱
接下来
下文
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
我军网络安全架构的背景
富士通服务器管理口地址
香港互联网科技公司排行榜
网络安全大赛ctf最受欢迎队
网络安全职业技能大赛预赛题
neo4j数据库分析
主域服务器两个网段怎么设置
服务器网上设置
华为防火墙服务器映射配置命令
电脑上的数据库怎么连接
武汉 万户网络技术有限公司
2020年中国数据库市场
与网络技术有关的ppt
数据库中如何查询触发器
我的世界服务器Alt
服务器上架安装标准规范
电子化采购软件开发
中小学生网络安全教育美篇
退款流程数据库设计
智能家居数据库系统的概要设计
服务器需要显示器吗
不适合计算机网络技术
金发网络技术
番禺物联网软件开发
数据库如何防止爬虫
武汉 万户网络技术有限公司
网络安全陈承曦个人资料
怎样不花钱在我的世界开服务器
行走喵星人总显示解析服务器异常
联想服务器进bios