论坛发帖 回帖的mysql,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='192.168.1.1';

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);

通过存储过程批量生成spool语句

过存储过程批量生成spool语句 CREATE OR REPLACE PROCEDURE pro_yx_full_txt IS export_handle UTL_FILE.file_type; v_ ...

MySql存储过程批量删除多个数据库中同名表中的指定字段

1. 创建存储过程batchDeleteField:删除所有名称为"MyDB_"开头的数据库中的指定字段 -- ---------------------------- -- Pr ...

MySql存储过程批量给多个数据库中的同名表添加字段

1 创建存储过程 batchAddField:给所有"MyDB_"开头的数据库添加新字段 -- ---------------------------- -- Procedure ...

mysql存储过程批量向表插入数据

业务需要,往某个表中批量插入数据,使用存储过程插入 首先,要建立一张mysql表,表明为phone_number, 三个字段,id 自增,number 就是要插入的表格,is_used 表示十分已经使 ...

原！mysql存储过程 批量导入数据

mysql需要导入某前缀例如12345为前缀的,后缀扩展2位 即00-99. 利用存储过程插入数据. DROP PROCEDURE IF EXISTS insert_popsms_code;DELIM ...

mysql 存储过程批量删除重复数据

表结构: LOAD DATA INFILE '/usr/local/phone_imsi_12' replace INTO TABLE tbl_imsi2number_new FIELDS TERMI ...

Mysql 存储过程批量建表

CREATE DEFINER=`root`@`%` PROCEDURE `createTables`() begin declare i int; declare suffix varchar(20) ...

mysql存储过程批量插入数据

DROP TABLE IF EXISTS TeachersInfo; CREATE TABLE TeachersInfo ( id INT NOT NULL AUTO_INCREMENT, teach ...

利用MySQL存储过程批量插入100W条测试数据

DROP PROCEDURE IF EXISTS insert_batch; CREATE PROCEDURE insert_batch() BEGIN ; loopname:LOOP '); ; T ...

随机推荐

使用canvas编写环形图&period;

原理使用canvas画图: 第一步:画一个大圆 第二部:画一个扇形 第三部:画一个小圆 相互叠加. 最终效果: 现在上代码: (function($){ $.fn.drawPic=function(o ...

Oracle参数篇

1. ARCHIVE_LAG_TARGET forces a log switch after the specified amount of time elapses.有效值为0(disabled) ...

动词 or 名词 ：这是一个问题 【转载】

前言:有网友让我用通俗的语言来讲一讲RESTful ,   我在这一块工程实践的不太多,有点为难了,  只能讲一讲我的理解, 欢迎大家批评指正.计算机行业最擅长造新词了,像什么AJAX,IoC, AO ...

js 判断某个对象是不是dom对象

导入NGUI插件

在Unity编辑器顶部菜单栏中的Assets菜单中选择Import Package,然后选择Custom Package(自定义资源包),弹出资源路径窗口,在其中找到NGUI资源包所在的位置,单击”打 ...

Android SlidingMenu开源库及其使用

极客学院教程: http://www.jikexueyuan.com/course/61_5.html?ss=1 1. SlidingMenu开源库的配置 2. SlidingMenu 的使用 --- ...

Linux企业级项目实践之网络爬虫(16)——使用base64传输二进制数据

用http传输二进制的数据时,需要将二进制做一下转化,例如传输的int类型,将int类型之间转为char以后,丢失掉了长度的信息,如数字123456,本来只有4个字节,但是转化成文本的"12 ...

【转】Grub Rescue修复方法

症状: 开机显示: GRUB loading error:unknow filesystem grub rescue> 原因:已经发现下面几种操作会导致这种问题:1,想删除debian,于是直接 ...

开源社群系统ThinkSNS&plus;PC端最新播报！

亲爱的粉丝,授权客户,企业创业者们,这一次,我们将为你们打造最好用的社交软件系统. 在这里你将看到TSer们本周研发.优化.设计的动态即时播报,可评论留言提出您的问题及建议与我们互动. 同时,研发已经 ...

etl数据同步工具 kettle

kellet使用 https://www.cnblogs.com/gala1021/p/7814712.html