mysql – 将邻接模型更新为嵌套集模型的存储过程
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 将邻接模型更新为嵌套集模型的存储过程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5365字，纯文字阅读大概需要8分钟。
内容图文
如果您还不知道,这两个模型是在关系数据库中存储树的最常用方法.
邻接模型：
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
嵌套集：
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
你也可以take a look here
我在MySQL中有一个邻接模型表,我决定将它转换为嵌套集模型.我需要一个代码来填充基于父列的LEFT和RIGHT列,将它们混合在一起并达到类似的效果
我需要的：
+-------------+----------------------+--------+-----+-----+
| category_id | name | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
| 1 | ELECTRONICS | NULL | 1 | 20 |
| 2 | TELEVISIONS | 1 | 2 | 9 |
| 3 | TUBE | 2 | 3 | 4 |
| 4 | LCD | 2 | 5 | 6 |
| 5 | PLASMA | 2 | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 1 | 10 | 19 |
| 7 | MP3 PLAYERS | 6 | 11 | 14 |
| 8 | FLASH | 7 | 12 | 13 |
| 9 | CD PLAYERS | 6 | 15 | 16 |
| 10 | 2 WAY RADIOS | 6 | 17 | 18 |
+-------------+----------------------+--------+-----+-----+
解决方法:
最后我找到了解决方案,但需要进行一些优化和调整才能使用我的案例,并且我添加了带ID的排序,以便对树进行排序,
答案主要来自here,所以归功于@deceze,
CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_recover`()
MODIFIES SQL DATA
BEGIN
DECLARE currentId, currentParentId CHAR(36);
DECLARE currentLeft INT;
DECLARE startId INT DEFAULT 1;
# Determines the max size for MEMORY tables.
SET max_heap_table_size = 1024 * 1024 * 512;
START TRANSACTION;
# Temporary MEMORY table to do all the heavy lifting in,
# otherwise performance is simply abysmal.
DROP TABLE IF EXISTS `tmp_tree`;
CREATE TABLE `tmp_tree` (
`id` bigint(36) NOT NULL DEFAULT '0',
`parent` char(36) DEFAULT NULL,
`lft` int(11) unsigned DEFAULT NULL,
`rgt` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX USING HASH (`parent`),
INDEX USING HASH (`lft`),
INDEX USING HASH (`rgt`)
) ENGINE = MEMORY
SELECT `id`,
`parent`,
`lft`,
`rgt`
FROM `tree`;
# Leveling the playing field.
UPDATE `tmp_tree`
SET `lft` = NULL,
`rgt` = NULL;
# Establishing starting numbers for all root elements.
WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `parent` = 0 AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO
UPDATE `tmp_tree`
SET `lft` = startId,
`rgt` = startId + 1
WHERE `parent` = 0
AND `lft` IS NULL
AND `rgt` IS NULL
ORDER BY `id` ASC
LIMIT 1;
SET startId = startId + 2;
END WHILE;
# Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
DROP INDEX `lft` ON `tmp_tree`;
DROP INDEX `rgt` ON `tmp_tree`;
CREATE INDEX `lft` USING BTREE ON `tmp_tree` (`lft`);
CREATE INDEX `rgt` USING BTREE ON `tmp_tree` (`rgt`);
# Numbering all child elements
WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO
# Picking an unprocessed element which has a processed parent.
SELECT `tmp_tree`.`id`
INTO currentId
FROM `tmp_tree`
INNER JOIN `tmp_tree` AS `parents`
ON `tmp_tree`.`parent` = `parents`.`id`
WHERE `tmp_tree`.`lft` IS NULL
AND `parents`.`lft` IS NOT NULL
ORDER BY `tmp_tree`.`id` DESC
LIMIT 1;
# Finding the element's parent.
SELECT `parent`
INTO currentParentId
FROM `tmp_tree`
WHERE `id` = currentId;
# Finding the parent's lft value.
SELECT `lft`
INTO currentLeft
FROM `tmp_tree`
WHERE `id` = currentParentId;
# Shifting all elements to the right of the current element 2 to the right.
UPDATE `tmp_tree`
SET `rgt` = `rgt` + 2
WHERE `rgt` > currentLeft;
UPDATE `tmp_tree`
SET `lft` = `lft` + 2
WHERE `lft` > currentLeft;
# Setting lft and rgt values for current element.
UPDATE `tmp_tree`
SET `lft` = currentLeft + 1,
`rgt` = currentLeft + 2
WHERE `id` = currentId;
END WHILE;
# Writing calculated values back to physical table.
UPDATE `tree`, `tmp_tree`
SET `tree`.`lft` = `tmp_tree`.`lft`,
`tree`.`rgt` = `tmp_tree`.`rgt`
WHERE `tree`.`id` = `tmp_tree`.`id`;
COMMIT;
DROP TABLE `tmp_tree`;
END
内容总结
以上是互联网集市为您收集整理的mysql – 将邻接模型更新为嵌套集模型的存储过程全部内容，希望文章能够帮你解决mysql – 将邻接模型更新为嵌套集模型的存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】