对于递归关系的表,如何做到级联更新

[yujing890226]

RT

设有表:

USE `test`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` 
(
 `id` int(11) COMMENT '主键',
 `parent` int(11) COMMENT '父节点',
 `name` varchar(20) DEFAULT NULL COMMENT '名称',
 `cost` float DEFAULT NULL COMMENT '成本',
 `budget` float DEFAULT NULL COMMENT '预算',
 `progress` float DEFAULT NULL COMMENT '进度'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
ALTER TABLE `test` ADD CONSTRAINT PK_TEST1 PRIMARY KEY (`id`);
ALTER TABLE `test` ADD CONSTRAINT FK_TEST1_PARENT_TEST FOREIGN KEY(`parent`) 
REFERENCES test(`id`);	    
INSERT INTO `test`(`id`,`name`) VALUES(-1,'结束标记');
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (1,-1,'复杂任务1',200);
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (2,1,'复杂任务1-子项--简单任务2',100);
INSERT INTO `test`(`id`,`parent`,`name`,`budget`) VALUES (3,1,'复杂任务1-子项--简单任务3',100);

事务:
更新3
update `test` set cost =50 where id =3;
期望的结果:
id parent name cost budget progress
1 -1 '复杂任务1' 50 200 0.25
2 1 '复杂任务1-子项--简单任务2' null 100 null
3 1 '复杂任务1-子项--简单任务3' 50 100 0.5

更新2
update `test` set cost =50 where id =2;
期望的结果:
id parent name cost budget progress
1 -1 '复杂任务1' 100 200 0.5
2 1 '复杂任务1-子项--简单任务2' 50 100 0.5
3 1 '复杂任务1-子项--简单任务3' 50 100 0.5

已有实现:

delimiter //
-- 求指定节点的总成本。(总成本=子节点的累加和)
DROP FUNCTION IF EXISTS test_sum//
CREATE FUNCTION test_sum(p_parent int) RETURNS FLOAT
BEGIN
DECLARE result FLOAT DEFAULT 0.0;
SELECT SUM(cost) into result FROM test where id in(select id from test where parent = p_parent);
       IF(result IS NULL) then 
 set result = 0;
       END IF ;
RETURN result;
end//

-- 更新指定节点的进度,并更新此节点的所有父节点的成本和进度
drop function if exists getParentList;//
create function getParentList(childId int) returns varchar(1000)
begin
declare sTemp varchar(1000);
declare sTempPar int;
declare l_cost float default 0;
declare l_budget float default 0;
declare l_parent int;
set sTemp = '$';
set sTempPar = childId;
select cost into l_cost from test where id=childId;
select budget into l_budget from test where id=childId;
UPDATE test SET progress = l_cost/l_budget where id = childId;
while sTempPar != -1 do	
select budget into l_budget from test where id = sTempPar;
select parent into l_parent from test where id = sTempPar;
set sTemp = concat(sTemp,',',l_parent);
update test set cost=test_sum(l_parent) where id=l_parent;
IF(sTempPar != childId) then
UPDATE test SET progress = test_sum(sTempPar)/l_budget where id=sTempPar;
end if;	
select group_concat(parent) into sTempPar from test where parent<>id and find_in_set(id,sTempPar)>0;		
end while;
return sTemp;
end//
delimiter ;

语句序列:

        update `test` set cost =50 where id =3;
	select 	getParentList(3);
	update `test` set cost =50 where id =2;
	select 	getParentList(2);

问题:
语句序列不合预期,期望语句序列:
update `test` set cost =50 where id =3;
update `test` set cost =50 where id =2;
如果使用触发器实现，则在插入/更新的时机,不能修改同一张表中的数据。

以上。求指导,求扫盲。多谢。

[buyong]

取消外键 更新时语句保证顺序 增加对parent的validation

[thomasLand]

①做此表的一个备份test_back ②做一道test_bak的触发器,在test_bak表改变时候,备份test_bak到test_bak。并调用函数。 ③操作时针对test_back,查询时针对test。 这样做,显然开销比较大。

[thomasLand]

②做一道test_bak的触发器,在test_bak表改变时候,备份test_bak到test。并调用函数。

[thomasLand]

核心就是将test_bak当做test表的一个外观。用来接收参数,转发语句。test_bak的数据一致性甚至都不需要额外的精力维护。