如何在不违反完整性的情况下使用唯一约束交换 MySQL 中的行值？

在 MySQL 中交换行值，同时保留唯一约束

在 MySQL 中，在表中的两行之间交换优先级值的任务唯一约束可能会因违反约束而遇到错误。为了理解这个问题，让我们检查一个典型的 UPDATE 语句：

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 处理更新的方式与其他 DBMS 不同。它在每行更新后（而不是在整个语句完成后）检查约束违规。因此，交换值直接违反了唯一约束。

没有虚假值或多个查询的替代方案

不幸的是，如果不使用MySQL 中的中间值（伪造或空）或多个查询。唯一约束在每行更新后强制执行唯一性，使得直接交换不可能。

临时约束删除

一种选择是暂时删除唯一约束，执行交换操作，然后重新添加约束。但是，不建议使用这种方法，因为它会损害数据完整性。

虚假值和多个查询

建议的方法包括使用虚假值 (-3) 临时保存第一行的优先级值并将其与第二行的值交换。事务中需要两个查询：

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 中的行值？的详细内容。更多信息请关注PHP中文网其他相关文章！