首頁 > 軟體

Mysql儲存過程如何實現歷史資料遷移

2023-01-20 14:02:23

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

總的來說掌握基本語法,或者看一眼別人的格式,就可以模仿出來了。

以上為個人經驗,希望能給大家一個參考,也希望大家多多支援it145.com。


IT145.com E-mail:sddin#qq.com