树形结构数据库的保存方式(mysql)

CREATE TABLE `groups` (
  `g_id` int(11) NOT NULL auto_increment COMMENT '主键',
  `g_name` varchar(100) NOT NULL COMMENT '用户组名（唯一约束',
  `g_authority` varchar(100) character set utf8 collate utf8_bin NOT NULL COMMENT '用户组权限（构思中',
  `g_parent` int(11) NOT NULL COMMENT '父节点',
  `g_status` bit(1) NOT NULL COMMENT '1 正常 0 禁用',
  `u_id` int(11) NOT NULL COMMENT '外键  操作人\n                      ',
  `g_cdate` datetime NOT NULL COMMENT '创建日期',
  PRIMARY KEY  (`g_id`),
  UNIQUE KEY `AK_uk_groups_name` (`g_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户组';
递归得到所有子节点:
delimiter //
create FUNCTION getchilds(prentid int)returns varchar(1000)
begin
declare dparents_ids  varchar(1000);
DECLARE results varchar(1000);
set dparents_ids=cast(prentid  as CHAR);
set results='0';
  while dparents_ids is not null do
set results=concat(results,',',dparents_ids);
select GROUP_CONCAT(g_id) into dparents_ids from groups where FIND_IN_SET(g_parent,dparents_ids) and g_status=1;
end while;
return results;
end; //
父节点递归(得到id集合):
delimiter //
create FUNCTION getparent(prentid int)returns varchar(1000)
begin
declare i_index INT;
declare temp int;
declare dparents_ids  varchar(1000);
set dparents_ids='';
set i_index =0;
set temp=prentid;
  while i_index <> -1 do
select g_parent into i_index from groups where g_id=temp;
set temp=i_index;
if i_index <> prentid and i_index <> -1
then
set dparents_ids=concat(dparents_ids,',',i_index);
end if;
end while;
return dparents_ids;
end; //