在MySQL中交换列值 [英] Swapping column values in MySQL

问题描述

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

这可能与 UPDATE < b>在某种程度上？ UPDATE表SET X = Y，Y = X 显然不会做我想要的。

---

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

解决方案

我只是要处理相同的，

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




2. 这里是一个使用临时变量的方法。感谢来自 http://beerpla.net的评论中的Antony / 2009/02/17 / swapping-column-values-in-mysql / IS NOT NULL调整。没有它，查询工作不可预测。请参阅帖子末尾的表模式。如果其中一个值为NULL，则此方法不交换值。使用没有此限制的方法＃3。

   
   
   

   UPDATE swap_test SET x = y，y = @ temp WHERE（@temp：=这个方法是由Dipin提供的，同样是

http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/rel =noreferrer> http://beerpla.net/2009/02/17/swapping-column-values -in-mysql / 。我认为这是最优雅和干净的解决方案。它与NULL和非NULL值一起工作。




UPDATE swap_test SET x =（@ temp：= x），x = y，y = @temp;




3. 我想出的另一种方法似乎有效：

   
   
   

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

基本上，第一个表是更新的表，第二个表用于从旧表中提取。 br />
注意，这种方法需要一个主键。

这是我的测试模式：

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

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.

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

---

Edit: 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. The UPDATE table SET X=Y, Y=X approach obviously doesn't work, as it'll just set both values to Y.

2. 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;

3. 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;

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

This is my test schema:

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

这篇关于在MySQL中交换列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！