mysql 数据迁移用的一个存储过程

  • A+
所属分类：数据库技术

mysql 数据迁移用的一个存储过程，需要的朋友可以收藏下。

  1. DELIMITER $$
  2. USE `servant_591up`$$
  3. DROP PROCEDURE IF EXISTS `sp_move_data`$$
  4. CREATE PROCEDURE `sp_move_data`()
  5. BEGIN
  6. DECLARE v_exit INT DEFAULT 0;
  7. DECLARE v_spid BIGINT;
  8. DECLARE v_id BIGINT;
  9. DECLARE i INT DEFAULT 0;
  10. DECLARE c_table INT;
  11. DECLARE v_UniqueKey VARCHAR(57);
  12. DECLARE v_TagCatalogId INT;
  13. DECLARE v_RootCatalogId INT;
  14. DECLARE v_UserId BIGINT;
  15. DECLARE v_QuestionId CHAR(36);
  16. DECLARE v_CorrectCount INT;
  17. DECLARE v_ErrorCount INT;
  18. DECLARE v_LastIsCorrect INT;
  19. DECLARE v_LastAnswerXML TEXT CHARSET utf8;
  20. DECLARE v_TotalCostTime INT;
  21. DECLARE v_Reviews VARCHAR(200) CHARSET utf8;
  22. DECLARE v_AnswerResultCategory INT;
  23. DECLARE v_LastCostTime INT;
  24. DECLARE v_LastAnswerTime DATETIME;
  25. DECLARE v_IsPublic INT;
  26. DECLARE v_SUBJECT INT;
  27. DECLARE v_TotalCount INT;
  28. DECLARE v_AnswerMode SMALLINT(6);
  29. DECLARE v_ExerciseWeight FLOAT;
  30. 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;
  31. DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit=1;
  32. OPEN c_ids;
  33. REPEAT
  34. 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;
  35. IF v_exit = 0 THEN
  36. SET @vv_id = v_id;
  37. SELECT MOD(v_UserId,100) INTO c_table;
  38. SET @SQL_CONTEXT =
  39. CONCAT('INSERT INTO new_answerresult_',
  40. c_table,'
  41. (UniqueKey,TagCatalogId,RootCatalogId,UserId,QuestionId,CorrectCount,ErrorCount,LastIsCorrect,LastAnswerXML,TotalCostTime,Reviews,AnswerResultCategory,LastCostTime,LastAnswerTime,IsPublic,SUBJECT,TotalCount,AnswerMode,ExerciseWeight)values(',
  42. '''',v_UniqueKey,'''',',',
  43. v_TagCatalogId,',',
  44. v_RootCatalogId,',',
  45. v_UserId,',',
  46. '''',v_QuestionId,'''',',',
  47. v_CorrectCount,',',
  48. v_ErrorCount,',',
  49. v_LastIsCorrect,',',
  50. '''',v_LastAnswerXML,'''',',',
  51. v_TotalCostTime,',',
  52. '''',REPLACE(IFNULL(v_Reviews,''),'''',''),'''',',',
  53. v_AnswerResultCategory,',',
  54. v_LastCostTime,',',
  55. '''',v_LastAnswerTime,'''',',',
  56. v_IsPublic,',',
  57. v_SUBJECT,',',
  58. v_TotalCount,',',
  59. v_AnswerMode,',',
  60. v_ExerciseWeight,')');
  61. PREPARE STMT FROM @SQL_CONTEXT;
  62. EXECUTE STMT ;
  63. DEALLOCATE PREPARE STMT;
  64. END IF;
  65. SET i=i+1;
  66. #100
  67. #IF MOD(i,100)=0 THEN COMMIT;
  68. #END IF;
  69. UNTIL v_exit=1
  70. END REPEAT;
  71. CLOSE c_ids;
  72. #COMMIT;
  73. END$$
  74. DELIMITER ;
1 2 3