Mysql Stored Procedure (batch generation of Forum posts, replies, topics, and other data), mysql Stored Procedure

Mysql Stored Procedure (batch generation of Forum posts, replies, topics, and other data), mysql Stored Procedure

USE Database Name 1;
Drop procedure if exists database name 1. Stored PROCEDURE name;
Delimiter $
Create procedure database name 1. Stored PROCEDURE name (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
# Randomly obtain a forum ID
Select fid into @ vfid from tps_forum_forum where fup <> 0 and 'type' = 'Forum 'and 'status' = 1 order by rand () limit 1;

# Obtain the pid
Select ifnull (max (pid) + 1,1) into @ vpid from tps_forum_post;

# Getting tid
Select ifnull (max (tid) + 1,1) into @ vtid from tps_forum_post;

# Obtain the storeid username of a post
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 ('Post title', floor (1 + RAND () * 10000000 ));
Set @ vpostdate = unix_timestamp (DATE_FORMAT (LOCALTIME () + @ vintervala, '% Y-% m-% d % H: % I: % s '));

Set @ vip = '192. 161.61.12 ';
Set @ vmessagea = concat ('Post content', floor (1 + RAND () * 10000000), '\ r \ n ');
Set @ vporta = floor (10000 + RAND () * 55535 );

# Set @ vportb = floor (10000 + RAND () * 55535 );
Set @ vmessageb = concat ('reply de', @ vtid, 'Post content ');

#3 add post sending information
Insert into 'tps _ forum_post '('pid', 'fid', 'tid', 'First ', 'author', 'authorid', 'subobject', 'dateline ', 'message', 'useip', 'Port ',
'Invisable', 'anonus us', 'usesig ', 'htmlon', 'bbcodeoff', 'smileyoff', 'parseurloff', 'attachment', 'rate', 'ratetimes ', 'status', 'tags', 'comment', 'replace credentials', '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 );

# Latest topic information table
Insert into 'tps _ forum_newthread '('tid', 'fid', 'dateline') VALUES (@ vtid, @ vfid, @ vpostdate );

# Table sharding coordination information table
Insert into tps_forum_post_tableid values ();

# Assume that one post has 50000 replies
Set @ j = 1;
While (@ j <= @ vCount2)
Do
# Obtain the storeid username of the reply
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;

# Obtain the 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. Table sharding
Insert into tps_forum_post_tableid values ();

#3 add a message to reply to the post
Insert into 'tps _ forum_post '('pid', 'fid', 'tid', 'First ', 'author', 'authorid', 'subobject', 'dateline ', 'message', 'useip', 'Port ',
'Invisable', 'anonus us', 'usesig ', 'htmlon', 'bbcodeoff', 'smileyoff', 'parseurloff', 'attachment', 'rate', 'ratetimes ', 'status', 'tags', 'comment', 'replace credentials', '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 );

# Topic participant record table, 1st reply posts
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;


# Topic participant record table, post information replied at @ 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;

# Get 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 forum topic information table
Insert into 'tps _ forum_thread '('tid', 'fid', 'posttableid', 'typeid', 'sortid', 'readperm', 'price', 'author ', 'authorid', 'subobject', 'dateline ', 'lastpost ',
'Lastposter', 'view', 'replies ', 'displayorder', 'highlight', 'digest', 'rate', 'Special ', 'attachment', 'moderated ', 'Closed ', 'stickreply', 'Recommends ',
'Recommend _ add', 'recommend _ sub', 'hats', 'status', 'isgroup', 'favtimes ', 'comment Times', 'stamp', 'icon ', 'pushidaid ', 'cover', 'replace credentials', 'relatebytag ',
'Maxposition', 'bgcolor', 'comments', 'hiddy ')
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 );

# Obtain the number of topics in a forum
Select count (0) into @ vthreadCount from tps_forum_thread where fid = @ vfid;

# Obtain the number of posts posted in the Forum
Select count (0) into @ vpostCount from tps_forum_post where first = 1 and fid = @ vfid;

# Obtain the number of posts posted today
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 ));

# Obtain the overall posting information
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 the topic post data and overall posting information in the forum table
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 database name 1. Stored Procedure name (3, 10 );