您的位置:首页技术文章

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

【字号: 日期:2023-02-05 11:01:26浏览:2作者:猪猪
目录
  • 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();
    

    总结

    没开始的时候觉得会很难,但是真的边学边写的时候,心情就会逐渐转变。万事开头难说的不错,一旦开始获得正向反馈,问题也就慢慢的迎刃而解了。

    其实这个写的并不复杂,只是代码比较长。

    总结一下:

    • 首先要克服自己的心里恐惧
    • 定义存储过程的语法declare procedure
    • 了解游标及存储过程的使用场景
    • 定义游标的过程declare 游标名 cursor for (select 语句)
    • 打开游标open 游标名 关闭游标close 游标名
    • 将游标中查询的字段事先定义好,然后通过fetch 游标名 into 事先定义的变量 来获得每一条数据(有点像ES6的generator,走一步踢一脚)
    • 变量赋值select xxx into 变量set xxx = 变量值
    • 其他的就是条件控制语句loop 、while、if、else

    总的来说掌握基本语法,或者看一眼别人的格式,就可以模仿出来了。

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

    标签: MySQL
    相关文章: