Comment échanger les valeurs de ligne dans MySQL tout en respectant les contraintes uniques ?

Échanger les valeurs des lignes dans MySQL sans violer les contraintes uniques

Dans MySQL, il est souvent nécessaire d'échanger les valeurs de deux lignes dans une table tout en conservant une contrainte unique sur une colonne spécifique. Cependant, l'utilisation de l'instruction UPDATE typique avec une clause CASE peut entraîner une erreur d'entrée en double si la contrainte unique est violée.

Le problème

Le problème survient parce que MySQL traite les mises à jour ligne par ligne, en vérifiant les violations de contraintes uniques après chaque modification. Dans l'exemple fourni :

UPDATE tasks
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

MySQL met à jour la première ligne avec la priorité 2 en priorité 3. Cependant, lorsqu'il tente de mettre à jour la deuxième ligne avec la priorité 3 en priorité 2, il rencontre une violation car 3 déjà existe en tant que valeur unique.

La solution

Malheureusement, il n'est pas possible d'effectuer un échange de valeurs de ligne dans MySQL sans utiliser de fausses valeurs ou plusieurs requêtes. Cela est dû au comportement unique de traitement des contraintes de MySQL.

Pour contourner cette limitation, on peut utiliser l'approche à deux instructions suivante enveloppée dans une 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 ;

Cette approche échange efficacement les valeurs en attribuant des valeurs négatives comme espace réservé temporaire. Au sein de la transaction, MySQL considère les mises à jour comme une opération unique, empêchant la violation de contrainte unique.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!