如何在MySQL中交换特定的字段值？
让我们首先创建一个表-
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Number1 int, Number2 int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable(Number1,Number2) values(10,30); mysql> insert into DemoTable(Number1,Number2) values(60,50); mysql> insert into DemoTable(Number1,Number2) values(110,100);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+---------+---------+ | Id | Number1 | Number2 | +----+---------+---------+ | 1 | 10 | 30 | | 2 | 60 | 50 | | 3 | 110 | 100 | +----+---------+---------+ 3 rows in set (0.00 sec)
现在让我们在MySQL中交换特定的字段值。在这里，我们交换ID为2的字段“Number1”和“Number2”-
mysql> update DemoTable set Number1=(@temporaryVariable:=Number1), Number1=Number2, Number2=@temporaryVariable where Id=2; Rows matched: 1 Changed: 1 Warnings: 0
让我们再次检查表记录-
mysql> select *from DemoTable;
这将产生以下输出-
+----+---------+---------+ | Id | Number1 | Number2 | +----+---------+---------+ | 1 | 10 | 30 | | 2 | 50 | 60 | | 3 | 110 | 100 | +----+---------+---------+ 3 rows in set (0.00 sec)