Múltiples actualizaciones en MySQL

mysql sql sql-update


Sé que puedes insertar varias filas a la vez,¿hay alguna manera de actualizar varias filas a la vez (como en una consulta)en MySQL?

Editar:Por ejemplo,tengo lo siguiente

Name   id  Col1  Col2
Row1   1    6     1
Row2   2    2     3
Row3   3    9     5
Row4   4    16    8

Quiero combinar todas las siguientes actualizaciones en una sola consulta

UPDATE table SET Col1 = 1 WHERE id = 1;
UPDATE table SET Col1 = 2 WHERE id = 2;
UPDATE table SET Col2 = 3 WHERE id = 3;
UPDATE table SET Col1 = 10 WHERE id = 4;
UPDATE table SET Col2 = 12 WHERE id = 4;



Answer 1 Michiel de Mare


Sí,eso es posible-puedes usar INSERTAR ...EN LA ACTUALIZACIÓN DE LA LLAVE DUPLICADA.

Usando tu ejemplo:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);



Answer 2 Harrison Fisk


Como tienes valores dinámicos,necesitas usar un IF o CASE para que las columnas se actualicen.Se pone un poco feo,pero debería funcionar.

Usando tu ejemplo,podrías hacerlo como:

UPDATE table SET Col1 = CASE id 
                          WHEN 1 THEN 1 
                          WHEN 2 THEN 2 
                          WHEN 4 THEN 10 
                          ELSE Col1 
                        END, 
                 Col2 = CASE id 
                          WHEN 3 THEN 3 
                          WHEN 4 THEN 12 
                          ELSE Col2 
                        END
             WHERE id IN (1, 2, 3, 4);



Answer 3 Roman Imankulov


La pregunta es vieja,pero me gustaría ampliar el tema con otra respuesta.

Mi punto es que la forma más fácil de lograrlo es simplemente envolver múltiples consultas con una transacción. La respuesta aceptada INSERT ... ON DUPLICATE KEY UPDATE es un buen truco, pero uno debe ser consciente de sus inconvenientes y limitaciones:

  • Como se ha dicho,si se lanza la consulta con filas cuyas claves primarias no existen en la tabla,la consulta inserta nuevos registros "a medio hacer".Probablemente no es lo que quieres
  • Si tiene una tabla con un campo no nulo sin valor predeterminado y no desea tocar este campo en la consulta, obtendrá "Field 'fieldname' doesn't have a default value" advertencia de MySQL incluso si no lo hace t inserte una sola fila en absoluto. Te meterá en problemas si decides ser estricto y convertir las advertencias de mysql en excepciones de tiempo de ejecución en tu aplicación.

Hice algunas pruebas de rendimiento para tres de las variantes sugeridas, incluida la variante INSERT ... ON DUPLICATE KEY UPDATE , una variante con cláusula "case / when / then" y un enfoque ingenuo con transacción. Puede obtener el código Python y los resultados aquí . La conclusión general es que la variante con declaración de caso resulta ser dos veces más rápida que otras dos variantes, pero es bastante difícil escribir un código correcto y seguro para la inyección, por lo que personalmente me quedo con el enfoque más simple: usar transacciones.

Editar: Los hallazgos de Dakusan demuestran que mis estimaciones de desempeño no son del todo válidas. Consulte esta respuesta para ver otra investigación más elaborada.




Answer 4 newtover


No estoy seguro de por qué no se menciona todavía otra opción útil:

UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = _col2;



Answer 5 Dakusan


Todo lo siguiente se aplica a InnoDB.

Creo que es importante conocer las velocidades de los 3 métodos diferentes.

Hay tres métodos:

  1. INSERTAR:INSERTAR con ON DUPLICAR LA CLAVE ACTUALIZADA
  2. TRANSACCIÓN:Cuando haces una actualización para cada registro dentro de una transacción
  3. CASO:En el cual usted un caso/cuando para cada registro diferente dentro de una ACTUALIZACIÓN

Acabo de probar esto, y el método INSERT fue 6.7 veces más rápido para mí que el método TRANSACTION. Probé en un conjunto de 3.000 y 30.000 filas.

El método de TRANSACCIÓN todavía tiene que ejecutar cada consulta individualmente,lo que lleva tiempo,aunque agrupa los resultados en la memoria,o algo así,mientras se ejecuta.El método TRANSACCIÓN también es bastante caro tanto en los registros de replicación como en los de consulta.

Peor aún, el método CASE fue 41,1 veces más lento que el método INSERT con 30.000 registros (6,1 veces más lento que TRANSACTION). Y 75 veces más lento en MyISAM. Los métodos INSERT y CASE rompieron incluso con ~ 1,000 registros. Incluso con 100 registros, el método CASE es MUY más rápido.

Así que,en general,creo que el método INSERT es el mejor y el más fácil de usar.Las consultas son más pequeñas y más fáciles de leer y sólo ocupan una consulta de acción.Esto se aplica tanto a InnoDB como a MyISAM.

Cosas extra:

La solución para el problema INSERT de campo no predeterminado es desactivar temporalmente los modos SQL relevantes: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES","") . Asegúrese de guardar el sql_mode primero si planea revertirlo.

En cuanto a otros comentarios que he visto que dicen que el auto_incremento sube usando el método INSERT,este parece ser el caso en InnoDB,pero no en MyISAM.

El código para realizar las pruebas es el siguiente.También produce archivos .SQL para eliminar la sobrecarga del intérprete php

<?
//Variables
$NumRows=30000;

//These 2 functions need to be filled in
function InitSQL()
{

}
function RunSQLQuery($Q)
{

}

//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
    RunTest($i, $NumRows);

function RunTest($TestNum, $NumRows)
{
    $TheQueries=Array();
    $DoQuery=function($Query) use (&$TheQueries)
    {
        RunSQLQuery($Query);
        $TheQueries[]=$Query;
    };

    $TableName='Test';
    $DoQuery('DROP TABLE IF EXISTS '.$TableName);
    $DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
    $DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');

    if($TestNum==0)
    {
        $TestName='Transaction';
        $Start=microtime(true);
        $DoQuery('START TRANSACTION');
        for($i=1;$i<=$NumRows;$i++)
            $DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
        $DoQuery('COMMIT');
    }

    if($TestNum==1)
    {
        $TestName='Insert';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
    }

    if($TestNum==2)
    {
        $TestName='Case';
        $Query=Array();
        for($i=1;$i<=$NumRows;$i++)
            $Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
        $Start=microtime(true);
        $DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
    }

    print "$TestName: ".(microtime(true)-$Start)."<br>\n";

    file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}