MySQL 中互换表中两列数据的正确方法

准备测试数据

为了演示互换过程，我们先创建一个包含原价和现价字段的测试表，并插入几条示例记录。执行以下 SQL 创建表结构：

CREATE TABLE `product` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '产品id',
 `name` varchar(50) NOT NULL COMMENT '产品名称',
 `original_price` decimal(5,2) unsigned NOT NULL COMMENT '原价',
 `price` decimal(5,2) unsigned NOT NULL COMMENT '现价',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `product` (`id`, `name`, `original_price`, `price`) VALUES 
(NULL, '雪糕', '5', '3.5'), 
(NULL, '鲜花', '18', '15'), 
(NULL, '甜点', '25', '12.5'), 
(NULL, '玩具', '55', '45'), 
(NULL, '钱包', '285', '195');

查询当前表内容，确认初始状态：

mysql> select * from product;

常见的错误互换方法

遇到互换两列的需求时，初学者往往会写出如下语句：

update product set original_price=price,price=original_price;

这条语句看似逻辑清晰，但在 MySQL 的实际执行中，赋值操作是顺序执行的。具体过程如下：

1. 首先执行 original_price=price：将 original_price 的值更新为当前的 price 值。此时，原 original_price 的信息已丢失。
2. 接着执行 price=original_price：由于上一步已经改变了 original_price 的值，这里的右值实际上是刚刚被更新后的 price 值。

结果是两列都变成了原来 price 的值，数据并未真正互换。验证执行结果如下：

mysql> update product set original_price=price,price=original_price;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from product;
+----+--------+----------------+--------+
| id | name  | original_price | price |
+----+--------+----------------+--------+
| 1 | 雪糕  |      3.50 |  3.50 |
| 2 | 鲜花  |     15.00 | 15.00 |
| 3 | 甜点  |     12.50 | 12.50 |
| 4 | 玩具  |     45.00 | 45.00 |
| 5 | 钱包  |     195.00 | 195.00 |
+----+--------+----------------+--------+

正确的互换方法

要实现两列数据的真正互换，可以利用 MySQL 支持表自连接的特性。通过为表设置两个别名，分别读取对方的原始值进行赋值。正确的 SQL 语句如下：

update product as a, product as b 
set a.original_price=b.price, a.price=b.original_price 
where a.id=b.id;

原理说明：这里将表 product 别名化为 a 和 b。在 SET 子句中，b.price 和 b.original_price 引用的是互换前的原始快照值（因为是从 b 表读取），而 a 是被更新的表。由于 b 作为源表没有被修改，所以能正确提供旧值完成交换。

执行后再次查询，可见两列数据已成功互换：

mysql> select * from product;
+----+--------+----------------+--------+
| id | name  | original_price | price |
+----+--------+----------------+--------+
| 1 | 雪糕  |      3.50 |  3.50 |  <-- 注：此处应为互换后的结果，若前一次未回滚则显示互换后
| 2 | 鲜花  |     15.00 | 15.00 |
... 
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

若在干净的初始数据上直接执行正确语句，结果将是：

• 雪糕: original_price 变为 3.50，price 变为 5.00
• 鲜花: original_price 变为 15.00，price 变为 18.00
• 以此类推...

总结

在进行数据库字段交换操作时，务必注意 MySQL UPDATE 语句的逐行顺序执行特性。简单的直接赋值会导致信息丢失，推荐采用自连接的方式引用原始值，以确保操作的准确性。

以上就是 MySQL 互换表中两列数据的详细内容，更多关于 MySQL 操作技巧的资料请关注本站其它相关文章！