MYSQL-SQL 递归节点

今天碰到一个同事需要实现一个递归的方法，用来比较好的实现子节点的查询。

如图，画的太丑，凑合看

查找1的子节点只需要输入 lft between 1 and 16 即可找到他下面所有的子节点

原始数据如下：

DROP TABLE IF EXISTS `ecms_node`;
CREATE TABLE `ecms_node` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(30) DEFAULT '',
  `title` char(50) DEFAULT '',
  `level` tinyint(5) unsigned DEFAULT '1' COMMENT '级别:1应用,2:模块,3:方法',
  `status` tinyint(1) unsigned DEFAULT '0' COMMENT '状态:1启用,0停用',
  `pid` tinyint(11) unsigned DEFAULT '1' COMMENT '父id',
  `super` tinyint(1) unsigned DEFAULT '0' COMMENT '是否检查权限,0检查,1不检查',
  `remark` varchar(1000) DEFAULT '',
  `lft` int(11) DEFAULT NULL,
  `rgt` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;



INSERT INTO `ecms_node` VALUES ('1', 'Admin', '后台', '1', '1', '0', '0', '后台管理', '1', null);
INSERT INTO `ecms_node` VALUES ('2', 'Index', '后台', '2', '1', '1', '1', '', null, null);
INSERT INTO `ecms_node` VALUES ('3', 'index', '后台首页', '3', '1', '2', '1', '', null, null);
INSERT INTO `ecms_node` VALUES ('4', 'nav', '导航', '3', '1', '2', '1', '', null, null);
INSERT INTO `ecms_node` VALUES ('5', 'top', '框架顶部', '3', '1', '2', '1', '', null, null);
INSERT INTO `ecms_node` VALUES ('6', 'welcome', '欢迎页', '3', '1', '2', '1', '', null, null);
INSERT INTO `ecms_node` VALUES ('7', 'Sys', '网站设置', '2', '1', '1', '0', '', null, null);
INSERT INTO `ecms_node` VALUES ('8', 'node', '节点管理', '3', '1', '7', '0', '', null, null);
INSERT INTO `ecms_node` VALUES ('9', 'nav', '导航', '3', '1', '7', '0', '', null, null);
INSERT INTO `ecms_node` VALUES ('10', 'User', '用户', '2', '1', '1', '0', '', null, null);
INSERT INTO `ecms_node` VALUES ('11', 'role', '角色', '3', '1', '10', '0', '', null, null);
INSERT INTO `ecms_node` VALUES ('14', 'Contents', '前台', '1', '1', '0', '0', '前台内容', null, null);
INSERT INTO `ecms_node` VALUES ('15', 'index', '用户', '3', '1', '10', '0', '网站用户', null, null);
INSERT INTO `ecms_node` VALUES ('16', 'edituser', '编辑用户', '4', '1', '10', '0', '修改用户信息', null, null);
INSERT INTO `ecms_node` VALUES ('17', 'userrole', '用户角色', '4', '1', '10', '0', '设置用户角色', null, null);
INSERT INTO `ecms_node` VALUES ('18', 'editrole', '编辑角色', '4', '1', '10', '0', '编辑角色信息', null, null);
INSERT INTO `ecms_node` VALUES ('19', 'rolenode', '角色权限', '4', '1', '10', '0', '设置角色权限', null, null);
INSERT INTO `ecms_node` VALUES ('20', 'editnode', '编辑节点', '4', '1', '7', '0', '编辑节点信息', null, null);

思路是从开始的节点往下找，找到最后一个之后再返回倒数第2层再往下找，一直重复，直到完结

DROP procedure  IF EXISTS test_lft;
create procedure test_lft(
 f_pid  INT,
 f_left INT,
 OUT f_id int,
 out f_rowid int
)
BEGIN
  declare _rowid int;
  declare  _tid  int;
  declare  _sid  int DEFAULT 0;

  SET _rowid=f_left+1;

  SELECT min(id) into _tid  FROM ecms_node WHERE pid=f_pid and lft=0;

  IF _tid>0 THEN

        WHILE _tid > 0 DO

            UPDATE ecms_node SET lft=_rowid WHERE id=_tid and lft=0 ;

            SET _sid=_tid;

            SELECT min(id) into _tid FROM ecms_node WHERE pid=_tid and lft=0 ;

            SET _rowid=_rowid+1;
         END WHILE;

    UPDATE ecms_node SET rgt=_rowid WHERE id=_sid;
    SELECT   pid,rgt into f_id,f_rowid FROM ecms_node WHERE id=_sid;

    ELSE
     UPDATE ecms_node SET rgt=_rowid WHERE id=f_pid;
     SELECT   pid,rgt into f_id,f_rowid FROM ecms_node WHERE id=f_pid;
    END IF;
END;

DROP procedure  IF EXISTS test_row;
create procedure test_row(
 f_pid  INT,
 f_left INT
)
BEGIN
  declare _row int default 0;

  UPDATE ecms_node SET lft=0 WHERE lft>f_left or lft is null;
  UPDATE ecms_node SET rgt=0 WHERE rgt>f_left or rgt is null;
  UPDATE ecms_node SET lft=f_left where id=f_pid;


  CALL test_lft(f_pid,f_left,@a,@b);

  SELECT count(*) into _row FROM ecms_node where lft=0 or rgt=0;

  WHILE _row >0 DO
    SET f_pid=@a,f_left=@b;
    CALL test_lft(f_pid,f_left,@a,@b);
    SELECT count(*) into _row FROM ecms_node where lft=0 or rgt=0;
  END WHILE;

END;

CALL test_row(1,1);

这种存储的好处在于能够很快的找出节点下面的子节点，缺点在于如果有节点变动，需要更新后面所有的节点。