Perfect-World

Êý¾Ý¿â±£´æ·ÖÀàÐÅÏ¢µÄ±íÃüÃûΪ£ºcate£¬Æä½á¹¹ºÍ²âÊÔÓõļǼ·Ö±ðÈç1.jpgºÍ2.jpg
-- ----------------------------
-- Table structure for cate
-- ----------------------------
CREATE TABLE `cate` ( 
`id` int(5) NOT NULL auto_increment, 
`name` char(255) NOT NULL, 
`parent_id` int(4) NOT NULL, 
`nocontext` tinyint(1) NOT NULL, 
`path` char(255) NOT NULL default '0', 
`admin_id` tinyint(2) NOT NULL, 
`editor_id` tinyint(2) NOT NULL, 
`view` char(255) NOT NULL, 
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

1.jpg

2.jpg

1¡¢ÐÂÔö·ÖÀࣺ
-- ----------------------------
-- Procedure structure for cate_insert
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_insert`(_name CHAR(255), _parent_id INTEGER(4), _nocontext TINYINT(1), _admin_id TINYINT(2),_editor_id TINYINT(2),_view CHAR(255))
BEGIN
DECLARE X_max_id int default 0;
DECLARE X_parent_path char(255);
DECLARE X_path char(255);
select max(id) from cate into X_max_id ;
IF _parent_id!=0 THEN 
select `path` from `cate` where `id`= _parent_id  into X_parent_path; 
    SET X_path=CONCAT(X_parent_path,',',(X_max_id+1));
ELSE 
    SET X_path=X_max_id+1;
END IF;
INSERT INTO `cate` ( `name`, `parent_id`, `nocontext`, `path`, `admin_id`, `editor_id`, `view`)  VALUES  ( _name, _parent_id, _nocontext,X_path, _admin_id, _editor_id, _view);
END;;
DELIMITER ;

2¡¢±à¼­·ÖÀà
-- ----------------------------
-- Procedure structure for cate_update
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_update`(_id TINYINT(5),_name CHAR(255), _parent_id INTEGER(4), _nocontext TINYINT(1), _admin_id TINYINT(2),_editor_id TINYINT(2),_view CHAR(255))
BEGIN
DECLARE X_parent_path char(255);
DECLARE X_path_old char(255);
DECLARE X_path_new char(255);
DECLARE X_parent_id_old INT;
SELECT `path` from `cate` WHERE `id`=_id into  X_path_old;
IF _parent_id!=0 THEN 
    select `path` from `cate` where `id`= _parent_id  into X_parent_path; 
    SET X_path_new = CONCAT(X_parent_path,',',_id);
ELSE 
    SET X_path_new = _id;
END IF;
UPDATE `cate`  set  `name`=_name, `parent_id`=_parent_id, `nocontext`=_nocontext, `admin_id`=_admin_id, `editor_id`=_editor_id, `view`=_view WHERE `id`=_id;
UPDATE `cate` set `path`=REPLACE(`path`,X_path_old,X_path_new) ;
END;;
DELIMITER ;

3¡¢È¡³öËùÓзÖÀà
-- ----------------------------
-- Procedure structure for cate_selectAll
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_selectAll`()
BEGIN
SELECT * FROM `cate` ORDER BY `path`;
END;;
DELIMITER ;

4¡¢µÃµ½Ò»¸ö·ÖÀàµÄ·¾¶
-- ----------------------------
-- Procedure structure for cate_getRoute
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_getRoute`(_id INTEGER(5))
BEGIN
DECLARE x_path char(255);
select `path` from `cate` where `id`=_id  into x_path;
select * from `cate` where FIND_IN_SET(`id`,x_path)  order by `path`;
END;;
DELIMITER ;

5¡¢È¡³öͬһ¼¶·ÖÀà
-- ----------------------------
-- Procedure structure for cate_getBrother
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_getBrother`(_id INTEGER(5))
BEGIN
SELECT * FROM `cate` WHERE `parent_id`=(select `parent_id` from `cate` where `id`=_id ) order by `path`;
END;;
DELIMITER ;

6¡¢È¡³öµ¥¸ö·ÖÀà
-- ----------------------------
-- Procedure structure for cate_select
-- ----------------------------
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `cate_select`(_id INTEGER)
BEGIN
SELECT * FROM `cate` WHERE `id`=_id;
END;;
DELIMITER ;
Íê