早睡早起,方能养生
Sleep early rise early, way to keep healthy

Mysql如何互换表中两列数据

super
2022-01-20 17:13
views 1928

数据表

 

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)


分享
0 条讨论
top