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

小编典典

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

mysql

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

我试图交换两行的优先级值，但我一直违反约束。我在类似情况下的某个地方看到了该语句，但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

阅读 730

2020-05-17

共1个答案

小编典典

是否可以在MySQL中完成此操作而不使用伪造的值和多个查询？

不（我想不到的）。

问题是MySQL如何处理更新。MySQL（与UPDATE正确实施的其他DBMS不同）以破碎的方式处理更新。它会UNIQUE在每行更新后强制检查（和其他）约束，而不是在整个UPDATE语句完成后（应该这样做）进行检查。因此，（大多数）其他DBMS都没有这个问题。

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

对于从两行交换值，此技巧无济于事。您必须使用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 ;

2020-05-17