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中用于数据迁移存储过程分享的内容就先介绍到这里,更多相关文章的可以留意

代码注释

作者:喵哥笔记

IDC笔记

学的不仅是技术,更是梦想!