一、问题
数据库表数据入库时,由于数据处理出错,导致表中数据刚好有两列的数据互相对调插入了。等到发现错误时,表中已经存在了相当一部分业务数据了,只能对表中的数据进行清洗处理。
二、解决方案
可以使用 SQL
的 update
语句进行处理。
三、解决过程记录
SQL 执行环境:
MySQL
版本5.7.30
1. 表数据初始化
已对实际业务数据进行了简化抽象处理
create table `t_info` (
`id` int not null auto_increment,
`creator` varchar(8) null,
`create_date` varchar(8) null,
primary key (`id`)
);
insert into `t_info`(`id`, `creator`, `create_date`) values (1, '20210515', '11004747');
insert into `t_info`(`id`, `creator`, `create_date`) values (2, '20210616', '12014836');
insert into `t_info`(`id`, `creator`, `create_date`) values (3, '20210617', '12011236');
如下图所示,表数据中 creator
列(创建者)和 create_date
列(创建日期)两列数据保存错误,刚好对调了
2. 误区陷阱
一开始以为直接在 update
语句里对调两列数据即可
-- 错误的 sql 语句
update
t_info t
set
t.create_date = t.creator,
t.creator = t.create_date
但是,如下图所示,这样做是错误的,并没有实现表中两列数据的互换。因为 creator
数据先覆盖了 create_date
(此时原先 create_date
数据已丢失),然后再使用覆盖后的 create_date
数据(实质是 creator
的数据)给 creator
设置,其结果就是两列的数据都变为原先 creator
的数据。
3. 正确的处理
update
t_info as a,
t_info as b
set
a.create_date = b.creator,
a.creator = b.create_date
where a.id = b.id;
数据处理后,查询结果如下图所示,表中两列数据互换成功。
四、拓展
如果表中的两列数据,一部分记录需要互换,另一部分记录不需要互换,这怎么办呢?上面提到的解决方案是全表数据都会影响到的。
实际开发中,对于业务数据,可能会存在存量数据有问题,需要清洗整理,而增量数据没有问题,不需要变动这种场景。这种场景下,我们需要先找出有问题的数据,然后专门针对这部分数据进行清洗整理。
-- 为了模拟对应的场景,先清掉表数据,然后再插入相应数据
delete from t_info;
-- 这3条数据是有误的(模拟有问题的存量数据)
insert into `t_info`(`id`, `creator`, `create_date`) values (1, '20210515', '11004747');
insert into `t_info`(`id`, `creator`, `create_date`) values (2, '20210616', '12014836');
insert into `t_info`(`id`, `creator`, `create_date`) values (3, '20210617', '12011236');
-- 这2条数据是正确的(模拟没问题的增量数据)
insert into `t_info`(`id`, `creator`, `create_date`) values (4, '13011234', '20220102');
insert into `t_info`(`id`, `creator`, `create_date`) values (5, '13011235', '20220203');
数据重新插入后,表数据查询结果如下图所示
附加查询条件,使得 update
语句只处理有问题的数据
update
t_info as a,
t_info as b
set
a.create_date = b.creator,
a.creator = b.create_date
where a.id = b.id
and a.id in (1, 2, 3); -- 附加查询条件,限定有问题的数据
或者通过关联有问题的数据进行更新
-- join 与 inner join 是相同的
update
t_info as a
join
(select * from t_info where id in (1, 2, 3)) as b
on a.id = b.id
set
a.create_date = b.creator,
a.creator = b.create_date
处理后的查询结果如下图所示,数据清洗整理处理成功。