mysql - tablas - insertar datos en una tabla sql
¿Cómo ACTUALO una fila en una tabla o INSERT si no existe? (10)
Tengo la siguiente tabla de contadores:
CREATE TABLE cache (
key text PRIMARY KEY,
generation int
);
Me gustaría incrementar uno de los contadores, o ponerlo a cero si la fila correspondiente aún no existe. ¿Hay alguna forma de hacerlo sin problemas de concurrencia en SQL estándar? La operación a veces es parte de una transacción, a veces por separado.
El SQL debe ejecutarse sin modificaciones en SQLite, PostgreSQL y MySQL, si es posible.
Una búsqueda arrojó varias ideas que sufren problemas de concurrencia o son específicas de una base de datos:
Intente
INSERTuna nueva fila yUPDATEsi hubo un error. Lamentablemente, el error enINSERTaborta la transacción actual.UPDATEla fila, y si no se modificaron las filas,INSERTuna nueva fila.MySQL tiene una cláusula
ON DUPLICATE KEY UPDATE.
EDITAR: Gracias por todas las excelentes respuestas. Parece que Paul tiene razón, y no hay una sola forma portátil de hacerlo. Eso es bastante sorprendente para mí, ya que parece una operación muy básica.
¿Podría usar un disparador de inserción? Si falla, haz una actualización.
En PostgreSQL no existe un comando de fusión, y en realidad escribirlo no es trivial; en realidad hay casos extremos extraños que hacen que la tarea sea "interesante".
El mejor enfoque (como en: trabajar en la mayoría de las condiciones posibles) es usar la función, como la que se muestra en el manual (merge_db).
Si no desea usar la función, generalmente puede salirse con la suya:
updated = db.execute(UPDATE ... RETURNING 1)
if (!updated)
db.execute(INSERT...)
Solo recuerde que no es a prueba de fallas y eventualmente fallará.
Haría algo como lo siguiente:
INSERT INTO cache VALUES (key, generation)
ON DUPLICATE KEY UPDATE (key = key, generation = generation + 1);
Establezca el valor de generación en 0 en el código o en el sql, pero usando el ON DUP ... para incrementar el valor. Creo que esa es la sintaxis de todos modos.
MySQL (y posteriormente SQLite) también admiten la sintaxis REPLACE INTO:
REPLACE INTO my_table (pk_id, col1) VALUES (5, ''123'');
Esto identifica automáticamente la clave principal y encuentra una fila coincidente para actualizar, insertando una nueva si no se encuentra ninguna.
No sé si vas a encontrar una solución neutral para la plataforma.
Esto se llama comúnmente "UPSERT".
Vea algunas discusiones relacionadas:
SQL estándar proporciona la instrucción MERGE para esta tarea. No todos los DBMS admiten la instrucción MERGE.
SQLite admite reemplazar una fila si ya existe:
INSERT OR REPLACE INTO [...blah...]
Puedes acortar esto a
REPLACE INTO [...blah...]
Este acceso directo se agregó para que sea compatible con la expresión MySQL REPLACE INTO .
Si está de acuerdo con el uso de una biblioteca que escribe el SQL para usted, entonces puede usar Upsert (actualmente solo Ruby y Python):
Pet.upsert({:name => ''Jerry''}, :breed => ''beagle'')
Pet.upsert({:name => ''Jerry''}, :color => ''brown'')
Eso funciona en MySQL, Postgres y SQLite3.
Escribe un procedimiento almacenado o una función definida por el usuario (UDF) en MySQL y Postgres. Utiliza INSERT OR REPLACE en SQLite3.
Si no tiene una forma común de actualizar o insertar atómicamente (por ejemplo, a través de una transacción), puede recurrir a otro esquema de bloqueo. Un archivo de 0 bytes, mutex del sistema, named pipe, etc ...
la cláusula ON DUPLICATE KEY UPDATE es la mejor solución porque: REPLACE hace un DELETE seguido de un INSERT, por lo que durante un período tan leve el registro se elimina creando la remota posibilidad de que una consulta vuelva a saltar si la página visto durante la consulta REPLACE.
Prefiero INSERTAR ... EN ACTUALIZACIÓN DUPLICADA ... por ese motivo.
La solución de jmoz es la mejor: aunque prefiero la sintaxis SET a los paréntesis
INSERT INTO cache
SET key = ''key'', generation = ''generation''
ON DUPLICATE KEY
UPDATE key = ''key'', generation = (generation + 1)
;