mysql 数据迁移用的一个存储过程，需要的朋友可以收藏下。
- 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 ;
我的微信
扫一扫更精彩
大家的支持是我更新的动力！！！
评论