在 MySQL 中交换列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Swapping column values in MySQL
提问by Liedman
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.
我有一个带坐标的 MySQL 表，列名是 X 和 Y。现在我想交换这个表中的列值，这样 X 变成 Y，Y 变成 X。最明显的解决方案是重命名列，但我不想进行结构更改，因为我不一定有权限这样做。
Is this possible to do with UPDATEin some way? UPDATE table SET X=Y, Y=Xobviously won't do what I want.
这可能以某种方式与UPDATE 相关联吗？UPDATE table SET X=Y, Y=X显然不会做我想要的。
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.
编辑：请注意，我上面提到的权限限制有效地阻止了使用 ALTER TABLE 或其他更改表/数据库结构的命令。不幸的是，重命名列或添加新列不是选项。
回答by Artem Russakovskii
I just had to deal with the same and I'll summarize my findings.
我只需要处理相同的问题，我将总结我的发现。
The
UPDATE table SET X=Y, Y=Xapproach obviously doesn't work, as it'll just set both values to Y.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;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;
该
UPDATE table SET X=Y, Y=X方法显然不起作用，因为它只会将两个值都设置为 Y。这是一个使用临时变量的方法。感谢http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/评论中的 Antony对“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;
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);
回答by RolandoMySQLDBA
You could take the sum and subtract the opposing value using X and Y
您可以求和并使用 X 和 Y 减去相反的值
UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
Here is a sample test (and it works with negative numbers)
这是一个示例测试（它适用于负数）
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>
Here is the swap being performed
这是正在执行的交换
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>
Give it a Try !!!
试一试 ！！！
回答by Dipin
The following code works for all scenarios in my quick testing:
以下代码适用于我的快速测试中的所有场景：
UPDATE swap_test
SET x=(@temp:=x), x = y, y = @temp
回答by Greg Hewgill
UPDATE table SET X=Y, Y=Xwill do precisely what you want (edit: in PostgreSQL, not MySQL, see below). The values are taken from the old row and assigned to a new copy of the same row, then the old row is replaced. You do not have to resort to using a temporary table, a temporary column, or other swap tricks.
UPDATE table SET X=Y, Y=X会做你想要的（编辑：在 PostgreSQL 中，而不是 MySQL，见下文）。这些值取自旧行并分配给同一行的新副本，然后替换旧行。您不必求助于使用临时表、临时列或其他交换技巧。
@D4V360: I see. That is shocking and unexpected. I use PostgreSQL and my answer works correctly there (I tried it). See the PostgreSQL UPDATE docs(under Parameters, expression), where it mentions that expressions on the right hand side of SET clauses explicitly use the old values of columns. I see that the corresponding MySQL UPDATE docscontain the statement "Single-table UPDATE assignments are generally evaluated from left to right" which implies the behaviour you describe.
@D4V360：我明白了。这是令人震惊和意外的。我使用 PostgreSQL 并且我的答案在那里正常工作（我试过了）。请参阅PostgreSQL UPDATE 文档（在参数、表达式下），其中提到 SET 子句右侧的表达式明确使用列的旧值。我看到相应的MySQL UPDATE 文档包含语句“通常从左到右评估单表 UPDATE 分配”，这意味着您描述的行为。
Good to know.
很高兴知道。
回答by mercutio
Ok, so just for fun, you could do this! (assuming you're swapping string values)
好吧，只是为了好玩，你可以这样做！（假设您正在交换字符串值）
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)
A nice bit of fun abusing the left-to-right evaluation process in MySQL.
在 MySQL 中滥用从左到右的评估过程很有趣。
Alternatively, just use XOR if they're numbers. You mentioned coordinates, so do you have lovely integer values, or complex strings?
或者，如果它们是数字，只需使用 XOR。你提到了坐标，所以你有可爱的整数值或复杂的字符串吗？
Edit: The XOR stuff works like this by the way:
编辑：顺便说一下，异或的东西是这样工作的：
update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;
回答by workplaylifecycle
I believe have a intermediate exchange variable is the best practice in such way:
我相信有一个中间交换变量是这种方式的最佳实践：
update z set c1 = @c := c1, c1 = c2, c2 = @c
First, it works always; second, it works regardless of data type.
首先，它始终有效；其次，无论数据类型如何，它都可以工作。
Despite of Both
尽管两者
update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2
and
和
update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2
are working usually, only for number data type by the way, and it is your responsibility to prevent overflow, you can not use XOR between signed and unsigned, you also can not use sum for overflowing possibility.
正常工作，顺便说一下，仅适用于数字数据类型，防止溢出是您的责任，您不能在有符号和无符号之间使用 XOR，也不能使用 sum 来解决溢出的可能性。
And
和
update z set c1 = c2, c2 = @c where @c := c1
is not working if c1 is 0 or NULL or zero length string or just spaces.
如果 c1 是 0 或 NULL 或零长度字符串或只是空格，则不起作用。
We need change it to
我们需要把它改成
update z set c1 = c2, c2 = @c where if((@c := c1), true, true)
Here is the scripts:
这是脚本：
mysql> create table z (c1 int, c2 int)
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into z values(0, 1), (-1, 1), (pow(2, 31) - 1, pow(2, 31) - 2)
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.02 sec)
mysql> update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 2
mysql> update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 3
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.02 sec)
mysql> update z set c1 = c2, c2 = @c where @c := c1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 1 | 0 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)
mysql> update z set c1 = @c := c1, c1 = c2, c2 = @c;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)
mysql>update z set c1 = c2, c2 = @c where if((@c := c1), true, true);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 1 | 0 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)
回答by Unsliced
Two alternatives 1. Use a temporary table 2. Investigate the XOR algorithm
两种选择 1. 使用临时表 2. 研究XOR 算法
回答by fijter
ALTER TABLE table ADD COLUMN tmp;
UPDATE table SET tmp = X;
UPDATE table SET X = Y;
UPDATE table SET Y = tmp;
ALTER TABLE table DROP COLUMN tmp;
Edit: About Greg's comment: No, this doesn't work:
编辑：关于格雷格的评论：不，这不起作用：
mysql> select * from test; +------+------+ | x | y | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec)mysql> update test set x=y, y=x; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test; +------+------+ | x | y | +------+------+ | 2 | 2 | | 4 | 4 | +------+------+ 2 rows in set (0.00 sec)
回答by fijter
This surely works! I've just needed it to swap Euro and SKK price columns. :)
这肯定有效！我只是需要它来交换欧元和 SKK 价格列。:)
UPDATE tbl SET X=Y, Y=@temp where @temp:=X;
The above will not work (ERROR 1064 (42000): You have an error in your SQL syntax)
以上将不起作用（ERROR 1064 (42000): You have an error in your SQL syntax）
回答by SeanDowney
You couldchange column names, but this is more of a hack. But be cautious of any indexes that may be on these columns
您可以更改列名称，但这更像是一种技巧。但要小心可能在这些列上的任何索引
