Mysql如何互换表中两列数据

数据表

CREATE TABLE `user`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL COMMENT '名字',
  `age` int(3) NULL DEFAULT 0 COMMENT '年龄',
  `height` int(3) NULL DEFAULT 0 COMMENT '厘米',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

INSERT INTO `user`(`name`, `age`, `height`) VALUES 
('AA', 150, 12),
('BB', 152, 13),
('CC', 151, 12),
('DD', 142, 11),
('EE', 145, 12);

mysql> SELECT * FROM `user`;
+----+------+-----+--------+
| id | name | age | height |
+----+------+-----+--------+
|  1 | AA   | 150 |     12 |
|  2 | BB   | 152 |     13 |
|  3 | CC   | 151 |     12 |
|  4 | DD   | 142 |     11 |
|  5 | EE   | 145 |     12 |
+----+------+-----+--------+
5 rows in set (0.07 sec)

由于粗心，管理员在录入是将年龄（age）与身高（height）颠倒了，如何快速的将这两列数据互换呢？

如果先全部查询，再foreach遍历更新，效率较低不太推荐

使用sql语句将age字段与height字段互换

新同学可能会使用以下sql语句将age与height互换

UPDATE `user` SET `age`=`height`,`height`=`age`;

这样更改将会使age与height的值都变成height的值

因为update的执行顺序是：

1）执行age=height，此时age的值会变为height的值

2）执行height=age，此时height的值又会变成age的值，其实height相当于没有更新。

执行结果如下：

mysql> SELECT * FROM `user`;
+----+------+-----+--------+
| id | name | age | height |
+----+------+-----+--------+
|  1 | AA   | 150 |     12 |
|  2 | BB   | 152 |     13 |
|  3 | CC   | 151 |     12 |
|  4 | DD   | 142 |     11 |
|  5 | EE   | 145 |     12 |
+----+------+-----+--------+
5 rows in set (0.07 sec)

mysql> UPDATE `user` SET `age`=`height`,`height`=`age`;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM `user`;
+----+------+-----+--------+
| id | name | age | height |
+----+------+-----+--------+
|  1 | AA   |  12 |     12 |
|  2 | BB   |  13 |     13 |
|  3 | CC   |  12 |     12 |
|  4 | DD   |  11 |     11 |
|  5 | EE   |  12 |     12 |
+----+------+-----+--------+
5 rows in set (0.07 sec)

可以看到，此时age虽然是修改正确了，但是height是不正确的。

正确互换的sql语句

UPDATE `user` as a, `user` as b SET a.`age`=b.`height`,a.`height`=b.`age` WHERE a.`id`=b.`id`;

执行结果如下：

mysql> SELECT * FROM `user`;
+----+------+-----+--------+
| id | name | age | height |
+----+------+-----+--------+
|  1 | AA   | 150 |     12 |
|  2 | BB   | 152 |     13 |
|  3 | CC   | 151 |     12 |
|  4 | DD   | 142 |     11 |
|  5 | EE   | 145 |     12 |
+----+------+-----+--------+
5 rows in set (0.07 sec)

mysql> UPDATE `user` as a, `user` as b SET a.`age`=b.`height`,a.`height`=b.`age` WHERE a.`id`=b.`id`;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM `user`;
+----+------+-----+--------+
| id | name | age | height |
+----+------+-----+--------+
|  1 | AA   |  12 |    150 |
|  2 | BB   |  13 |    152 |
|  3 | CC   |  12 |    151 |
|  4 | DD   |  11 |    142 |
|  5 | EE   |  12 |    145 |
+----+------+-----+--------+
5 rows in set (0.07 sec)