在 MySQL 中交换列值

2021-11-20 00:00:00 database mysql

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

I have a MySQL table with coordinates, the column names are X and Y. Now I want to swap the column values in this table, so that X becomes Y and Y becomes X. The most apparent solution would be renaming the columns, but I don't want to make structure changes since I don't necessarily have permissions to do that.

这可能以某种方式与 UPDATE 相关吗?UPDATE table SET X=Y, Y=X 显然不会做我想要的.

Is this possible to do with UPDATE in some way? UPDATE table SET X=Y, Y=X obviously won't do what I want.

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

Please note that my restriction on permissions, mentioned above, effectively prevents the use of ALTER TABLE or other commands that change the table/database structure. Renaming columns or adding new ones are unfortunately not options.

推荐答案

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

I just had to deal with the same and I'll summarize my findings.

  1. UPDATE table SET X=Y, Y=X 方法显然不起作用，因为它只会将两个值都设置为 Y.

  1. The UPDATE table SET X=Y, Y=X approach obviously doesn't work, as it'll just set both values to Y.

这是一个使用临时变量的方法.感谢来自 http://beerpla.net 评论的安东尼/2009/02/17/swapping-column-values-in-mysql/ 用于IS NOT NULL"调整.没有它，查询将无法预测.请参阅文章末尾的表架构.如果其中之一为 NULL，则此方法不会交换值.使用没有此限制的方法 #3.

Here's a method that uses a temporary variable. Thanks to Antony from the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ for the "IS NOT NULL" tweak. Without it, the query works unpredictably. See the table schema at the end of the post. This method doesn't swap the values if one of them is NULL. Use method #3 that doesn't have this limitation.

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 值.

This method was offered by Dipin in, yet again, the comments of http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/. I think it’s the most elegant and clean solution. It works with both NULL and non-NULL values.

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

我想出的另一种似乎有效的方法:

Another approach I came up with that seems to work:

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

本质上，第一个表是更新的表，第二个表用于从中提取旧数据.
请注意，此方法需要存在主键.

Essentially, the 1st table is the one getting updated and the 2nd one is used to pull the old data from.
Note that this approach requires a primary key to be present.

这是我的测试架构:

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);

相关文章