创建一个测试的表
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
