Comment mettre à jour une ligne dans un tableau ou l'insérer si elle n'existe pas ?

Question

J'ai le tableau de compteurs suivant :

CREATE TABLE cache (
    key text PRIMARY KEY,
    generation int
);

Je voudrais incrémenter un des compteurs, ou le mettre à zéro si la ligne correspondante n'existe pas encore. Existe-t-il un moyen de le faire sans problèmes de concurrence en SQL standard ? L'opération fait parfois partie d'une transaction, parfois elle est séparée.

Le SQL doit fonctionner sans modification sur SQLite, PostgreSQL et MySQL, si possible.

Une recherche a permis de trouver plusieurs idées qui souffrent de problèmes de concurrence ou qui sont spécifiques à une base de données :

• Essayez de INSERT une nouvelle ligne, et UPDATE s'il y avait une erreur. Malheureusement, l'erreur sur INSERT abandonne la transaction en cours.

• UPDATE la ligne, et si aucune ligne n'a été modifiée, INSERT une nouvelle rangée.

• MySQL dispose d'un ON DUPLICATE KEY UPDATE clause.

EDIT : Merci pour toutes ces réponses. Il semble que Paul ait raison, et qu'il n'y ait pas une seule façon portable de faire cela. C'est assez surprenant pour moi, car cela semble être une opération très basique.

Answer 1

MySQL (et par la suite SQLite) supporte également la syntaxe REPLACE INTO :

REPLACE INTO my_table (pk_id, col1) VALUES (5, '123');

Cette opération identifie automatiquement la clé primaire et trouve une ligne correspondante à mettre à jour, en insérant une nouvelle ligne si aucune n'est trouvée.

Answer 2

SQLite supporte remplacer une rangée s'il existe déjà :

INSERT OR REPLACE INTO [...blah...]

Vous pouvez raccourcir cela en

REPLACE INTO [...blah...]

Ce raccourci a été ajouté pour être compatible avec le programme MySQL REPLACE INTO expression.

Answer 3

Je ferais quelque chose comme ce qui suit :

INSERT INTO cache VALUES (key, generation)
ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);

Définir la valeur de génération à 0 dans le code ou dans le sql mais utiliser le ON DUP... pour incrémenter la valeur. Je pense que c'est la syntaxe de toute façon.

Answer 4

La clause ON DUPLICATE KEY UPDATE est la meilleure solution car : REPLACE effectue un DELETE suivi d'un INSERT, de sorte que, pendant une très courte période, l'enregistrement est supprimé, ce qui crée la très courte possibilité qu'une requête revienne en ayant sauté cet élément si la page a été consultée pendant la requête REPLACE.

Je préfère INSERT ... ON DUPLICATE UPDATE ... pour cette raison.

La solution de jmoz est la meilleure : bien que je préfère la syntaxe SET aux parenthèses

INSERT INTO cache 
SET key = 'key', generation = 'generation'
ON DUPLICATE KEY 
UPDATE key = 'key', generation = (generation + 1)
;

Answer 5

Je ne sais pas si vous allez trouver une solution neutre du point de vue de la plate-forme.

C'est ce qu'on appelle communément un "UPSERT".

Voir quelques discussions connexes :

• http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server
• http://stackoverflow.com/questions/234