Mysql交换两列的值

个人公众号，欢迎关注，不定期抽奖送书

创建一个测试的表

create table test_swap(x char(10), y char(10));

插入几条数据

insert into test_swap values('x1', 'y1'), ('x2', 'y2'), ('x3', null), (null, 'y4');

看一下现在表的样子

select * from test_swap;

输出

+------+------+
| x    | y    |
+------+------+
| x1   | y1   |
| x2   | y2   |
| x3   | NULL |
| NULL | y4   |
+------+------+
4 rows in set (0.00 sec)

执行交换语句

update test_swap set x=(@t:=x), x=y, y=@t;

再看一下交换后表的样子

select * from test_swap;

输出

+------+------+
| x    | y    |
+------+------+
| y1   | x1   |
| y2   | x2   |
| NULL | x3   |
| y4   | NULL |
+------+------+
4 rows in set (0.00 sec)

交换成功

参考

• http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql