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; //
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户组';
递归得到所有子节点:
delimiter //
create FUNCTION getchilds(prentid int)returns varchar(1000)
begin
declare dparents_ids
DECLARE results varchar(1000);
set dparents_ids=cast(prentid
set results='0';
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
set dparents_ids='';
set i_index =0;
set temp=prentid;
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; //