如何在MySQL中交换两行的值而不违反唯一约束？

Question

如何在MySQL中交换两行的值而不违反唯一约束？

46

我有一个“任务”表，其中有一个优先级列，该列具有唯一约束。

我正在尝试交换两行的优先级值，但我一直违反约束。我在类似情况下的某个地方看到过这个语句，但它不是使用MySQL。

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

这将导致错误：

Error Code: 1062. Duplicate entry '3' for key 'priority_UNIQUE'

在MySQL中是否有可能不使用虚假值和多个查询来完成这个任务？

编辑：

以下是表结构：

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `priority` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `priority_UNIQUE` (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

- wannabeartist

你能显示表的结构吗？ - jcho360

添加了创建语句（从Workbench复制） - wannabeartist

3

好的，请开始一个事务，锁定表，删除约束以进行更改，再添加约束，提交事务，最后解锁表。 - xQbert

7个回答

2

我遇到了同样的问题。尝试了使用CASE WHEN和TRANSACTION进行每个可能的单语句查询 - 完全没有运气。我想出了三个替代方案。您需要决定哪个对您的情况更有意义。在我的情况下，我正在处理从前端返回的小对象重新组织的集合（数组），新顺序是不可预测的（这不是交换两个项目的交易），并且最重要的是，顺序的更改（通常在英文版中进行）必须传播到其他15种语言。

1.第一种方法：完全删除现有记录，并使用新数据重新填充整个集合。显然，这只能在您从前端接收到您需要恢复刚刚删除的内容时才能起作用。

2.第二种方法：此解决方案类似于使用虚假值。在我的情况下，我重新排序的集合还包括移动前的原始项目位置。此外，在UPDATE运行时，我必须以某种方式保留原始索引值。诀窍是操作索引列的第15位，该列在我的情况下为UNSIGNED SMALLINT。如果您具有（签名）INT / SMALLINT数据类型，则可以反转索引的值而不是进行位操作。

每次调用仅需运行第一个UPDATE。此查询会提高当前“index”字段（我有无符号smallint）的第15位。以前的14位仍反映原始索引值，这永远不会接近32K范围。

UPDATE *table* SET `index`=(`index` | 32768) WHERE *condition*;

然后迭代您的集合，提取原始和新索引值，并逐个更新每条记录。

foreach( ... ) {
    UPDATE *table* SET `index`=$newIndex WHERE *same_condition* AND `index`=($originalIndex | 32768);
}

这个最后的UPDATE语句必须每次调用只运行一次。该查询有效地清除了index字段的第15位，从而恢复了原始索引值，如果有的话。

UPDATE *table* SET `index`=(`index` & 32767) WHERE *same_condition* AND `index` > 32767;

第三种方法是将相关记录移动到一个没有主键的临时表中，更新所有索引，然后将所有记录移回第一张表。

- Alex D

采用了第一种方法。 - robsch

1

我遇到了类似的问题。

我想交换两个唯一的id，并且这些id是另一个表中的FK。

对我来说，最快的解决方案是：

在我的FK表中创建一个幽灵条目。
返回到我想要切换id的表格。
关闭FK检查 SET FOREIGN_KEY_CHECKS=0;
将我的第一个（A）id设置为ghost（X）fk（释放A）
将我的第二个（B）id设置为A（释放B）
将A设置为B（释放X）
删除幽灵记录并打开检查。 SET FOREIGN_KEY_CHECKS=1;

- Dwza

1

虚假值选项：

好的，我的查询与此类似，我已经找到了一种在“一个”查询中更新的方法。我的id列是PRIMARY，而position是UNIQUE组的一部分。这是我的原始查询，无法进行交换：

INSERT INTO `table` (`id`, `position`)
  VALUES (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

但是位置是一个无符号整数，它永远不会为0，所以我将查询更改为以下内容：

INSERT INTO `table` (`id`, `position`)
  VALUES (2, 0), (1, 2), (2, 1)
  ON DUPLICATE KEY UPDATE `position` = VALUES(`position`);

现在它可以工作了！显然，MYSQL按顺序处理值组。

也许这对你有用（未经测试，我对MYSQL几乎一无所知）：

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=3 THEN 0 
    WHEN priority=2 THEN 3 
    WHEN priority=0 THEN 2 
END 

WHERE priority IN (2,3,0);

祝你好运。

- LGT

0

如果您的目标列不是唯一约束条件，还有另一种相当简单的方法来交换表格行。

UPDATE `tasks` AS a
INNER JOIN `tasks` AS b ON a.id <> b.id
  SET a.priority = b.priority
WHERE a.id IN (2,3) AND b.id IN (2,3)

- hex494D49

1

这在MySQL中不起作用，它会抛出Duplicate entry '3' for key 'priority_UNIQUE'的错误。 - Honza

@Honza 我刚刚检查了解决方案，既然你是对的，我已经更改了描述。我没有删除它，因为它可能仍然对其他人有帮助。 - hex494D49

-2

不确定这是否违反了限制，但我一直在尝试做类似的事情，最终通过结合我找到的几个答案得出了这个查询：

UPDATE tasks as T1,tasks as T2 SET T1.priority=T2.priority,T2.priority=T1.priority WHERE (T1.task_id,T2.task_id)=($T1_id, $T2_id)

我正在交换的列没有使用唯一标识，所以我不确定这是否有帮助...

- David dB

-3

您可以通过在键索引中进行轻微更改，使用上述更新语句实现值的交换。

CREATE TABLE `tasks` (   `id` int(11) NOT NULL,   `name` varchar(200) DEFAULT NULL,   `priority` varchar(45) DEFAULT NULL,   PRIMARY KEY (`id`,`priority`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这将具有由id和priority组合而成的主键索引。然后您可以交换值。

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

我在这里看不到任何需要使用用户变量或临时变量的必要。希望这可以解决你的问题 :)

- satdev86

4

但这并没有强制执行与原始键相同的约束 - 你可能会直接删除 UNIQUE KEY 约束，而将 PRIMARY KEY 约束保留不变。 - Damien_The_Unbeliever

1

是的，这不是一个“轻微”的更改。它甚至允许在“id”列中存在重复值。 - ypercubeᵀᴹ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ypercubeᵀᴹ · Accepted Answer

在MySQL中是否可以不使用虚假值和多个查询来完成这个任务？

不行。（至少我想不出来其他方法）

问题在于MySQL处理更新的方式。与其他实现UPDATE命令的DBMS不同，MySQL以一种有缺陷的方式进行更新。它在每个单独的行更新后强制检查UNIQUE（和其他）约束条件，而不是在整个UPDATE语句完成后执行检查，这就是为什么你在（大多数）其他DBMS中没有这个问题的原因。

对于某些更新（比如增加所有或一些ID，id=id+1），这可以通过在更新中使用一个非标准功能——ORDER BY来解决。

对于交换两行中的值，这个技巧是无法帮助的。你将不得不使用NULL或虚假值（该值不存在但允许在列中使用）以及2或3条语句。

你也可以暂时移除唯一性约束，但我认为这不是一个好主意。

因此，如果唯一的列是有符号整数并且没有负值，你可以使用2条语句包含在一个事务中：

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;