Mysql存儲(chǔ)過(guò)程如何實(shí)現(xiàn)歷史數(shù)據(jù)遷移
Mysql遷移歷史數(shù)據(jù)
記錄一下工作中由于業(yè)務(wù)需要以及系統(tǒng)的數(shù)據(jù)庫(kù)模型變更,導(dǎo)致需要做一下歷史數(shù)據(jù)遷移的解決辦法
需求陳述
- 一共涉及到三張表,分別稱為A、B、C
- 歷史數(shù)據(jù)在表A中。
- A表中存的數(shù)據(jù)有兩部分,通過(guò)一個(gè)busi_reg_province_code 字段來(lái)區(qū)分
- 一部分插入到B表,一部分插入到C表(就是用busi_reg_province_code來(lái)區(qū)分的兩部分)
- 存入B中的部分,對(duì)于存入C中的部分是一對(duì)多的關(guān)系。(相當(dāng)于B是做個(gè)匯總,C是詳細(xì)情況)
- 存入B的要計(jì)算存入C的某一字段值的總和
其實(shí)就是將一張表中的數(shù)據(jù),拆分分別存入B和C中。但是B和C是一對(duì)多的關(guān)系。
心路歷程
Step1
- 說(shuō)到數(shù)據(jù)遷移,第一想法就是通過(guò)
insert into select的語(yǔ)法形式來(lái)做數(shù)據(jù)遷移。 - 但是因?yàn)锽是C數(shù)據(jù)的匯總,所以不免需要使用一些聚合函數(shù)做計(jì)算,還要分組。
- 嗯~想想就頭大。
- 嘗試著寫了一下以后,最后還是放棄了。(突然覺(jué)得自己對(duì)SQL是一無(wú)所知,菜的摳jio)
Step2
- 放棄了寫SQL,怎么辦呢?需求還得做。
- 那作為一名JAVA開(kāi)發(fā),于是寫一個(gè)接口的想法誕生了。
- 整理一下思路,發(fā)現(xiàn)用JAVA寫,嗯~(會(huì)心一笑)還是很容易的。
- 畢竟java8的流式處理還是很方便的。但是就在這時(shí),心里突然又覺(jué)得 emm~ 我這樣逃避好像也不好啊。
- 沒(méi)有長(zhǎng)進(jìn)都,而且這個(gè)接口就調(diào)用一次,屬實(shí)是有點(diǎn)不合適。
- 所以覺(jué)得還是放棄JAVA方式。
Step3
- 既然還是用SQL語(yǔ)句來(lái)寫,但是什么
sum、count、group by、case when摻在一起又那么復(fù)雜又理不清,可咋辦呢? - 那只好 必應(yīng)一下。剛好查到了存儲(chǔ)結(jié)構(gòu)。
- 但是此時(shí)思想還是停留在
insert into select的階段,但是因?yàn)橹麈I并不是自增的,這個(gè)主鍵的問(wèn)題得解決。
整理一下問(wèn)題:
- 主鍵非自增,所以怎么賦值?
- 需要計(jì)算總值的列怎么計(jì)算?
- 怎么能寫一個(gè)SQL把兩個(gè)表都插入完成?
上面這幾個(gè)問(wèn)題一出現(xiàn),似乎已經(jīng)沒(méi)辦法再使用insert into select的形式了。
所以只能一個(gè)一個(gè)循環(huán)處理。那怎么循環(huán)呢?
這個(gè)時(shí)候就行到了游標(biāo)。可是這兩個(gè)東西,不管是觸發(fā)器,還是游標(biāo)這個(gè)技能都已經(jīng)封存已久,一點(diǎn)不記得了。所以重新學(xué)習(xí)一下
學(xué)習(xí)參考了一下這個(gè)文章。我覺(jué)得寫的還是蠻細(xì)致的
最終實(shí)現(xiàn)
下面是我最終寫完的存儲(chǔ)過(guò)程。用了游標(biāo)的嵌套
# --------------------------歷史數(shù)據(jù)遷移----------------------
# 刪除存儲(chǔ)過(guò)程
drop procedure if exists convertHistory;
# 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程
create procedure convertHistory()
begin
# 定義一個(gè)主鍵
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;
# 創(chuàng)建游標(biāo)
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';
# 指定游標(biāo)循環(huán)結(jié)束時(shí)的返回值
declare continue HANDLER FOR not found set done = true;
# 把初始值ID減一個(gè)數(shù)目
set outerId = outerId - 100;
# 先把之前遷移的刪掉
delete from mkt_resource_conf where REMARK = '歷史數(shù)據(jù)割接';
# 刪掉之前的
delete from mkt_resource_store_conf where REMARK = '歷史數(shù)據(jù)割接';
# 打開(kāi)游標(biāo)
open orignData;
fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
while (not done) do
# 具體的業(yè)務(wù)邏輯
# 查詢的都是配置項(xiàng),那么插入到配置表
# 配置項(xiàng)需要查詢一下該配置的總量
select sum(QUANTITY)
from prd_flow_info
where FLOW_GRADE = flowGrade
and BUSI_REG_PROVINCE_CODE = '99' into allocateNum;
# 1、2G 的流量直接做轉(zhuǎn)換,轉(zhuǎn)為MB
if flowUnit = 'G' then
set flowGrade = flowGrade * 1024;
end if;
insert into mkt_resource_conf
values (outerId, caseName, gradeId, flowGrade, allocateNum, allocateNum, 1, '沒(méi)什么說(shuō)明', 'system', 'system', sysdate(),
'system', 'system',
sysdate(), '1', '歷史數(shù)據(jù)割接');
# 查詢門店的配置,并且插入到門店的配置信息表
# 這里就要寫一個(gè)嵌套的游標(biāo)了
begin
# 定義一個(gè)配置表的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);
# 定義游標(biāo)
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;
# 開(kāi)始游標(biāo)了
open storeData;
fetch storeData into storeCode,alloNum,usedNum;
while (not storeDone) do
# 從表里查一下storeName,沒(méi)有就沒(méi)轍了
select STORE_NAME from mkt_resource_store_conf where STORE_CODE = storeCode limit 1 into storeName;
# 開(kāi)始保存到門店配置表
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, '歷史數(shù)據(jù)割接');
commit ;
# ID -1
set storeConfId = storeConfId - 1;
fetch storeData into storeCode,alloNum,usedNum;
end while;
# 重置變量
set storeDone = false;
# 關(guān)閉內(nèi)層游標(biāo)
close storeData;
end;
# 把初始值ID減一
set outerId = outerId - 1;
fetch orignData into caseName,flowGrade,gradeId,allocateNum,province,flowUnit;
end while;
# 關(guān)閉游標(biāo)
close orignData;
SELECT * FROM mkt_resource_conf where REMARK = '歷史數(shù)據(jù)割接';
SELECT * FROM mkt_resource_store_conf where REMARK = '歷史數(shù)據(jù)割接';
end;
call convertHistory();
總結(jié)
沒(méi)開(kāi)始的時(shí)候覺(jué)得會(huì)很難,但是真的邊學(xué)邊寫的時(shí)候,心情就會(huì)逐漸轉(zhuǎn)變。萬(wàn)事開(kāi)頭難說(shuō)的不錯(cuò),一旦開(kāi)始獲得正向反饋,問(wèn)題也就慢慢的迎刃而解了。
其實(shí)這個(gè)寫的并不復(fù)雜,只是代碼比較長(zhǎng)。
總結(jié)一下:
- 首先要克服自己的心里恐懼
- 定義存儲(chǔ)過(guò)程的語(yǔ)法
declare procedure - 了解游標(biāo)及存儲(chǔ)過(guò)程的使用場(chǎng)景
- 定義游標(biāo)的過(guò)程
declare 游標(biāo)名 cursor for (select 語(yǔ)句) - 打開(kāi)游標(biāo)
open 游標(biāo)名關(guān)閉游標(biāo)close 游標(biāo)名 - 將游標(biāo)中查詢的字段事先定義好,然后通過(guò)
fetch 游標(biāo)名 into 事先定義的變量來(lái)獲得每一條數(shù)據(jù)(有點(diǎn)像ES6的generator,走一步踢一腳) - 變量賦值
select xxx into 變量和set xxx = 變量值 - 其他的就是條件控制語(yǔ)句loop 、while、if、else等
總的來(lái)說(shuō)掌握基本語(yǔ)法,或者看一眼別人的格式,就可以模仿出來(lái)了。
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
Mysql中的日期時(shí)間函數(shù)小結(jié)
本文主要介紹了Mysql中的日期時(shí)間函數(shù)小結(jié),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
mysql安全啟動(dòng)腳本mysqld_safe詳細(xì)介紹
這篇文章主要介紹了mysql安全啟動(dòng)腳本mysqld_safe詳細(xì)介紹,mysqld_safe增加了一些安全特性,需要的朋友可以參考下2014-07-07
SQL實(shí)現(xiàn)Excel的10個(gè)常用功能的示例詳解
SQL,數(shù)據(jù)分析崗的必備技能,你可以不懂Python,R,不懂可視化,不懂機(jī)器學(xué)習(xí)。但SQL,你必須懂。本文為大家總結(jié)了SQL實(shí)現(xiàn)Excel的10個(gè)常用功能的示例代碼,感興趣的可以了解一下2022-07-07
數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)查詢優(yōu)化之子查詢優(yōu)化,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-01-01
mysql千萬(wàn)級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)
這篇文章主要介紹了mysql千萬(wàn)級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
Mysql(MyISAM)的讀寫互斥鎖問(wèn)題的解決方法
最近因?yàn)閿?shù)據(jù)庫(kù)讀的請(qǐng)求增加,出現(xiàn)了比較嚴(yán)重的讀寫鎖問(wèn)題,由于主從分離,主服務(wù)器很快的執(zhí)行完了寫入的操作,但從庫(kù)由于有大量的select的查詢,會(huì)被這些來(lái)自主輔同步的update,insert嚴(yán)重堵塞,最后造成所有的Mysql從庫(kù)負(fù)載迅速上升。2011-09-09

