Comment puis-je récupérer les anciennes valeurs de colonne lors d'une opération SQL UPDATE ?

Récupération des anciennes valeurs de colonnes lors d'une MISE À JOUR à l'aide de Pure SQL

Introduction

Lors de la mise à jour des lignes dans une base de données, il peut être utile d'accéder à l'original valeurs des colonnes mises à jour. Bien que SQL propose la clause RETURNING pour récupérer les valeurs post-mise à jour, il ne prend pas en charge la récupération directe des anciennes valeurs de colonne. Cet article explore une solution de contournement pour récupérer à la fois les nouvelles et les anciennes valeurs de colonne lors d'une opération UPDATE.

Solution utilisant une auto-jointure

Malgré la limitation de la clause RETURNING, il est possible d'accéder à la colonne d'origine valeurs en joignant la table à elle-même. Cette technique élimine le besoin de déclencheurs ou de procédures stockées.

UPDATE my_table
SET processing_by = our_id_info  -- unique to this worker
FROM my_table AS old_my_table
WHERE trans_nbr IN (
  SELECT trans_nbr
  FROM my_table
  GROUP BY trans_nbr
  HAVING COUNT(*) > 1
  LIMIT our_limit_to_have_single_process_grab
)
AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by;

Cette requête effectue les étapes suivantes :

1. Filtre et identifie les lignes à mettre à jour à l'aide d'une sous-requête.
2. Joint la table à elle-même en utilisant un alias (old_my_table) pour accéder à la colonne d'origine valeurs.
3. Définit Processing_by sur our_id_info et joint uniquement les ID de ligne correspondants.
4. Renvoie l'ID de ligne, la nouvelle valeur Processing_by et l'ancienne valeur Processing_by.

Éviter Problèmes d'écriture simultanés

Bien que la solution d'auto-adhésion fonctionne bien sans charge d'écriture simultanée, elle introduit des problèmes de concurrence si plusieurs travailleurs tentent de mettre à jour les mêmes lignes. Pour résoudre ce problème :

Niveau d'isolement sérialisable

La sérialisation des transactions avec le niveau d'isolement SERIALIZABLE garantit qu'aucune autre transaction ne peut accéder aux données pendant une opération. Cependant, cela peut avoir un impact sur les performances.

Verrouillage explicite

Le verrouillage de la ligne spécifique à mettre à jour à l'aide de la clause FOR UPDATE empêche d'autres transactions de la modifier jusqu'à la fin de l'opération. Cela garantit l'intégrité des données tout en maintenant les performances.

UPDATE tbl x
SET tbl_id = 23
, name = 'New Guy'
FROM (SELECT tbl_id, name FROM tbl WHERE tbl_id = 3 FOR UPDATE) y
WHERE x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
, x.tbl_id, x.name;

Conclusion

En utilisant une auto-jointure et en appliquant des mécanismes de contrôle de concurrence, il est possible de récupérer les anciennes et les nouvelles valeurs de colonne lors d'une MISE À JOUR. opération utilisant du SQL pur. Cette approche offre flexibilité et contrôle dans la gestion des modifications de données dans les systèmes de bases de données.

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!