在MySQL中交换列值

一尘不染

在MySQL中交换列值

mysql

我有一个带有坐标的MySQL表，列名称为X和Y。现在我想交换此表中的列值，以使X成为Y，Y成为X。最明显的解决方案是重命名列，但是我不想进行结构更改，因为我不一定有这样做的权限。

这可能以某种方式与 UPDATE 有关吗？ UPDATE表SET X = Y，Y = X 显然不会满足我的要求。

编辑：请注意，我上面提到的权限限制有效地阻止了ALTER TABLE或其他更改表/数据库结构的命令的使用。不幸的是，重命名列或添加新列不是选项。

阅读 274

2020-05-17

共1个答案

一尘不染

我只需要处理相同的问题，然后我将总结我的发现。

该UPDATE table SET X=Y, Y=X方法显然行不通，因为它将两个值都设置为Y。
这是使用临时变量的方法。感谢 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/的注释中的“ IS NOT NULL”调整。没有它，查询将无法正常工作。请参阅文章末尾的表架构。如果其中一个为NULL，则此方法不交换值。使用没有此限制的方法＃3。

UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

Dipin在 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/的注释中再次提供了此方法。我认为这是最优雅，最干净的解决方案。它适用于NULL和非NULL值。

UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

我想出的另一种方法似乎可行：

UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

本质上，第一个表是要更新的表，第二个表是用于从中提取旧数据的表。
请注意，此方法要求显示主键。

这是我的测试架构：

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);

2020-05-17