记录一下工作中由于业务需要以及系统的数据库模型变更,导致需要做一下历史数据迁移的解决办法
其实就是将一张表中的数据,拆分分别存入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();
没开始的时候觉得会很难,但是真的边学边写的时候,心情就会逐渐转变。万事开头难说的不错,一旦开始获得正向反馈,问题也就慢慢的迎刃而解了。
其实这个写的并不复杂,只是代码比较长。
总结一下:
首先要克服自己的心里恐惧定义存储过程的语法declare procedure
了解游标及存储过程的使用场景定义游标的过程declare 游标名 cursor for (select 语句)
打开游标open 游标名
关闭游标close 游标名
将游标中查询的字段事先定义好,然后通过fetch 游标名 into 事先定义的变量
来获得每一条数据(有点像ES6的generator,走一步踢一脚)变量赋值select xxx into 变量
和 set xxx = 变量值
其他的就是条件控制语句loop 、while、if、else等
总的来说掌握基本语法,或者看一眼别人的格式,就可以模仿出来了。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持程序之家。