如何使用SQL修复数据库中损坏的MPTT树(嵌套集)?

本文介绍了如何使用SQL修复数据库中损坏的MPTT树(嵌套集)?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个使用lft，rght和parent_id列存储在MySQL中的超过100,000条记录的MPTT树.现在，左/右值已损坏，而父ID仍保持不变.它将需要大量查询才能在应用程序层中对其进行修复.有没有什么好方法可以减轻数据库负担，并让它仅使用SQL重新计算左/右值?

I have an MPTT tree of over 100,000 records stored in MySQL using lft, rght and parent_id columns. Now the left/right values became corrupted, while the parent ids are still intact. It would require tons of queries to repair it in the application layer. Is there a good way to put the burden on the database and have it recalculate the left/right values using only SQL?

为了澄清，我需要重新计算嵌套集，而不是相邻记录的ID.

Just to clarify, I need to recalculate the numeric lft/rght values of a nested set, not the ids of neighboring records.

(来源: mysql )

(source: mysql)

推荐答案

使用SQL Server，以下脚本似乎对我有用.

Using SQL Server, following script seems to be working for me.

category_id name parent lft rgt lftcalc rgtcalc ----------- -------------------- ----------- ----------- ----------- ----------- ----------- 1 ELECTRONICS NULL 1 20 1 20 2 TELEVISIONS 1 2 9 2 9 3 TUBE 2 3 4 3 4 4 LCD 2 5 6 5 6 5 PLASMA 2 7 8 7 8 6 PORTABLE ELECTRONICS 1 10 19 10 19 7 MP3 PLAYERS 6 11 14 11 14 8 FLASH 7 12 13 12 13 9 CD PLAYERS 6 15 16 15 16 10 2 WAY RADIOS 6 17 18 17 18

脚本

SET NOCOUNT ON GO DECLARE @nested_category TABLE ( category_id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, parent INT, lft INT, rgt INT ); DECLARE @current_Category_ID INTEGER DECLARE @current_parent INTEGER DECLARE @SafeGuard INTEGER DECLARE @myLeft INTEGER SET @SafeGuard = 100 INSERT INTO @nested_category SELECT 1,'ELECTRONICS',NULL,NULL,NULL UNION ALL SELECT 2,'TELEVISIONS',1,NULL,NULL UNION ALL SELECT 3,'TUBE',2,NULL,NULL UNION ALL SELECT 4,'LCD',2,NULL,NULL UNION ALL SELECT 5,'PLASMA',2,NULL,NULL UNION ALL SELECT 6,'PORTABLE ELECTRONICS',1,NULL,NULL UNION ALL SELECT 7,'MP3 PLAYERS',6,NULL,NULL UNION ALL SELECT 8,'FLASH',7,NULL,NULL UNION ALL SELECT 9,'CD PLAYERS',6,NULL,NULL UNION ALL SELECT 10,'2 WAY RADIOS',6,NULL,NULL /* Initialize */ UPDATE @nested_category SET lft = 1 , rgt = 2 WHERE parent IS NULL UPDATE @nested_category SET lft = NULL , rgt = NULL WHERE parent IS NOT NULL WHILE EXISTS (SELECT * FROM @nested_category WHERE lft IS NULL) AND @SafeGuard > 0 BEGIN SELECT @current_Category_ID = MAX(nc.category_id) FROM @nested_category nc INNER JOIN @nested_category nc2 ON nc2.category_id = nc.parent WHERE nc.lft IS NULL AND nc2.lft IS NOT NULL SELECT @current_parent = parent FROM @nested_category WHERE category_id = @current_category_id SELECT @myLeft = lft FROM @nested_category WHERE category_id = @current_parent UPDATE @nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE @nested_category SET lft = lft + 2 WHERE lft > @myLeft; UPDATE @nested_category SET lft = @myLeft + 1, rgt = @myLeft + 2 WHERE category_id = @current_category_id SET @SafeGuard = @SafeGuard - 1 END SELECT * FROM @nested_category ORDER BY category_id SELECT COUNT(node.name), node.name, MIN(node.lft) FROM @nested_category AS node, @nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY 3, 1

Testscript ##

SET NOCOUNT ON GO DECLARE @nested_category TABLE ( category_id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, parent INT, lft INT, rgt INT, lftcalc INT, rgtcalc INT ); INSERT INTO @nested_category SELECT 1,'ELECTRONICS',NULL,1,20,NULL,NULL UNION ALL SELECT 2,'TELEVISIONS',1,2,9,NULL,NULL UNION ALL SELECT 3,'TUBE',2,3,4,NULL,NULL UNION ALL SELECT 4,'LCD',2,5,6,NULL,NULL UNION ALL SELECT 5,'PLASMA',2,7,8,NULL,NULL UNION ALL SELECT 6,'PORTABLE ELECTRONICS',1,10,19,NULL,NULL UNION ALL SELECT 7,'MP3 PLAYERS',6,11,14,NULL,NULL UNION ALL SELECT 8,'FLASH',7,12,13,NULL,NULL UNION ALL SELECT 9,'CD PLAYERS',6,15,16,NULL,NULL UNION ALL SELECT 10,'2 WAY RADIOS',6,17,18,NULL,NULL /* Initialize */ UPDATE @nested_category SET lftcalc = 1 , rgtcalc = 2 WHERE parent IS NULL DECLARE @current_Category_ID INTEGER DECLARE @current_parent INTEGER DECLARE @SafeGuard INTEGER DECLARE @myRight INTEGER DECLARE @myLeft INTEGER SET @SafeGuard = 100 WHILE EXISTS (SELECT * FROM @nested_category WHERE lftcalc IS NULL) AND @SafeGuard > 0 BEGIN SELECT @current_Category_ID = MAX(nc.category_id) FROM @nested_category nc INNER JOIN @nested_category nc2 ON nc2.category_id = nc.parent WHERE nc.lftcalc IS NULL AND nc2.lftcalc IS NOT NULL SELECT @current_parent = parent FROM @nested_category WHERE category_id = @current_category_id SELECT @myLeft = lftcalc FROM @nested_category WHERE category_id = @current_parent UPDATE @nested_category SET rgtcalc = rgtcalc + 2 WHERE rgtcalc > @myLeft; UPDATE @nested_category SET lftcalc = lftcalc + 2 WHERE lftcalc > @myLeft; UPDATE @nested_category SET lftcalc = @myLeft + 1, rgtcalc = @myLeft + 2 WHERE category_id = @current_category_id SELECT * FROM @nested_category WHERE category_id = @current_parent SELECT * FROM @nested_category ORDER BY category_id SET @SafeGuard = @SafeGuard - 1 END SELECT * FROM @nested_category ORDER BY category_id SELECT COUNT(node.name), node.name, MIN(node.lft) FROM @nested_category AS node, @nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY 3, 1