創(chuàng)建一個(gè)測(cè)試的表

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

插入幾條數(shù)據(jù)

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

看一下現(xiàn)在表的樣子

select * from test_swap;

輸出

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

執(zhí)行交換語(yǔ)句

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