我有一个使用lft,rght和parent_id列存储在MysqL中的超过100,000条记录的MPTT树.现在左/右的值被破坏,而父ids仍然完好无损.在应用层需要大量的查询来修复它.有没有一个很好的方法来负担数据库,并重新计算左/右的值只使用sql？
为了澄清,我需要重新计算一个nested set的数值lft / rght值,而不是相邻记录的id.
The Nested Set http://dev.mysql.com/tech-resources/articles/hierarchical-data-4.png
解决方法
使用sql Server,以下脚本似乎适用于我.
输出测试脚本
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 UNION ALL SELECT 2,'TELEVISIONS',1,NULL UNION ALL SELECT 3,'TUBE',2,NULL UNION ALL SELECT 4,'LCD',NULL UNION ALL SELECT 5,'PLASMA',NULL UNION ALL SELECT 6,'PORTABLE ELECTRONICS',NULL UNION ALL SELECT 7,'MP3 PLAYERS',6,NULL UNION ALL SELECT 8,'FLASH',7,NULL UNION ALL SELECT 9,'CD PLAYERS',NULL UNION ALL SELECT 10,'2 WAY RAdioS',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,rgt INT,lftcalc INT,rgtcalc INT
);
INSERT INTO @nested_category
SELECT 1,20,9,3,4,5,8,10,19,11,14,12,13,15,16,17,18,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),1
