Bienvenue chez les artisans développeurs !

MariaDB / MySQL - UPSERT avec les procédures stockées

Posté par Alexis
Dernière révision :

Le problème

Imaginez de devoir délivrer des licences.
Votre analyse comporte 4 tables :

Comme on essaye d'éviter les relations ternaires, on n'évite de faire :
Licences ( IDLicences, IDApplications, IDClients, IDPostes )

Mais plutôt :
Applications_Clients ( IDApplications_Clients, IDApplications, IDClients )
Licences ( IDLicences, IDApplications_Clients, IDPostes )

Note : Et en plus cette table Applications_Clients peut stocker le nombre maximum de licences délivrables.

Mais cela signifie que lors de la délivrance de la 1ère licence pour une application/client, le tuple de Applications_Clients n'existe pas !
Et là, les ennuis commence.

Naïvement on peut faire :

  1. REPLACE
  2. Applications_Clients(IDApplications,IDClients)
  3. VALUES
  4. (...)

Ou
  1. INSERT IGNORE INTO
  2. Applications_Clients(IDApplications,IDClients)
  3. VALUES
  4. (...)

Mais

1. REPLACE

REPLACE supprime puis ajoute l'enregistrement. Ce qui nous amène à 3 constats :
a - Cela provoque le calcul d'un nouvel AUTO_INCREMENT et casse par conséquent les liaisons existantes. Imaginons d'avoir une table DernièresConnexions liée à Licences afin de mémoriser les 10 dernières. L'utilisation de REPLACE est donc à proscrire.
b - La génération d'un nouvel AUTO_INCREMENT n'a que peu d'importance dans la majorité des cas. Mais si vous manipulez, comme moi, des bases à fortes volumétries, vous êtes forcément attentif à la valeur maximale d'un INT UNSIGNED située à 4 294 967 295. En rapport à 200 millions d'enregistrements, le multiplicateur n'est que de 21. C'est à dire que la génération de l'AUTO_INCREMENT plus de 21 fois provoquera un dépassement de la limite du champ ! Certains pourraient arguer qu'il suffira d'utiliser alors un BIGINT UNSIGNED pour que cela ne soit plus un problème ? Mais cela reviendrait à doubler l'espace nécessaire de la PRIMARY KEY dont on connait l'importance dans la vitesse d'exécution de certaines requêtes ! Et ne constitue pas une solution car ce n'est pas à la conséquence mais à la cause à laquelle il faut apporter réponse.
c - SELECT LAST_INSERT_ID() renvoie la valeur de l'ID de l'enregistrement inséré

2. INSERT IGNORE

a - Ne "cassent" pas les relations car l'insertion est simplement ignoré
b - Comme REPLACE, incrémenta la prochaine valeur de l'AUTO_INCREMENT
c - SELECT LAST_INSERT_ID() renvoie bien le dernier mais sûrement pas celui de la ligne qui aurait dû être insérée !

La solution

Vous l'aurez compris, elle passe par une procédure stockée. D'autres manières de procéder peuvent exister et faire "le job" mais je vous confie la mienne, éprouvée depuis très longtemps. Le code est volontairement simplifié afin d'aller à l'essentiel.

  1. CREATE PROCEDURE pcdLicencesAddA20(
  2. IN _IDApplications INT UNSIGNED,
  3. IN _IDClients INT UNSIGNED,
  4. IN _IDPostes INT UNSIGNED
  5. )
  6.  
  7. BEGIN
  8.  
  9. DECLARE entIDApplications_Clients INT UNSIGNED DEFAULT 0;
  10.  
  11. SELECT
  12. A_C.IDApplications_Clients INTO entIDApplications_Clients
  13. FROM
  14. Applications_Clients A_C
  15. WHERE
  16. A_C.IDApplications=_IDApplications
  17. AND A_C.IDClients=_IDClients
  18. ;
  19.  
  20. IF entIDApplications_Clients=0 THEN
  21. INSERT INTO
  22. Applications_Clients(IDApplications,IDClients)
  23. VALUES
  24. (_IDApplications,_IDClients)
  25. ;
  26. SELECT LAST_INSERT_ID() INTO entIDApplications_Clients
  27. END IF;
  28.  
  29. INSERT INTO
  30. Licences(IDApplications_Clients,IDPostes)
  31. VALUES
  32. (entIDApplications_Clients,_IDPostes)
  33. ;
  34.  
  35. SELECT LAST_INSERT_ID();# Renvoie IDLicences
  36.  
  37. END

Quelques explications sont peut-être les bienvenues ?

Conclusion

J'espère vous avoir apporté quelques lumières sur l'utilisation des procédures stockées dans ce contexte d'UPSERT dans vos bases MariaDB / MySQL.
Comme vous l'avez vu, ce n'est pas très compliqué. Vous ne générez pas d'AUTO_INCREMENT fantôme et vous n'envoyez qu'une seule requête au serveur. De plus, c'est très simple à utiliser car un simple :

CALL pcdLicencesAddA20(...)

Suffit à l'exécuter.

Reste plus qu'à vous lancer 😉

Commentaires

Pas encore de commentaire



Je ne suis pas un robot et je sais que