数据库表合并场景实践

在实际场景中，我们见的比较多的是表拆分，正好遇到一个需要表合并的需求，下面来分析分析

背景

目前是线上有若干张表：a1 a2、b1 b2、c1 c2...，目前需要将这些表进行合并[将b1 c1等表数据都合并到a1，将b2 c2等表合并到a2]，但是线上也会有业务在跑，不能停机更新还需要保证迁移的健壮性

因为c1 c2合并的本质与b1 b2一致，故下面以b1 b2合并为例来进行描述

表结构与关系

CREATE TABLE `a1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `info` varchar(255) COLLATE utf8mb4_croatian_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;

CREATE TABLE `a2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `a_id` int(11) DEFAULT NULL COMMENT 'a表id',
  `info` varchar(255) COLLATE utf8mb4_croatian_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;



CREATE TABLE `b1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `info` varchar(255) COLLATE utf8mb4_croatian_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;

CREATE TABLE `b2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `b_id` int(11) DEFAULT NULL COMMENT 'a表id',
  `info` varchar(255) COLLATE utf8mb4_croatian_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;

最初做法

性质冲冲完成了，SQL如下

insert into a1 (info) select info from b1;
insert into a2 (a_id,info) select b_id,info from b2;

那么来测试下，看结果是否准确，结果如下

select a1.id,a1.info a1info,a2.a_id,a2.info a2info from a1 left join a2 on a1.id = a2.a_id;

很明显这个数据错乱了，b2迁移过来的数据与a1原来的数据关联上了，造成了数据混乱

方案改造

分析先前做法，其实是b2在做迁移时，没有考虑到b2中数据要同样与迁移过去的b1数据新的主键关联上，那么如何做呢？接着往下看

a1表新增一个old_id字段，在b1迁移到a1表后，将b1原本的主键id保存在a1中
a2表新增一个is_new字段，用来标识哪些是新插入到a2的数据，防止后续修数时将a2中旧数据关联上新迁移到a1中的数据

ALTER TABLE `a1` ADD COLUMN `old_id` int NULL COMMENT '旧表主键id';
ALTER TABLE `a2` ADD COLUMN `is_new` tinyint NULL COMMENT '是否新数据标记 1：正在迁移的数据 0：已迁移完的数据 null：线上在跑的数据' ;


insert into a1 (info,old_id) select info,id from b1;
insert into a2 (a_id,info,is_new) select b_id,info,1 from b2;


update a2,b1,a1 set a2.a_id = a1.id where a1.old_id is not null and a1.old_id = b1.id and a2.is_new = 1 and a2.a_id = a1.old_id;


-- 回滚SQL
ALTER TABLE `a1` DROP COLUMN `old_id`;
ALTER TABLE `a2` DROP COLUMN `is_new`;

然后再用同样的SQL来测试

select a1.id,a1.info a1info,a2.a_id,a2.info a2info from a1 left join a2 on a1.id = a2.a_id;

到此为止，需求已经实现，但是还不完美，因为这段SQL仅仅只能跑一次，那么如果执行到一半因为各种原因失败了呢？如何将迁移的数据清理掉重新再次迁移呢？

这样看来我们上面的SQL健壮性还是不够

迁移SQL健壮性改造

-- 第一步：新增三个辅助迁移字段
ALTER TABLE `a1` ADD COLUMN `old_id` int NULL COMMENT '旧表主键id';
ALTER TABLE `a1` ADD COLUMN `is_new` int NULL COMMENT '是否新数据标记 1：是 0：否';
ALTER TABLE `a2` ADD COLUMN `is_new` tinyint NULL COMMENT '是否新数据标记 1：正在迁移的数据 0：已迁移完的数据 null：线上在跑的数据' ;


-- 第二步：开始数据迁移
insert into a1 (info,old_id,is_new) select info,id,1 from b1;
insert into a2 (a_id,info,is_new) select b_id,info,1 from b2;


-- 第三步：修正a2新迁移数据与原数据关联关系
update a2,b1,a1 set a2.a_id = a1.id where a1.old_id is not null and a1.old_id = b1.id and a2.is_new = 1 and a2.a_id = a1.old_id;


-- 第四步：打扫战场，方便后续C表等的迁移
UPDATE a2 SET is_new = 0 where is_new = 1;


-- C表重复第二步~第四步
-- ...
-- ...

-- 第五步迁移结束SQL
ALTER TABLE `a1` DROP COLUMN `old_id`;
ALTER TABLE `a1` DROP COLUMN `is_new`;
ALTER TABLE `a2` DROP COLUMN `is_new`;


-- 迁移失败回滚SQL
delete from a1 where is_new = 1;
delete from a2 where is_new is not null;