MySQL如何互换表中两列的数据

一、问题

数据库表数据入库时，由于数据处理出错，导致表中数据刚好有两列的数据互相对调插入了。等到发现错误时，表中已经存在了相当一部分业务数据了，只能对表中的数据进行清洗处理。

二、解决方案

可以使用 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');
1
2
3
4
5
6
7
8
9
10

如下图所示，表数据中 creator 列（创建者）和 create_date 列（创建日期）两列数据保存错误，刚好对调了

2. 误区陷阱

一开始以为直接在 update 语句里对调两列数据即可

-- 错误的 sql 语句
update
	t_info t
set
	t.create_date = t.creator,
	t.creator = t.create_date
1
2
3
4
5
6

但是，如下图所示，这样做是错误的，并没有实现表中两列数据的互换。因为 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;
1
2
3
4
5
6
7

数据处理后，查询结果如下图所示，表中两列数据互换成功。

四、拓展

如果表中的两列数据，一部分记录需要互换，另一部分记录不需要互换，这怎么办呢？上面提到的解决方案是全表数据都会影响到的。

实际开发中，对于业务数据，可能会存在存量数据有问题，需要清洗整理，而增量数据没有问题，不需要变动这种场景。这种场景下，我们需要先找出有问题的数据，然后专门针对这部分数据进行清洗整理。

-- 为了模拟对应的场景，先清掉表数据，然后再插入相应数据
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');
1
2
3
4
5
6
7
8
9
10
11

数据重新插入后，表数据查询结果如下图所示

附加查询条件，使得 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); -- 附加查询条件，限定有问题的数据
1
2
3
4
5
6
7
8

或者通过关联有问题的数据进行更新

-- 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
1
2
3
4
5
6
7
8
9

处理后的查询结果如下图所示，数据清洗整理处理成功。