两个Mysql唯一索引的交换: 避免重复索引 Duplicate entry '3' for key 'priority_UNIQUE'

需求

我做了一个排行榜,但是主键是pid,不是排名,排名作为唯一索引,两个人排名交换,只需要交换 排名唯一索引值即可.
但是直接单独更新 提示错误: Duplicate entry '3' for key 'priority_UNIQUE'

方法

本来希望可以在一条SQL语句中交换两个唯一索引值,但是发现这是不可能的,因为值得修改是一个一个 修改的,但凡修改为了 其他唯一索引值 就会报错.
所以只能先把两个记录的值 修改为 负数(保证仍然不会重复的值) , 然后再修改为需要交换的值,这样就可以达成需求,不过这个过程需要4条SQL语句.

后来发现 一条SQL语句修改两个值的, 那么就可以缩减为 2条SQL语句,为了避免错误导致只修改了一般,所以一般使用SQL事务.

SQL事务代码如下:

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 互换两行,如何在不违反唯一约束的情况下在MySQL中交换两行的值？

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

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);

This will lead to the error:

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

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

EDIT:

Here's the table structure:

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. (none that I can think of).

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.

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.

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.

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 ;