想设计DISCUZ这样的论坛帖子效果,用户可以对帖子进行回复,也可以对回复内容进行回复。我看了一下discuz的设计是评论有专门的数据库,应该是需要用到递归查找。
如果是用下面的办法:
drop database recur;
create database recur;
use recur;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
delimiter //
CREATE PROCEDURE showChildList (IN rootId INT,IN)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0);
select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;
END;
//
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
//
delimiter ;
call showChildList(1);
call showChildList(3);
call showChildList(5);
如果多个会话同时调用showChildList 的话,一个临时数据库会不会不安全,我想过一个rootid建一个临时数据库也不现实,请问有没有更好的办法解决? 谢谢。
如果是用下面的办法:
drop database recur;
create database recur;
use recur;
CREATE TABLE `treenodes` (
`id` int(11) NOT NULL,
`nodename` varchar(20) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
delimiter //
CREATE PROCEDURE showChildList (IN rootId INT,IN)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(sno int primary key auto_increment,
id int,
depth int
);
DELETE FROM tmpLst;
CALL createChildLst(rootId,0);
select tmpLst.*,treeNodes.*
from tmpLst,treeNodes
where tmpLst.id = treeNodes.id
order by tmpLst.sno;
END;
//
CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
//
delimiter ;
call showChildList(1);
call showChildList(3);
call showChildList(5);
如果多个会话同时调用showChildList 的话,一个临时数据库会不会不安全,我想过一个rootid建一个临时数据库也不现实,请问有没有更好的办法解决? 谢谢。
CREATE FUNCTION `getChildLst` (rootId INT) //注意红色的单引号
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM sinykk_url_sort where FIND_IN_SET(parent_id ,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
这种办法是否好一些? 不过执行不通过:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '注意红色的
单引号
RETURNS varchar(1000)
BEGIN
DECLARE sT' at line 1
CREATE FUNCTION `getChildLst` (rootId INT) //注意红色的单引号
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM sinykk_url_sort where FIND_IN_SET(parent_id ,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
这种办法是否好一些? 不过执行不通过:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '注意红色的
单引号
RETURNS varchar(1000)
BEGIN
DECLARE sT' at line 1
知道原因了,把//以及后面的文字去掉就可以了。这种办法应该可以用在并发把。不过貌似RETURNS varchar(1000)有大小限制http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx,有没有其他更好的办法? discuz评论是怎么样实现的?