MySQL:两行间的交换ID

Question

我们有现有的桌子：

| 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是唯一的主键。在编辑/更新记录时，我会注意更改第3行，id = 3到id = 5，第5行，id = 5到id = 3.类似于第3行和第5行之间的交换id。当然也有重复的地方，但是如何解决这种情况.

但是MySQL逻辑上抛出了错误。

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'

我知道这是个愚蠢的场景，但我们有一个客户想要这样做。他想编辑/更新每个现有的记录。

Answer 1

应该使用中间值将它们包装在事务中；

begin;
set @from = 3;
set @to = 5;
set @tmpid = (2000000 + @from % 147483647);
update col set id=@tmpid where id = @from;
update col set id=@from where id=@to;
update col set id=@to where id = @tmpid;
commit;

选择高端@tmpid命名空间以避免冲突非常重要，以免以后遇到麻烦。

Answer 2

列id是唯一的主键..。类似于第3行和第5行之间的交换id。

你不应该这么做。

在第一次创建记录时，任何记录的主键都应该被创建/设置/生成，并且它不应该在整个记录的整个生命周期中更改，直到记录最终被删除为止。

想象一下，如果银行重新给人们的银行账户重新编号的话，会有多么混乱！

另外，虽然这里似乎没有它们(还没有)，但拥有主键的主要好处之一是其他表可以有引用它的Foreign。如果开始更改主键值，则还必须更改所有相应的外键值(数据库将强制执行此操作)。

我很怀疑任何用户都会明白这一点。

..。顾客..。希望编辑/更新每个现有记录。

无论如何，允许他们编辑数据，而不是主键。

我将假设您正在为此提供某种类型的UI，并且它们不会直接针对数据库发出SQL。

如果他们试图使用它作为某种序列或“顺序”，那么使用一个单独的列是可行的。主键，甚至是自动增量键，都保证是唯一的；它们不能保证是连续的或连续的。

Answer 3

您可以将更新作为一条语句执行，但这仅在Server中有效，而不适用于MySQL

update col
set id = case when id = 3 then 5 else 3 end
where id in (3, 5);

Server db<>fiddle

MySQL db<>fiddle

在Server中，对主键(或任何唯一键)的update在语句级别进行计算。它在内部使用Split->Sort->Collapse计划，这意味着它实际上变成了一个删除、更新和插入，因此不存在违反约束的情况。

也请参阅这两篇文章保罗·怀特和法比亚诺·阿莫林

正确的约束实现将具有IMMEDIATE或DEFERRED选项。这两种方法都需要在语句级别，而不是在行级别进行约束检查。

MySQL不支持任何一个，因为它在逐行的基础上检查约束。PostgreSQL还检查行级别上的IMMEDIATE约束。

但是Server只检查语句级别。您可以在文档中看到这个。