multiple - update mysql java

Actualizaciones múltiples en MySQL

Question

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

Editar: Por ejemplo tengo los siguientes

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

Quiero combinar todas las actualizaciones siguientes en una 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

¿Por qué nadie menciona varias declaraciones en una consulta ?

En php, multi_query método multi_query de la instancia mysqli.

Desde el manual de php.

MySQL opcionalmente permite tener varias declaraciones en una cadena de declaración. Enviar varias declaraciones a la vez reduce los viajes de ida y vuelta cliente-servidor, pero requiere un manejo especial.

Aquí está el resultado en comparación con otros 3 métodos en la actualización 30,000 en bruto. El código se puede encontrar here que se basa en la respuesta de @Dakusan

Transacción: 5.5194580554962
Insertar: 0.20669293403625
Caso: 16.474853992462
Multi: 0.0412278175354

Como puede ver, la consulta de varias declaraciones es más eficiente que la respuesta más alta.

Si recibe un mensaje de error como este:

PHP Warning: Error while sending SET_OPTION packet

Es posible que deba aumentar el max_allowed_packet en el archivo de configuración de mysql que en mi máquina es /etc/mysql/my.cnf y luego reiniciar mysqld.

Answer 2

Como tiene valores dinámicos, necesita usar un IF o un 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

Con PHP hice esto. Use el punto y coma, divídalo en una matriz y luego envíelo a través del bucle.

$con = new mysqli(''localhost'',''user1'',''password'',''my_database'');
$batchUpdate = true; /*You can choose between batch and single query */
$queryIn_arr = explode(";", $queryIn);
if($batchUpdate) /* My SQL prevents multiple insert*/
{
foreach($queryIn_arr as $qr)
{
if(strlen($qr)>3)
{
//echo ''<br>Sending data to SQL1:<br>''.$qr.''</br>'';
$result = $conn->query($qr);
}
}
}
else
{
$result = $conn->query($queryIn);
}
$con->close();

Answer 4

Hay una configuración que puede modificar llamada ''declaración múltiple'' que desactiva el ''mecanismo de seguridad'' de MySQL implementado para evitar (más de una) orden de inyección. Típico de la implementación ''brillante'' de MySQL, también evita que el usuario realice consultas eficientes.

Aquí ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) hay algo de información sobre la implementación en C de la configuración.

Si está usando PHP, puede usar mysqli para hacer declaraciones múltiples (creo que php ha enviado con mysqli por un tiempo)

$con = new mysqli(''localhost'',''user1'',''password'',''my_database'');
$query = "Update MyTable SET col1=''some value'' WHERE id=1 LIMIT 1;";
$query .= "UPDATE MyTable SET col1=''other value'' WHERE id=2 LIMIT 1;";
//etc
$con->multi_query($query);
$con->close();

Espero que ayude.

Answer 5

La pregunta es antigua, 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 dijo, si se inicia la consulta con filas cuyas claves principales no existen en la tabla, la consulta inserta nuevos registros "a medias". 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 insertar 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 la cláusula "caso / cuándo / entonces" y un enfoque ingenuo con la transacción. Puede obtener el código de python y los resultados here . 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 el código correcto y seguro para la inyección, por lo que personalmente me limito al enfoque más simple: utilizar transacciones.

Edit: Los hallazgos de Dakusan demuestran que mis estimaciones de rendimiento no son del todo válidas. Por favor, vea esta respuesta para otra investigación más elaborada.

Answer 6

Lo siguiente actualizará todas las filas en una tabla.

Update Table Set
Column1 = ''New Value''

La siguiente actualizará todas las filas donde el valor de Column2 es más de 5

Update Table Set
Column1 = ''New Value''
Where
Column2 > 5

Todos los Unkwntech de Unkwntech de actualizar más de una tabla.

UPDATE table1, table2 SET
table1.col1 = ''value'',
table2.col1 = ''value''
WHERE
table1.col3 = ''567''
AND table2.col6=''567''

Answer 7

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

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 8

Puede crear un alias de la misma tabla para darle los ID que desea insertar (si está realizando una actualización fila por fila:

UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id;

Además, debería parecer obvio que también puede actualizar desde otras tablas. En este caso, la actualización se duplica como una declaración "SELECCIONAR", que le proporciona los datos de la tabla que está especificando. Usted está indicando explícitamente en su consulta los valores de actualización, por lo que la segunda tabla no se ve afectada.

Answer 9

Sí ... es posible usando INSERT ON DUPLICATE KEY UPDATE instrucción sql ... sintaxis: INSERT INTO table_name (a, b, c) VALORES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = VALORES (a), b = VALORES (b), c = VALORES (c)

Answer 10

Sí, eso es posible, puede usar INSERT ... ON DUPLICATE KEY UPDATE.

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 11

También puede interesarle utilizar combinaciones en las actualizaciones, lo que también es posible.

Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4
-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.

Editar: si los valores que está actualizando no provienen de algún otro lugar de la base de datos, deberá realizar varias consultas de actualización.

Answer 12

Todo lo siguiente se aplica a InnoDB.

Siento que saber la velocidad de los 3 métodos diferentes es importante.

Hay 3 métodos:

1. INSERTAR: INSERTAR CON ACTUALIZACIÓN DE CLAVE EN DUPLICADO
2. TRANSACCIÓN: donde hace una actualización para cada registro dentro de una transacción
3. CASO: En qué caso / cuándo para cada registro diferente dentro de una ACTUALIZACIÓN

Acabo de probar esto, y el método INSERT fue 6.7x 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 aún tiene que ejecutar cada consulta individualmente, lo que lleva tiempo, aunque los resultados en la memoria, o algo así, durante la ejecución. El método TRANSACTION también es bastante costoso tanto en la replicación como en los registros de consultas.

Peor aún, el método CASE fue 41.1x más lento que el método INSERT con 30,000 registros (6.1x más lento que TRANSACTION). Y 75x más lento en MyISAM. Los métodos INSERT y CASE se rompieron incluso en ~ 1,000 registros. Incluso con 100 registros, el método CASE es BARELY más rápido.

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

Cosas de bonificación:

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_TA‌​BLES",""),"STRICT_AL‌​L_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_increment aumenta usando el método INSERT, lo probé también y parece que no es el caso.

El código para ejecutar las pruebas es el siguiente. También genera 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).'';'');
}

Answer 13

Usa una tabla temporal

// Reorder items
function update_items_tempdb(&$items)
{
shuffle($items);
$table_name = uniqid(''tmp_test_'');
$sql = "CREATE TEMPORARY TABLE `$table_name` ("
." `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
.", `position` int(10) unsigned NOT NULL"
.", PRIMARY KEY (`id`)"
.") ENGINE = MEMORY";
query($sql);
$i = 0;
$sql = '''';
foreach ($items as &$item)
{
$item->position = $i++;
$sql .= ($sql ? '', '' : '''')."({$item->id}, {$item->position})";
}
if ($sql)
{
query("INSERT INTO `$table_name` (id, position) VALUES $sql");
$sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
." WHERE `$table_name`.id = `test`.id";
query($sql);
}
query("DROP TABLE `$table_name`");
}

Answer 14

utilizar

REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);

Tenga en cuenta:

• ID tiene que ser una clave única primaria
• Si utiliza claves externas para hacer referencia a la tabla, REEMPLAZAR elimina y luego inserta, por lo que esto podría causar un error

Answer 15

UPDATE `your_table` SET
`something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`),
`something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`),
`something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`),
`something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`),
`something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`),
`something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`) 

// Solo lo estas construyendo en PHP como

$q = ''UPDATE `your_table` SET '';
foreach($data as $dat){
$q .= ''
`something` = IF(`id`="''.$dat->id.''","''.$dat->value.''",`something`),
`smth2` = IF(`id`="''.$dat->id.''", "''.$dat->value2.''",`smth2`),'';
}
$q = substr($q,0,-1);

Para que pueda actualizar la tabla de agujeros con una consulta

Answer 16

UPDATE table1, table2 SET table1.col1=''value'', table2.col1=''value'' WHERE table1.col3=''567'' AND table2.col6=''567''

Esto debería funcionar para ti.

Hay una referencia en el manual de MySQL para varias tablas.

Answer 17

UPDATE tableName SET col1=''000'' WHERE id=''3'' OR id=''5''

Esto debería lograr lo que estás buscando. Simplemente añada más id. Lo he probado.