mysql 数据传输函数_mysql数据传输/数据迁移的过程函数编写问题：报错-问答-阿里云开发者社区-阿里云...

最新推荐文章于 2023-01-14 13:45:00 发布

weixin_39855706

最新推荐文章于 2023-01-14 13:45:00 发布

阅读量95

点赞数

文章标签： mysql 数据传输函数

本文链接：https://blog.csdn.net/weixin_39855706/article/details/113272424

版权

本小白由于项目需求需要将服务器上的数据取下来融合到本地的数据库，写了如下代码

BEGIN

//声明变量用来存从服务器上取下来的值

declare done int default 0;

declare prd_postid int;

declare prd_topicid int;

declare prd_createrid int;

declare prd_createtime datetime;

declare prd_posterid varchar(20);

declare prd_status smallint(6);

declare prd_typeid int;

declare prd_anonymous tinyint(1);

declare prd_title varchar(100);

declare prd_postcontent longtext;

declare tempid varchar(100);

declare prdCursor CURSOR for

//pid tid等为服务器上对应表的所需字段

select t1.pid,t1.tid,t1.authorid,from_unixtime(t1.dateline),t1.useip ,t1.status,t1.fid,t1.anonymous,t1.subject,t1.message from pre_forum_post t1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open prdCursor;

cursor_loop:loop

FETCH prdCursor INTO prd_postid,prd_topicid,prd_createrid,prd_createtime,prd_posterid,prd_status,prd_typeid,prd_anonymous,prd_title,prd_postcontent;

select POST_ID from bbs_post where POST_ID = prd_postid into tempid;

if tempid = prd_postid then

//TOPIC_ID ,CREATER_ID 等为本数据库的对应字段

update bbs_post set TOPIC_ID = prd_topicid, CREATER_ID =prd_createrid,CREATE_TIME=prd_createtime,POSTER_IP=prd_posterid,STATUS=prd_status,TYPE_ID=prd_typeid,ANONYMOUS =prd_anonymous,SITE_ID=1,CONFIG_ID=1;

update bbs_post_text set POST_TITLE=prd_title, POST_CONTENT =prd_postcontent where POST_ID = prd_postid;

elseif tempid = null then

insert into bbs_post(POST_ID,TOPIC_ID ,CREATER_ID ,CREATE_TIME,POSTER_IP,STATUS,TYPE_ID,ANONYMOUS,SITE_ID,CONFIG_ID) values (prd_postid,prd_topicid,prd_createrid,prd_createtime,prd_posterid,prd_status,prd_typeid,prd_anonymous,1,1);

insert into bbs_post_text(POST_ID,POST_TITLE,POST_CONTENT) values(prd_postid,prd_title,prd_postcontent);

else

insert into bbs_post_text(POST_ID,POST_TITLE,POST_CONTENT) values(prd_postid,prd_title,prd_postcontent);

END IF;

end loop cursor_loop;

CLOSE prdCursor;

END

执行了一晚上读取了38w条记录，总共有50w条记录，现在想把剩下的12w条记录也读进来，于是稍微改进了代码，如下

CREATE DEFINER=`root`@`localhost` PROCEDURE `post2_procedure`()

BEGIN

declare done int default 0;

declare prd_postid int;

declare prd_topicid int;

declare prd_createrid int;

declare prd_createtime datetime;

declare prd_posterid varchar(20);

declare prd_status smallint(6);

declare prd_typeid int;

declare prd_anonymous tinyint(1);

declare prd_title varchar(100);

declare prd_postcontent longtext;

declare tempid varchar(100);

declare prdCursor CURSOR for

select t1.pid,t1.tid,t1.authorid,from_unixtime(t1.dateline),t1.useip ,t1.status,t1.fid,t1.anonymous,t1.subject,t1.message from pre_forum_post t1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open prdCursor;

cursor_loop:loop

FETCH prdCursor INTO prd_postid,prd_topicid,prd_createrid,prd_createtime,prd_posterid,prd_status,prd_typeid,prd_anonymous,prd_title,prd_postcontent;

//修改的地方

select POST_ID from bbs_post where POST_ID = prd_postid in (select prd_postid from pre_forum_post where pid not in(select POST_ID from wdtxcms.bbs_post))into tempid;

if tempid = prd_postid then

update bbs_post_text set POST_CONTENT = prd_postcontent;

elseif tempid =null then

insert into bbs_post_text(POST_ID,POST_TITLE,POST_CONTENT) values(prd_postid,prd_title,prd_postcontent);

else

insert into bbs_post_text(POST_ID,POST_TITLE,POST_CONTENT) values(prd_postid,prd_title,prd_postcontent);

END IF;

end loop cursor_loop;

CLOSE prdCursor;

END

修改之后执行了一下午，也没有数据读进来，麻烦大神们帮我看看是哪里出错了。