MySQL中用于数据迁移存储过程分享

这篇文章主要为大家详细介绍了MySQL中用于数据迁移存储过程分享，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
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 ; 

注：关于MySQL中用于数据迁移存储过程分享的内容就先介绍到这里，更多相关文章的可以留意