如何在不违反唯一约束的情况下在MySQL中交换两行的值? [英] How to swap values of two rows in MySQL without violating unique constraint?

问题描述

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

I have a "tasks" table with a priority column, which has a unique constraint.

我试图交换两行的优先级值，但是我一直违反约束.我在类似情况下的某个地方看到了该语句，但不是在MySQL中.

I'm trying to swap the priority value of two rows, but I keep violating the constraint. I saw this statement somewhere in a similar situation, but it wasn't with 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中完成此操作而不使用伪造的值和多个查询?

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

这是表格结构:

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

推荐答案

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

否.(我认为没有).

问题是MySQL如何处理更新. MySQL(与正确实现UPDATE的其他DBMS不同)以更新的方式处理更新.它会在每次更新单行后强制检查UNIQUE(和其他)约束，而不是(应该这样做)在整个UPDATE语句完成之后执行检查.因此，(大多数)其他DBMS都没有这个问题.

The problem is how MySQL processes updates. MySQL (in difference with other DBMS that implement UPDATE properly), processes updates in a broken manner. It enforces checking of UNIQUE (and other) constraints after every single row update and not - as it should be doing - after the whole UPDATE statement completes. That's why you don't have this issue with (most) other DBMS.

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

For some updates (like increasing all or some ids, id=id+1), this can be solved by using - another non-standard feature - an ORDER BY in the update.

要交换两行中的值，该技巧无济于事.您必须使用NULL或伪造的值(该值不存在，但您的列中允许使用)和2或3条语句.

For swapping the values from two rows, that trick can't help. You'll have to use NULL or a bogus value (that doesn't exist but is allowed in your column) and 2 or 3 statements.

您也可以暂时删除唯一约束，但是我认为这不是一个好主意.

You could also temporarily remove the unique constraint but I don't think that's a good idea really.

因此，如果唯一列是有符号整数并且没有负值，则可以在事务中使用2条语句:

So, if the unique column is a signed integer and there are no negative values, you can use 2 statements wrapped up in a transaction:

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 ;

这篇关于如何在不违反唯一约束的情况下在MySQL中交换两行的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！