mysql - 在主键冲突的 MySQL 中交换两个不同行上的两列

Question

我正在尝试交换两个不同行(角色)上的两列。我知道我可以通过设置用户变量来做到这一点，但如果可能的话，我试图在一个查询中完成这一切。

我的查询是:

UPDATE table AS t1, table AS t2 
SET t1.role = 'standby', t2.role = 'primary'
WHERE t1.sysid = t2.sysid AND t1.role = 'primary' AND t2.role = 'standby';

表结构为

sysid | role    | devid
1     | primary | dev1
1     | standby | dev2

主键是 sysid,role

问题似乎是事务时主键之间的冲突，错误:错误代码:1706。不允许主键/分区键更新，因为表都更新为“t1” '和't2'。

由于MySQL从左到右处理，我尝试先将临时值设置为t1以避免冲突，但仍然失败并出现相同的错误:

SET t1.role = 'tmp', t2.role = 'primary', t1.role = 'standby'

有没有办法用 MySQL 实现这一点？

Answer 1

您可以仅使用临时值来交换角色。选择一个尚不存在的值，以免导致 key 冲突。使用事务来保证数据完整性。

START TRANSACTION;
UPDATE table SET role = 'temprole' WHERE sysid = 1 and role = 'primary';
UPDATE table SET role = 'primary' WHERE sysid = 1 and role = 'standby';
UPDATE table SET role = 'standby' WHERE sysid = 1 and role = 'temprole';
COMMIT;