我建一个树形结构的表
id 是自增的 chain 是其祖先节点的id以点号分隔的字串
CREATE TABLE `tree` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) default NULL,
`chain` varchar(32) default NULL,
`leaf` tinyint(1) default '1',
`name` varchar(30) default NULL,
`type` varchar(10) default NULL,
PRIMARY KEY (`id`),
KEY `tree_pid_id_fk` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE `tree`
ADD CONSTRAINT `tree_pid_id_fk` FOREIGN KEY (`parent_id`) REFERENCES `tree` (`id`);触发器如下
CREATE TRIGGER chain_trigger AFTER insert ON tree
FOR EACH ROW
BEGIN
DECLARE p_chain varchar(32) default null ;
DECLARE item_count int(11) DEFAULT 0;
select count(*) into item_count from tree where id = new.parent_id ;
if item_count = 0 then
update tree set chain = new.parent_id where id=new.id ;
else
select chain into p_chain from tree where id = new.parent_id ;
update tree set chain = CONCAT(p_chain,'.',new.parent_id) where id = new.id ;
end if;
END;
为了简化游标操作我写了个count
mysql 的版本是5.0.18 nt
id 是自增的 chain 是其祖先节点的id以点号分隔的字串
CREATE TABLE `tree` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) default NULL,
`chain` varchar(32) default NULL,
`leaf` tinyint(1) default '1',
`name` varchar(30) default NULL,
`type` varchar(10) default NULL,
PRIMARY KEY (`id`),
KEY `tree_pid_id_fk` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE `tree`
ADD CONSTRAINT `tree_pid_id_fk` FOREIGN KEY (`parent_id`) REFERENCES `tree` (`id`);触发器如下
CREATE TRIGGER chain_trigger AFTER insert ON tree
FOR EACH ROW
BEGIN
DECLARE p_chain varchar(32) default null ;
DECLARE item_count int(11) DEFAULT 0;
select count(*) into item_count from tree where id = new.parent_id ;
if item_count = 0 then
update tree set chain = new.parent_id where id=new.id ;
else
select chain into p_chain from tree where id = new.parent_id ;
update tree set chain = CONCAT(p_chain,'.',new.parent_id) where id = new.id ;
end if;
END;
为了简化游标操作我写了个count
mysql 的版本是5.0.18 nt
create trigger chain_trigger before insert on tree
for each row
begin
declare p_chain varchar(32) default null;
declare item_count int(11) default 0;
select count(*) into item_count from tree where id=new.parent_id;
if item_count=0 then
set new.chain=new.parent_id;
else
select chain into p_chain from tree where id=new.parent_id;
set new.chain=CONCAT(p_chain,'.',new.parent_id);
end if;
END;
//
delimiter ;