Mysql：交换不同行的数据

Question

假设有一个名为fruits的表格，它长这样：

------------------------------------------
| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | orange     | orange    | 10       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | plum       | purple    | 25       |
------------------------------------------

我该如何交换行的值，同时保留id编号不变？

示例：

SWAP ROW WITH ID "5" WITH ROW WITH ID "2"

结果：

------------------------------------------
| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | plum       | purple    | 25       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | orange     | orange    | 10       |
------------------------------------------

请注意，除了id之外，所有值都保持不变。 我需要处理一个非常大的值列表，因此我需要一个一行代码或至多不需要创建临时表等操作的解决方案。

注意：id是唯一的。

谢谢

- Alain Jacomet Forte

---

像几乎任何其他编程语言中的任何其他交换操作一样。将记录＃1的数据复制到临时存储中，将记录＃2的数据复制到记录＃1中。然后将数据从临时存储复制到记录＃2中。在SQL中没有内置的“交换”操作可以在单个整洁查询中完成您想要的操作。 - Marc B

或者编写一个能够完成相同功能的脚本（参考@MarcB的评论）。 - Sai

临时存储可以是表中的新行吗？但我需要确保在复制操作完成后删除该行。 - Alain Jacomet Forte

你只需要一个地方来存储其中至少一条记录，以便在它们之间复制数据时使用。但我不会将记录放在同一张表中，因为突然出现了重复的（例如）苹果记录，会打乱任何计数/库存等内容……即使这是一个临时的记录。 - Marc B

如果这是表格中的新行，那么每次都需要找到唯一的ID，因此编写脚本会更简单，或者像@MarcB建议的那样，将其存储在另一个地方。 - Sai

Answer 1

你可以使用join不等式来对齐想要交换的行：

update fruit a
 inner join fruit b on a.id <> b.id
   set a.color = b.color,
       a.name = b.name,
       a.calories = b.calories
 where a.id in (2,5) and b.id in (2,5)

http://sqlfiddle.com/#!18/27318a/5

- cocogorilla

---

为了交换两个值，我尝试使用"a.color=b.color, b.color=a.color"，结果它出奇地奏效了。SQL不需要中间变量这一点是否“正常”，与普通编程语言相反，还是只是一个特例？ - xtian

@xtian 看看更新后的 fiddle。请注意，更新可以重写为 select 语句。运行该 select，您应该会看到一个“中间变量”，它以表格输出的形式呈现，其中包含原始值的列和交换值的列。这是“源”，当 update 语句写入目标表时将从中执行。因此，它与普通编程语言没有区别。您的中间变量在 update 表达式本身中被隐含。 - cocogorilla

@cocogorilla 谢谢。我很欣赏你的深入思考。 - Ishwor Khanal

我尝试了这个，但是得到了两行相同的结果。在fthiella的答案中，“in”的id是镜像的，但是在这里它们是相同的。 - Valter Ekholm

Answer 2

由于ID是唯一的，所以仅交换ID很困难，更容易交换列内容。像这样的查询可能是您需要的：

UPDATE
  yourtable t1 INNER JOIN yourtable t2
  ON (t1.id, t2.id) IN ((1,5),(5,1))
SET
  t1.color = t2.color,
  t1.name = t2.name,
  t1.calories = t2.calories

Please see fiddle here.

- fthiella

---

这是一个很好的做法 :) - Sai

@fthiella 美丽 :) - good_evening

Answer 3

这里有一种方法可以临时存储值，而不需要使用临时表或在您的水果表中使用虚拟行：

SELECT name, color, calories FROM fruit WHERE id = 2 INTO @name, @color, @calories;

UPDATE fruit AS f1, fruit AS f2
SET
 f1.name = f2.name, f2.name = @name,
 f1.color = f2.color, f2.color = @color,
 f1.calories = f2.calories, f2.calories = @calories
WHERE (f1.id, f2.id) = (2, 5);

这里有另一种解决方案，它使用了一个虚拟 id 值：

UPDATE fruit SET id = 0 WHERE id = 5;
UPDATE fruit SET id = 5 WHERE id = 2;
UPDATE fruit SET id = 2 WHERE id = 0;

- Bill Karwin

Answer 4

如果您的操作基于ID，并且想要交换整个行，一种花哨的交换唯一ID的方法是从1开始对它们进行编号，并使用0作为临时值。

另一种执行此操作的方法是使用无符号列，并使用指定的值（例如：-1）作为临时值。 我不会真正推荐后者，因为这种方法实际上是在“浪费空间”。有关更多详细信息，请参见http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html。

- John Weisz