mysql存儲過程（批量生成論壇中發帖、回帖、主題等數據）

USE 資料庫名稱1;
DROP PROCEDURE IF EXISTS 資料庫名稱1.存儲過程名稱;
delimiter $$
CREATE PROCEDURE 資料庫名稱1.存儲過程名稱(in v_count bigint,in v_count2 bigint)
BEGIN
set @vCount1=v_count;
set @vCount2=v_count2;

set @i=1;
set @vintervala=10000;
set @vintervalb=10000;
while(@i<=@vCount1)
do
#隨機獲取一個版塊ID
select fid into @vfid from tps_forum_forum where fup<>0 and `type`='forum' and `status`=1 order by rand() limit 1;

#獲取pid
select ifnull(max(pid)+1,1) into @vpid from tps_forum_post;

#獲取tid
select ifnull(max(tid)+1,1) into @vtid from tps_forum_post;

#獲取發帖的storeid username
select m.uid,m.username into @vuida,@vusernamea from tps_common_member m where m.uid>4 and m.`status`=0 order by rand() limit 1,1;

set @vintervala=@vintervala+@i;
set @vintervalb=@vintervalb+@i*100;

set @vtitlea=concat('發帖標題',floor(1+RAND()*10000000));
set @vpostdate=unix_timestamp(DATE_FORMAT(LOCALTIME()+@vintervala,'%Y-%m-%d %H:%i:%S'));

set @vip='211.161.61.12';
set @vmessagea=concat('發帖內容',floor(1+RAND()*10000000),'\r\n');
set @vporta=floor(10000+RAND()*55535);

#set @vportb=floor(10000+RAND()*55535);
set @vmessageb=concat('回覆的第',@vtid,'個帖子的內容');

#3新增發送帖子的信息
INSERT INTO `tps_forum_post` (`pid`, `fid`, `tid`, `first`, `author`, `authorid`, `subject`, `dateline`, `message`, `useip`, `port`,
`invisible`, `anonymous`, `usesig`, `htmlon`, `bbcodeoff`, `smileyoff`, `parseurloff`, `attachment`, `rate`, `ratetimes`, `status`, `tags`, `comment`, `replycredit`, `position`)
VALUES (@vpid, @vfid, @vtid, 1, @vusernamea, @vuida, @vtitlea,@vpostdate,@vmessagea, @vip, @vporta, 0, 0, 1, 0, -1, -1, 0, 0, 0, 0, 0, '', 0, 0, 1);

#最新主題信息表
INSERT INTO `tps_forum_newthread` (`tid`, `fid`, `dateline`) VALUES (@vtid,@vfid, @vpostdate);

#分表協調信息表
insert into tps_forum_post_tableid values();

#假設1個帖子有50000個回覆
set @j=1;
while(@j<=@vCount2)
do
#獲取回帖的storeid username
select m.uid,m.username into @vuidb,@vusernameb from tps_common_member m where m.uid>4 and m.`status`=0 order by rand() limit 1000,1;

#獲取pid
select ifnull(max(pid)+1,1) into @vpid from tps_forum_post;

set @vreplaydate=unix_timestamp(DATE_FORMAT(LOCALTIME()+@vintervalb,'%Y-%m-%d %H:%i:%S'));

set @vposition=@j+1;

#2分表協調表
insert into tps_forum_post_tableid values();

#3新增回覆帖子的信息
INSERT INTO `tps_forum_post` (`pid`, `fid`, `tid`, `first`, `author`, `authorid`, `subject`, `dateline`, `message`, `useip`, `port`,
`invisible`, `anonymous`, `usesig`, `htmlon`, `bbcodeoff`, `smileyoff`, `parseurloff`, `attachment`, `rate`, `ratetimes`, `status`, `tags`, `comment`, `replycredit`, `position`)
VALUES (@vpid, @vfid, @vtid, 0, @vusernameb, @vuidb, '', @vreplaydate, @vmessageb, @vip, @vporta, 0, 0, 1, 0, -1, -1, 0, 0, 0, 0, 0, '0', 0, 0, @vposition);

#主題參與者記錄表，第1個回覆的帖子信息
INSERT INTO `tps_forum_threadpartake` (`tid`, `uid`, `dateline`)
select tid,authorid,dateline
from tps_forum_post where fid=@vfid and tid=@vtid and first=0 and position=2;


#主題參與者記錄表，第@vCount2+1個回覆的帖子信息
INSERT INTO `tps_forum_threadpartake` (`tid`, `uid`, `dateline`)
select tid,authorid,dateline
from tps_forum_post where fid=@vfid and tid=@vtid and first=0 and position=@vCount2+1;

COMMIT;

set @j=@j+1;
end while;

#獲取position
select max(position) into @vposition from tps_forum_post where first=0 and tid=@vtid;
select count(0) into @vreplay from tps_forum_post where first=0 and tid=@vtid;
select max(dateline) into @vreplaydate from tps_forum_post where first=0 and tid=@vtid;

#1論壇主題信息表
INSERT INTO `tps_forum_thread` (`tid`, `fid`, `posttableid`, `typeid`, `sortid`, `readperm`, `price`, `author`, `authorid`, `subject`, `dateline`, `lastpost`,
`lastposter`, `views`, `replies`, `displayorder`, `highlight`, `digest`, `rate`, `special`, `attachment`, `moderated`, `closed`, `stickreply`, `recommends`,
`recommend_add`, `recommend_sub`, `heats`, `status`, `isgroup`, `favtimes`, `sharetimes`, `stamp`, `icon`, `pushedaid`, `cover`, `replycredit`, `relatebytag`,
`maxposition`, `bgcolor`, `comments`, `hidden`)
VALUES (@vtid,@vfid, 0, 0, 0, 0, 0, @vusernamea, @vuida, @vtitlea, @vpostdate, @vreplaydate,@vusernamea, floor(1000+rand()*10000), @vreplay, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, floor(0+rand()*2), 32, 0, 0, 0, -1, -1, 0, 0, 0, '0', @vposition, '', 0, 0);

#獲取版塊內主題數量
select count(0) into @vthreadCount from tps_forum_thread where fid=@vfid;

#獲取版塊內發帖子數量
select count(0) into @vpostCount from tps_forum_post where first=1 and fid=@vfid;

#獲取今日發帖數量
select count(0) into @vtodayposts from tps_forum_post where first=1 and fid=@vfid
and dateline>=unix_timestamp(date_format(localtime(),'%Y-%m-%d')) and
dateline<=unix_timestamp(DATE_ADD(date_format(localtime(),'%Y-%m-%d'),INTERVAL 24*60*60-1 SECOND));

#獲取發帖綜合信息
select fp.pid,fp.message,fp.dateline,fp.author into @vpid,@vmessage,@vdateline,@vauthor
from tps_forum_post fp where first=1 and fid=@vfid order by dateline desc limit 1;

#更新版塊表中 主題 帖子 今日發帖數據 及發帖綜合信息
update tps_forum_forum ff set ff.threads=@vthreadCount,ff.posts=@vpostCount,ff.todayposts=@vtodayposts,
ff.lastpost=concat(@vpid,@vmessage,@vdateline,@vauthor) where ff.fid=@vfid;

COMMIT;

set @i=@i+1;

END WHILE;
end $$
delimiter ;

call 資料庫名稱1.存儲過程名稱(3,10);