系统重构之后生产数据迁移解决方案--Mysql为例

已于 2022-04-11 17:31:42 修改
阅读量1.9k 收藏 1
点赞数
文章标签： mysql
于 2022-04-11 17:23:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zhangmaoyang/article/details/124101251
版权

通常情况下，我们的系统在重构之后，表名、字段名都会发生不同程度的变化，如果说重构之后数据库结构变更很小，那么恭喜你，你省去了很多麻烦。但是往往实际情况事与愿违，所以此处仅记录本人迁移生产数据的一些思路和步骤，以及会遇到的一些问题

方案一：Server Link（推荐）

Mysql提供一种引擎叫FEDERATED，这种引擎允许我们链接其他数据库，然后通过sql命令复制其他库表到任意数据库。简单来说就类似于Navicat复制一个数据库的表到另一个数据库中去，这样相当于两个库的表都在一个库里边，导入数据就变得简单多了

1、创建远程server

在新数据库中执行

CREATE SERVER crmlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'ygsw', PASSWORD 'hugo2021!',HOST '192.168.30.15', PORT 3306, DATABASE 'crm');

上面的命令意思是创建一个server链接，使用的是mysql数据库，并配置了数据库链接参数

这样的话我们就变相的连接到了生产数据库

2、复制原表

对需要迁移的表先复制到新库中，以mls_base_corp（部门表）为例

CREATE TABLE `link_mls_base_corp`  (
  `ORIGREC` decimal(22,0) DEFAULT NULL,
  `ORIGSTS` char(1) DEFAULT 'N',
  `CORPID` varchar(50) NOT NULL,
  `PARENTID` varchar(50) DEFAULT '0',
  `CORPCODE` varchar(30) DEFAULT NULL,
  `CORPDESC` varchar(120) DEFAULT NULL,
  `RECORDERCODE` varchar(30) DEFAULT NULL,
  `RECORDERDESC` varchar(60) DEFAULT NULL,
  `RECORDTIME` datetime DEFAULT CURRENT_TIMESTAMP,
  `FREEZEFLAG` varchar(3) DEFAULT '0',
  `FREEZERCODE` varchar(30) DEFAULT NULL,
  `FREEZERDESC` varchar(60) DEFAULT NULL,
  `FREEZETIME` datetime DEFAULT NULL,
  `ORDERCODE` varchar(100) DEFAULT NULL,
  `CODE` varchar(400) DEFAULT NULL,
  `CODEID` varchar(50) DEFAULT NULL,
  `ABBREVIATION` varchar(50) DEFAULT NULL,
  `ADDRESS` varchar(50) DEFAULT NULL,
  `PHONE` varchar(50) DEFAULT NULL,
  `FAX` varchar(50) DEFAULT NULL,
  `POSTCODE` varchar(50) DEFAULT NULL,
  `PARENTNAME` varchar(120) DEFAULT NULL,
  `UNITTYPE` varchar(100) DEFAULT NULL COMMENT '单位类型',
  `LIMSCODE` varchar(200) DEFAULT NULL,
  `WEBSERVICEURL` varchar(500) DEFAULT NULL,
  `PARENTDIV` varchar(50) DEFAULT NULL COMMENT '成检标记实验室所属事业部字段',
  `CORPLEVEL` varchar(10) DEFAULT NULL COMMENT '客户级别',
  `HIERARCHYCODE` varchar(400) DEFAULT NULL COMMENT '层级编码',
  `DEFAULTCURRENCY` varchar(20) DEFAULT NULL COMMENT '默认币种',
  `CREDITPOOLFROZENQUOTA` varchar(50) DEFAULT NULL COMMENT '信用池冻结金额',
  `CREDITPOOLQUOTA` varchar(50) DEFAULT NULL COMMENT '信用池额度',
  `USEMONY` varchar(40) DEFAULT NULL COMMENT '信用池已用额度',
  `RESIDUALAMOUNT` varchar(40) DEFAULT NULL COMMENT '信用池剩余额度',
  PRIMARY KEY (`CORPID`),
  KEY `CORPCODE_INDEX` (`CORPCODE`)
) ENGINE = FEDERATED CONNECTION='crmlink/mls_base_corp';

注意create table的表名不要和原表冲突，前面加上link_前缀方便识别

中间至最后一行之前可以直接复制原表的建表语句

最后一行的ENGINE必须是FEDERATED，CONNECTION值引号中 / 前边是你在第一个创建的server链接名，/ 之后是原表名

执行sql命令之后就可以看到多了一张链接表

注意：

    FEDERATED链接的表的DML不会对原表产生影响，但是TRUNCATE（截断）命令会对原表产生效果

3、新表的表结构

初始化新表

CREATE TABLE `sys_dept` (
  `dept_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `pid` bigint(20) DEFAULT NULL COMMENT '上级部门',
  `sub_count` int(5) DEFAULT '0' COMMENT '子部门数目',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `dept_sort` int(5) DEFAULT '999' COMMENT '排序',
  `enabled` bit(1) NOT NULL COMMENT '状态',
  `create_by` varchar(255) DEFAULT NULL COMMENT '创建者',
  `update_by` varchar(255) DEFAULT NULL COMMENT '更新者',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `dept_code` varchar(30) DEFAULT NULL COMMENT '部门编码',
  PRIMARY KEY (`dept_id`) USING BTREE,
  KEY `inx_pid` (`pid`) USING BTREE,
  KEY `inx_enabled` (`enabled`) USING BTREE,
  KEY `dept_code_index` (`dept_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8 COMMENT='部门';

4、导入原数据

对比原表和新表的表结构，我们需要的字段变得少了很多。分析对应字段

原表和新表对应关系
新表字段对应关系说明原表字段
dept_id主键自增，不需要导入
pid上级id，由于原表主键是uuid生成，所以此字段不能一步到位，我们只需要在新表中存入原表中的唯一字段，比如dept_code，部门编码（CORPCODE）在原表中是唯一字段，为了正确关联关系，我们在新表中也必须存上，然后在下一步中进行处理PARENTID
sub_count原表中无此字段，在下面步骤中处理
name菜单名CORPDESC
dept_sort排序，只不过原表此字段是varchar类型ORDERCODE
enabled启用标识，和原表恰好相反对应FREEZEFLAG
create_by创建者RECORDERCODE
update_by更新人，原表没存，用创建者的值就行
create_time创建时间RECORDTIME
update_time修改时间，原表没存，用创建时间的值就行
dept_code部门编码CORPCODE

梳理完对应关系之后，我们就可以通过sql语句导入数据了

/*导入数据到sys_dept*/
INSERT INTO `sys_dept`
(`pid`, `sub_count`, `name`, `dept_sort`, `enabled`, `create_by`, `update_by`, `create_time`, `update_time`, `dept_code`) 
select (case PARENTID when '01' then null else 0 end),0,CORPDESC,(ORDERCODE + 0),(case FREEZEFLAG when '0' then 1 when '1' then 0 end),RECORDERCODE,RECORDERCODE,RECORDTIME,RECORDTIME,CORPCODE from link_mls_base_corp

这里其实就是从我们创建的链接表link_mls_base_corp中查询数据，再经过部分处理之后插入到新表之中

5、补充处理

这一步不一定会有，在当前表中我们还需要更新pid（上级id）、sub_count（子部门数目），这些处理都需要用到存储过程+遍历进行处理

①：更新pid

-- ****************************更新pid --开始**************************
/*拿dept_code联查原corp信息,corp信息中的PARENTID并非数字,但对应sys_dept中的dept_code,所以查询dept_code对应的主键id就是需要pid*/

-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS sync_dept_pid; 
-- 创建存储过程
CREATE PROCEDURE sync_dept_pid()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE deptCode varchar(30);
	DECLARE parentId bigint(20);
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE dept CURSOR FOR select d.dept_code,p.dept_id from sys_dept d join link_mls_base_corp c on d.dept_code = c.CORPID join sys_dept p on c.PARENTID = p.dept_code where d.pid is not null;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	open dept;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch dept into deptCode,parentId;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
			-- 执行业务逻辑
			update sys_dept set pid = parentId where dept_code=deptCode;
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch dept into deptCode,parentId;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close dept;
END;
-- 执行存储过程
call sync_dept_pid();
-- 最后删除存储过程
DROP PROCEDURE IF EXISTS sync_dept_pid;
-- ****************************更新pid --结束**************************

大概逻辑是：

      查询新表的部门，得到部门编码dept_code，对应原表CORPCODE，从而得到原表上级PARENTID，根据PARENTID得到上级id对应的CORPCODE，再拿上级id的CORPCODE在新表中查询对应的主键id即是需要的pid，有点绕，参考定义游标出的sql

select d.dept_code,p.dept_id from sys_dept d join link_mls_base_corp c on d.dept_code = c.CORPID join sys_dept p on c.PARENTID = p.dept_code where d.pid is not null

对结果集遍历然后批量跟新即可修改pid的值了

②：更新sub_count

同样我们使用存储过程+遍历的方式批量修改

-- ****************************更新sub_count --开始**************************
-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS sync_dept_sub_count; 
-- 创建存储过程
CREATE PROCEDURE sync_dept_sub_count()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE deptId bigint(20);
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE deptIdList CURSOR FOR select dept_id from sys_dept;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	-- 打开游标
	open deptIdList;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch deptIdList into deptId;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>1 do
			-- 执行业务逻辑
			update sys_dept set sub_count = (select a.count from (select count(*) as count from sys_dept where pid = deptId) a) where dept_id=deptId;
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch deptIdList into deptId;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close deptIdList;
END;
-- 执行存储过程
call sync_dept_sub_count();
-- 最后删除存储过程
DROP PROCEDURE IF EXISTS sync_dept_sub_count;
-- ****************************更新sub_count --结束**************************

看下结果把：

原表数据

 新表数据

 6、其他注意事项

使用此种方式有一个好处就是，我们一张一张的表处理完之后，最后我们汇聚成一个sql文件，到时候就可以一步到位进行数据迁移，不需要写代码去迁移数据

另外实际上先迁移什么表，后迁移什么表，应该是有逻辑顺序的，这个根据自己的业务去判断，比如我的用户表中必须存dept_id（部门id），所以应先迁移部门表数据然后迁移用户数据

如果新库的菜单表和原先菜单数据不一致，也就是说重构过程中改变了菜单结构，那么实际上我们希望是以新的菜单为准，那么用新的菜单数据，角色-菜单的绑定就需要放在迁移完所有数据之后通过管理页面或者sql手动绑定了

方案二：使用ETL工具

现在市面上也有很多ETL工具，全程数据提取转换加载工具（Extract-Transform-Load），虽然多用在大数据处理中，但根据业务需要其实也是符合我们重构数据迁移的，比如Kettle工具

关于Kettle网上有一大堆的介绍和使用，这里就不作介绍了，只提供一种思路

像这种可视化工具的好处就是带界面，字段可以手动选择对应关系，比如下图

 对于像还要做进一步处理的（比如上面的更新pid和sub_count），kettle应该也是提供单独的执行sql的处理的

所以这也算是一种界面+脚本的方式吧

其他方案

现在的云服务也有提供数据传输的，比如阿里云的数据传输服务 DTS，这种适合服务器本身就是阿里托管的，并且数据量庞大的情况。当然这是收费的，很多云服务商都有提供此功能，建议根据需要选择。

写在最后：

    本篇文章只是提供一些系统重构之后数据迁移的一些思路，方案有很多，不见得我的方法对于你就是可行的，也有我可能不知道的更优的方案，这里仅作记录，分享给有需要的同学。

Zhangmaoyang
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值