MySQL中用于数据迁移存储过程分享
2022-11-12 09:21:40
内容摘要
这篇文章主要为大家详细介绍了MySQL中用于数据迁移存储过程分享,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
DELIMITER
文章正文
这篇文章主要为大家详细介绍了MySQL中用于数据迁移存储过程分享,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | <code> DELIMITER $$ USE `servant_591up`$$ DROP PROCEDURE IF EXISTS `sp_move_data`$$ CREATE PROCEDURE `sp_move_data`() BEGIN DECLARE v_exit INT DEFAULT 0; DECLARE v_spid BIGINT; DECLARE v_id BIGINT; DECLARE i INT DEFAULT 0; DECLARE c_table INT; DECLARE v_UniqueKey VARCHAR(57); DECLARE v_TagCatalogId INT; DECLARE v_RootCatalogId INT; DECLARE v_UserId BIGINT; DECLARE v_QuestionId CHAR(36); DECLARE v_CorrectCount INT; DECLARE v_ErrorCount INT; DECLARE v_LastIsCorrect INT; DECLARE v_LastAnswerXML TEXT CHARSET utf8; DECLARE v_TotalCostTime INT; DECLARE v_Reviews VARCHAR(200) CHARSET utf8; DECLARE v_AnswerResultCategory INT; DECLARE v_LastCostTime INT; DECLARE v_LastAnswerTime DATETIME; DECLARE v_IsPublic INT; DECLARE v_SUBJECT INT; DECLARE v_TotalCount INT; DECLARE v_AnswerMode SMALLINT(6); DECLARE v_ExerciseWeight FLOAT; DECLARE c_ids CURSOR FOR SELECT UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight FROM ol_answerresult_56; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit=1; OPEN c_ids; REPEAT FETCH c_ids INTO v_UniqueKey,v_TagCatalogId,v_RootCatalogId,v_UserId,v_QuestionId,v_CorrectCount,v_ErrorCount,v_LastIsCorrect,v_LastAnswerXML,v_TotalCostTime,v_Reviews,v_AnswerResultCategory,v_LastCostTime,v_LastAnswerTime,v_IsPublic,v_SUBJECT,v_TotalCount,v_AnswerMode,v_ExerciseWeight; IF v_exit = 0 THEN SET @vv_id = v_id; SELECT MOD(v_UserId,100) INTO c_table; SET @SQL_CONTEXT = CONCAT( 'INSERT INTO new_answerresult_' , c_table,' (UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight)values(', '' '' ,v_UniqueKey, '' '' , ',' , v_TagCatalogId, ',' , v_RootCatalogId, ',' , v_UserId, ',' , '' '' ,v_QuestionId, '' '' , ',' , v_CorrectCount, ',' , v_ErrorCount, ',' , v_LastIsCorrect, ',' , '' '' ,v_LastAnswerXML, '' '' , ',' , v_TotalCostTime, ',' , '' '' ,REPLACE(IFNULL(v_Reviews, '' ), '' '' , '' ), '' '' , ',' , v_AnswerResultCategory, ',' , v_LastCostTime, ',' , '' '' ,v_LastAnswerTime, '' '' , ',' , v_IsPublic, ',' , v_SUBJECT, ',' , v_TotalCount, ',' , v_AnswerMode, ',' , v_ExerciseWeight, ')' ); PREPARE STMT FROM @SQL_CONTEXT; EXECUTE STMT ; DEALLOCATE PREPARE STMT; END IF; SET i=i+1; #100 #IF MOD(i,100)=0 THEN COMMIT; # END IF; UNTIL v_exit=1 END REPEAT; CLOSE c_ids; #COMMIT; END $$ DELIMITER ; </code> |
注:关于MySQL中用于数据迁移存储过程分享的内容就先介绍到这里,更多相关文章的可以留意
代码注释