服务器之家:专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - Mysql存储过程如何实现历史数据迁移

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

2023-02-28 15:06寻找桃子的果然 Mysql

这篇文章主要介绍了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的形式了。

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

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

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

最终实现

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
--------------------------历史数据迁移----------------------
# 删除存储过程
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

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

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

原文链接:https://blog.csdn.net/sinat_31420295/article/details/121291959

延伸 · 阅读

精彩推荐
  • MysqlCentos 7下使用RPM包安装MySQL 5.7.9教程

    Centos 7下使用RPM包安装MySQL 5.7.9教程

    这篇文章主要为大家详细介绍了Centos 7下使用RPM包安装MySQL 5.7.9的教程,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...

    520_13513312020-07-28
  • MysqlMySQL数据库的索引原理与慢SQL优化的5大原则

    MySQL数据库的索引原理与慢SQL优化的5大原则

    这篇文章主要介绍了MySQL数据库的索引原理与慢SQL优化的5大原则,包括:建立索引的原则,慢查询优化基本步骤,慢查询优化案例,explain使用,需要的朋友...

    wdc5152023-02-21
  • Mysql如何利用MySQL添加联合唯一索引

    如何利用MySQL添加联合唯一索引

    索引在sql调优部分占据着重要的位置,了解并深入索引对我们来说也是非常重要的,下面这篇文章主要给大家介绍了关于如何利用MySQL添加联合唯一索引的相关...

    直立行走的大瓶子8682022-10-24
  • MysqlMySQL游标详细介绍

    MySQL游标详细介绍

    大家好,本篇文章主要讲的是MySQL游标详细介绍,感兴趣的同学赶快来看一看吧,对你有帮助的话记得收藏一下,方便下次浏览...

    我是一棵卷心菜9232022-02-10
  • Mysql探究MySQL中varchar的定义长度

    探究MySQL中varchar的定义长度

    这篇文章主要介绍了探究MySQL中varchar的定义长度,文中主要对其长度究竟是字节还是字符做出了相关实验,需要的朋友可以参考下 ...

    MYSQL教程网3382020-05-10
  • MysqlMYSQL基础之连接MYSQL、修改密码、添加用户

    MYSQL基础之连接MYSQL、修改密码、添加用户

    在这篇文章中我们就从连接MYSQL、修改密码、增加用户等方面来学习一些MYSQL的常用命令。 ...

    mysql教程网4532019-10-24
  • Mysql64位CentOs7源码安装mysql-5.6.35过程分享

    64位CentOs7源码安装mysql-5.6.35过程分享

    本文给大家分享的是在64位CentOs7系统中使用源码安装mysql-5.6.35的详细过程,非常的细致,有需要的小伙伴可以参考下 ...

    刘振3602020-07-16
  • MysqlMySQL 8.0.13 下载安装教程图文详解

    MySQL 8.0.13 下载安装教程图文详解

    这篇文章主要介绍了MySQL 8.0.13 下载安装教程,本文图文并茂给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下...

    婷婷小美女3582020-09-09