Mysql存储过程如何实现历史数据迁移

Mysql迁移历史数据

记录一下工作中由于业务需要以及系统的数据库模型变更，导致需要做一下历史数据迁移的解决办法

需求陈述

• 一共涉及到三张表，分别称为A、B、C

• 历史数据在表A中。

• A表中存的数据有两部分，通过一个busi_reg_province_code 字段来区分

• 一部分插入到B表，一部分插入到C表（就是用busi_reg_province_code来区分的两部分）

• 存入B中的部分，对于存入C中的部分是一对多的关系。（相当于B是做个汇总，C是详细情况）

• 存入B的要计算存入C的某一字段值的总和

其实就是将一张表中的数据，拆分分别存入B和C中。但是B和C是一对多的关系。

心路历程

Step1

• 说到数据迁移，第一想法就是通过insert into select 的语法形式来做数据迁移。

• 但是因为B是C数据的汇总，所以不免需要使用一些聚合函数做计算，还要分组。

• 嗯～想想就头大。

• 尝试着写了一下以后，最后还是放弃了。（突然觉得自己对SQL是一无所知，菜的抠jio）

Step2

• 放弃了写SQL，怎么办呢？需求还得做。

• 那作为一名JAVA开发，于是写一个接口的想法诞生了。

• 整理一下思路，发现用JAVA写，嗯～（会心一笑）还是很容易的。

• 毕竟java8的流式处理还是很方便的。但是就在这时，心里突然又觉得 emm～ 我这样逃避好像也不好啊。

• 没有长进都，而且这个接口就调用一次，属实是有点不合适。

• 所以觉得还是放弃JAVA方式。

Step3

• 既然还是用SQL语句来写，但是什么sum、count、group by、case when 掺在一起又那么复杂又理不清，可咋办呢？

• 那只好 必应一下。刚好查到了存储结构。

• 但是此时思想还是停留在insert into select 的阶段，但是因为主键并不是自增的，这个主键的问题得解决。

整理一下问题：

• 主键非自增，所以怎么赋值？

• 需要计算总值的列怎么计算？

• 怎么能写一个SQL把两个表都插入完成？

上面这几个问题一出现，似乎已经没办法再使用insert into select的形式了。

所以只能一个一个循环处理。那怎么循环呢？

这个时候就行到了游标。可是这两个东西，不管是触发器，还是游标这个技能都已经封存已久，一点不记得了。所以重新学习一下

学习参考了一下这个文章。我觉得写的还是蛮细致的

最终实现

下面是我最终写完的存储过程。用了游标的嵌套

#  --------------------------历史数据迁移----------------------
# 删除存储过程
drop procedure if exists convertHistory;
# 创建一个存储过程
create procedure convertHistory()
begin
 #   定义一个主键
 declare outerId bigint default (select min(RESOURCE_ID) from mkt_resource_conf);
 # 定义查询插入的列
 declare caseName varchar(32);
 declare gradeId varchar(32);
 declare flowGrade bigint(10);
 declare allocateNum bigint(10);
 declare province varchar(8);
 declare flowUnit varchar(4);
 #   是否完成
 declare done int default false;
 # 创建游标
 declare orignData cursor for select CASE_NAME,
                                     FLOW_GRADE,
                                     GRADE_ID,
                                     QUANTITY,
                                     BUSI_REG_PROVINCE_CODE,
                                     FLOW_UNIT
                              from prd_flow_info
                              where BUSI_REG_PROVINCE_CODE = '100';
 #   指定游标循环结束时的返回值
 declare continue HANDLER FOR not found set done = true;
 #   把初始值ID减一个数目
 set outerId = outerId - 100;
 #   先把之前迁移的删掉
 delete from mkt_resource_conf where REMARK = '历史数据割接';
 #     删掉之前的
 delete from mkt_resource_store_conf where REMARK = '历史数据割接';
 # 打开游标
 open orignData;
 fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
 while (not done) do
 #     具体的业务逻辑
 #     查询的都是配置项，那么插入到配置表
 #     配置项需要查询一下该配置的总量
 select sum(QUANTITY)
 from prd_flow_info
 where FLOW_GRADE = flowGrade
   and BUSI_REG_PROVINCE_CODE = '99' into allocateNum;
 #     1、2G 的流量直接做转换,转为MB
 if flowUnit = 'G' then
   set flowGrade = flowGrade * 1024;
 end if;

insert into mkt_resource_conf
 values (outerId, caseName, gradeId, flowGrade, allocateNum, allocateNum, 1, '没什么说明', 'system', 'system', sysdate(),
         'system', 'system',
         sysdate(), '1', '历史数据割接');
 #     查询门店的配置，并且插入到门店的配置信息表
 #     这里就要写一个嵌套的游标了
 begin
   #       定义一个配置表的ID
   declare storeConfId bigint default (select min(STORE_CONF_ID) from mkt_resource_store_conf);
   declare storeCode varchar(32);
   declare alloNum bigint(10);
   declare usedNum bigint(10);
   declare storeDone int default false;
   declare storeName varchar(128);
   #     定义游标
   declare storeData cursor for select store_code,QUANTITY,USE_NUM
                                from prd_flow_info
                                where GRADE_ID = gradeId
                                  and BUSI_REG_PROVINCE_CODE = '99';
   declare continue HANDLER FOR not found set storeDone = true;
   #     select gradeId;

set storeConfId = storeConfId - 100;

# 开始游标了
   open storeData;
   fetch storeData into storeCode,alloNum,usedNum;

while (not storeDone) do
   #       从表里查一下storeName，没有就没辙了
   select STORE_NAME from mkt_resource_store_conf where STORE_CODE = storeCode limit 1 into storeName;
   #       开始保存到门店配置表
   insert into `mkt_resource_store_conf` (`store_conf_id`, `resource_id`, `store_code`, `STORE_NAME`,
                                          `allocated_res_num`,
                                          `used_res_num`,
                                          `create_id`, `create_name`, `create_time`, `update_id`, `update_name`,
                                          `update_time`,
                                          `state`, `remark`)
   values (storeConfId, outerId, storeCode, storeName, alloNum, usedNum, 'system', 'system', sysdate(), 'system',
           'system',
           sysdate(),
           1, '历史数据割接');
   commit ;
   #       ID -1
   set storeConfId = storeConfId - 1;
   fetch storeData into storeCode,alloNum,usedNum;
   end while;
   #     重置变量
   set storeDone = false;
   #     关闭内层游标
   close storeData;
 end;
 #   把初始值ID减一
 set outerId = outerId - 1;
 fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
 end while;
 #   关闭游标
 close orignData;

SELECT * FROM mkt_resource_conf where REMARK = '历史数据割接';
 SELECT * FROM mkt_resource_store_conf where REMARK = '历史数据割接';
end;

call convertHistory();

来源：https://blog.csdn.net/sinat_31420295/article/details/121291959