mysql - tablas - insertar datos en una tabla sql

¿Cómo ACTUALO una fila en una tabla o INSERT si no existe?

Question

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 INSERT una nueva fila y UPDATE si hubo un error. Lamentablemente, el error en INSERT aborta la transacción actual.

• UPDATE la fila, y si no se modificaron las filas, INSERT una 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.

Answer 1

¿Podría usar un disparador de inserción? Si falla, haz una actualización.

Answer 2

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á.

Answer 3

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.

Answer 4

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.

Answer 5

No sé si vas a encontrar una solución neutral para la plataforma.

Esto se llama comúnmente "UPSERT".

Vea algunas discusiones relacionadas:

• Soluciones para INSERTAR O ACTUALIZAR en SQL Server
• Implementación de SQL Server 2005 de MySQL REPLACE INTO?

Answer 6

SQL estándar proporciona la instrucción MERGE para esta tarea. No todos los DBMS admiten la instrucción MERGE.

Answer 7

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 .

Answer 8

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.

Answer 9

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 ...

Answer 10

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)
;