在MySQL中交换列值

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

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

---

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

1> Artem Russak..：

---

我只需处理相同的事情,我将总结我的发现.

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

这是一个使用临时变量的方法.感谢Antony在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);

---

如MySQL文档中所述,在单个语句中分配和读取变量是不安全的.操作顺序无法保证.所以唯一安全的方法是#4

---

@Jhawins那是因为beerpla.net是我的博客.

---

你知道,我从来没有想过这个愚蠢的面试问题有一个实际的用途,要求在不使用临时的情况下交换两个变量,但在这里它是,并且对于整数,这实际上会起作用:update swap_test set x = x + y, Y = XY,X = XY;

2> RolandoMySQL..：

---

您可以使用X和Y减去总和并减去相反的值

UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;

这是一个示例测试(它与负数一起使用)

mysql> use test
Database changed
mysql> drop table if exists swaptest;
Query OK, 0 rows affected (0.03 sec)

mysql> create table swaptest (X int,Y int);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO swaptest VALUES (1,2),(3,4),(-5,-8),(-13,27);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|   -5 |   -8 |
|  -13 |   27 |
+------+------+
4 rows in set (0.00 sec)

mysql>

这是正在执行的交换

mysql> UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    2 |    1 |
|    4 |    3 |
|   -8 |   -5 |
|   27 |  -13 |
+------+------+
4 rows in set (0.00 sec)

mysql>

试试看 !!!

---

对于数字来说,它确实是最好的.

3> Dipin..：

---

以下代码适用于我的快速测试中的所有方案:

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

4> Greg Hewgill..：

---

UPDATE表SET X = Y,Y = X将完全按照您的要求进行操作(编辑:在PostgreSQL中,而不是MySQL,见下文).这些值取自旧行并分配给同一行的新副本,然后替换旧行.您不必使用临时表,临时列或其他交换技巧.

@ D4V360:我明白了.这令人震惊和意外.我使用PostgreSQL,我的答案在那里正常工作(我试过).请参阅PostgreSQL UPDATE文档(在Parameters,expression下),其中提到SET子句右侧的表达式显式使用列的旧值.我看到相应的MySQL UPDATE文档包含语句"单表UPDATE赋值通常从左到右进行评估",这意味着您描述的行为.

很高兴知道.

---

恕我直言,这个答案是一个混乱 - 糟糕的建议与"哎呀从不介意"附加.其中一半应该是评论,剩下的唯一与问题相关的部分是MySQL文档的链接......

---

我用PostgreSQL和SET X = Y,Y = X救了我:)

5> mercutio..：

---

好的,所以只是为了好玩,你可以做到这一点!(假设您正在交换字符串值)

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 6    | 1    | 
| 5    | 2    | 
| 4    | 3    | 
+------+------+
3 rows in set (0.00 sec)

mysql> update swapper set 
    -> foo = concat(foo, "###", bar),
    -> bar = replace(foo, concat("###", bar), ""),
    -> foo = replace(foo, concat(bar, "###"), "");

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 1    | 6    | 
| 2    | 5    | 
| 3    | 4    | 
+------+------+
3 rows in set (0.00 sec)

在MySQL中滥用从左到右的评估过程很有趣.

或者,如果它们是数字,只需使用XOR.你提到了坐标,你有可爱的整数值,还是复杂的字符串？

编辑:顺便说一句,XOR的东西是这样的:

update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;