mysql存储过程实现无限分类

引用:

自己新建一个cms数据库，然后导入下面数据就可以了，输出的1000,1001,1002,1003...等等这些是我自己系统的数字字典，好方便和其他程序进行整合;P

引用:

采用改进前序遍历树算法

复制内容到剪贴板

代码:

-- phpMyAdmin SQL Dump
-- version 2.10.1
-- [url]http://www.phpmyadmin.net[/url]
--
-- 主机: localhost
-- 生成日期: 2007 年 08 月 23 日 14:36
-- 服务器版本: 5.0.41
-- PHP 版本: 5.2.3
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- 数据库: `cms`
--
-- --------------------------------------------------------
--
-- 表的结构 `pcms_channel`
--
DROP TABLE IF EXISTS `pcms_channel`;
CREATE TABLE IF NOT EXISTS `pcms_channel` (
  `cid` tinyint(3) unsigned NOT NULL auto_increment,
  `name` char(10) NOT NULL COMMENT '频道名称',
  `parentid` tinyint(4) NOT NULL COMMENT '父级ID',
  `lft` tinyint(4) NOT NULL COMMENT '左值',
  `rgt` tinyint(4) NOT NULL COMMENT '右值',
  `lv` tinyint(3) unsigned NOT NULL default '0' COMMENT '级层',
  `themeid` tinyint(3) unsigned NOT NULL default '1' COMMENT '使用的主题的ID',
  PRIMARY KEY  (`cid`),
  KEY `parentid` (`parentid`,`lft`,`rgt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
--
-- 导出表中的数据 `pcms_channel`
--
INSERT INTO `pcms_channel` (`cid`, `name`, `parentid`, `lft`, `rgt`, `lv`, `themeid`) VALUES
(1, 'phpoocms', 0, 1, 12, 0, 1),
(2, 'test', 1, 2, 7, 1, 1),
(3, 'te', 2, 3, 6, 2, 1),
(4, 'tes', 1, 8, 9, 1, 1),
(5, 'dd', 3, 4, 5, 3, 1),
(6, 'fromphp', 1, 10, 11, 1, 1);

复制内容到剪贴板

代码:

TRUNCATE TABLE `pcms_channel`

复制内容到剪贴板

代码:

DROP PROCEDURE IF EXISTS addChannel//
create procedure addChannel (in pid int,in name varchar(20))
BEGIN
DECLARE pr INT;/*右值*/
DECLARE lvv INT;/*层级*/
DECLARE aff INT;/*计数器*/
DECLARE af INT DEFAULT 0;
SET @result = null;
SELECT `rgt`,`lv` INTO pr,lvv FROM `pcms_channel` WHERE `cid` = pid;
IF pr THEN
START TRANSACTION;
UPDATE `pcms_channel` SET `lft`=`lft`+2 WHERE `lft`>pr;
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
UPDATE `pcms_channel` SET `rgt`=`rgt`+2 WHERE `rgt`>=pr;
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
INSERT INTO `pcms_channel` (`name`,`parentid`,`lft`,`rgt`,`lv`) VALUES (name,pid,pr,pr+1,lvv+1);
SELECT ROW_COUNT() INTO aff;
SET af = aff+af;
IF af >= 2 THEN
COMMIT;
SET @result = 1000;
SELECT 1000 AS result;
ELSE
ROLLBACK;
SET @result = 1002;
SELECT 1002 AS result;
END IF;
ELSE
SET @result = 1001;
SELECT 1001 AS result;
END IF;
END//
call addChannel (5,"aa")//

复制内容到剪贴板

代码:

DROP PROCEDURE IF EXISTS delChannel//
create procedure delChannel (in pid int)
BEGIN
DECLARE pl INT;
DECLARE pn INT;
DECLARE aff INT;
DECLARE af INT DEFAULT 0;
SET @result = null;
SET @parentid = null;
SET @name = null;
SELECT a.`lft`,IFNULL(COUNT(b.`cid`),0),a.`parentid`,a.`name` INTO pl,pn,@parentid,@name FROM `pcms_channel` AS a LEFT JOIN `pcms_channel` AS b ON a.`cid`=b.`parentid` WHERE a.`cid`=pid GROUP BY b.`parentid`;
IF pl&&!pn THEN
IF pl!=1 THEN
  START TRANSACTION;
  UPDATE `pcms_channel` SET `lft`=`lft`-2 WHERE `lft`>pl;
  SELECT ROW_COUNT() INTO aff;
  SET af = aff+af;
  UPDATE `pcms_channel` SET `rgt`=`rgt`-2 WHERE `rgt`>pl;
  SELECT ROW_COUNT() INTO aff;
  SET af = aff+af;
  DELETE FROM `pcms_channel` WHERE `cid` = pid;
  SELECT ROW_COUNT() INTO aff;
  SET af = aff+af;
  IF af >= 2 THEN
   COMMIT;
   SET @result = 1000;
   SELECT 1000 AS result;
  ELSE
   ROLLBACK;
   SET @result = 1002;
   SELECT 1002 AS result;
  END IF;
ELSE
  SET @result = 1004;
  SELECT 1004 AS result;
END IF;
ELSEIF pn&&pl THEN
SET @result = 1003;
SELECT 1003 AS result;
ELSE
SET @result = 1001;
SELECT 1001 AS result;
END IF;
END//
call delChannel (1)//

复制内容到剪贴板

代码:

DROP PROCEDURE IF EXISTS moveChannel//
create procedure moveChannel (pid int,tid int)
BEGIN
IF pid=1 THEN
SELECT 1004 AS result;
ELSE
IF pid!=tid THEN
  call delChannel (pid);
  IF @result=1000 THEN
   call addChannel (tid,@name);
   IF @result THEN
    SELECT 1000 AS result;
   ELSE
    call addChannel (@parentid,@name);
    SELECT @result AS result;
   END IF;
  ELSE
   SELECT @result AS result;
  END IF;
ELSE
  SELECT 1005 AS result;
END IF;
END IF;
SET @result=null;
SET @parentid=null;
SET @name=null;
END//
call moveChannel (1,1)//

复制内容到剪贴板

代码:

select cid,concat(repeat("　",lv)) from pcms_channel;

引用:

原帖由 wpl12 于 2007-9-7 17:04 发表
如果同级排列顺序要求呢...

引用:

原帖由 cybmm 于 2008-6-25 15:41 发表
存储过程不错,你是怎么管理存储过程啊?用什么工具呢?phpmyadmin好象不行吧??