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

发布于06月26日

我有一个带有优先级列的"任务"表，它有一个唯一的约束.

我试图交换两行的优先级值，但我一直违反约束.我在类似的情况下看到了这句话，但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中不使用假值和多个查询就可以实现这一点吗？

编辑:

以下是表格 struct :

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

推荐答案

Is it possible to accomplish this in MySQL without using bogus values and multiple queries?

No.(我想不出来).

问题是MySQL如何处理更新.MySQL(与其他正确实现UPDATE的DBMS不同)以不正确的方式处理更新.它在每一行更新之后强制执行UNIQUE(和其他)约束的判断，而不是在整个UPDATE语句完成之后执行判断.这就是为什么(大多数)其他DBMS没有这个问题.

对于一些更新(比如增加全部或部分ID，id=id+1)，可以通过在更新中使用ORDER BY(另一个非标准特性)来解决这个问题.

对于交换两行中的值，这个技巧没有帮助.您必须使用NULL或一个伪值(该值不存在，但在您的列中是允许的)和2或3条语句.

你也可以暂 timeshift 除这个独特的约束，但我认为这不是一个好主意.

因此，如果unique列是有符号整数，并且不存在负值，那么可以在一个事务中使用两条语句:

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 ;