value - Várias atualizações no MySQL




update table mysql server (12)

Eu sei que você pode inserir várias linhas ao mesmo tempo, existe uma maneira de atualizar várias linhas ao mesmo tempo (como em uma consulta) no MySQL?

Editar: Por exemplo, tenho o seguinte

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

Quero combinar todas as atualizações a seguir em uma 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;

A pergunta é antiga, mas gostaria de estender o tópico com outra resposta.

O que quero dizer é que a maneira mais fácil de conseguir isso é apenas agrupar várias consultas com uma transação. A resposta aceita INSERT ... ON DUPLICATE KEY UPDATE é um truque interessante, mas você deve estar ciente de suas desvantagens e limitações:

  • Como foi dito, se você iniciar a consulta com linhas cujas chaves primárias não existem na tabela, a consulta inserirá novos registros "semi-cozidos". Provavelmente não é o que você quer
  • Se você tiver uma tabela com um campo não nulo sem valor padrão e não quiser tocar nesse campo na consulta, receberá "Field 'fieldname' doesn't have a default value" aviso do MySQL, mesmo se você não ' t insira uma única linha. Isso causará problemas se você decidir ser rigoroso e transformar os avisos do mysql em exceções de tempo de execução no seu aplicativo.

Fiz alguns testes de desempenho para três das variantes sugeridas, incluindo a variante INSERT ... ON DUPLICATE KEY UPDATE , uma variante com a cláusula "case / when / then" e uma abordagem ingênua da transação. Você pode obter o código python e os resultados here . A conclusão geral é que a variante com declaração de caso é duas vezes mais rápida que duas outras variantes, mas é muito difícil escrever um código correto e seguro para injeção, portanto, eu pessoalmente adiro à abordagem mais simples: usar transações.

Edit: As descobertas de Dakusan provam que minhas estimativas de desempenho não são muito válidas. Por favor, veja esta resposta para outra pesquisa mais elaborada.


Com o PHP eu fiz isso. Use ponto-e-vírgula, divida-o em matriz e envie-o por loop.

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

Existe uma configuração que você pode alterar chamada 'declaração múltipla' que desativa o 'mecanismo de segurança' do MySQL implementado para impedir (mais de um) comando de injeção. Típico da implementação "brilhante" do MySQL, também impede que o usuário faça consultas eficientes.

Aqui ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) estão algumas informações sobre a implementação C da configuração.

Se você estiver usando PHP, poderá usar o mysqli para executar várias instruções (acho que o php já vem com o mysqli há algum tempo)

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


Não sei por que outra opção útil ainda não foi mencionada:

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;

Por que ninguém menciona várias instruções em uma consulta ?

No php, você usa o método multi_query da instância do mysqli.

Do manual php

O MySQL opcionalmente permite ter várias instruções em uma sequência de instruções. O envio de várias instruções ao mesmo tempo reduz as viagens de ida e volta do cliente-servidor, mas requer tratamento especial.

Aqui está o resultado comparando com outros 3 métodos na atualização 30.000 bruta. O código pode ser encontrado here com base na resposta de @Dakusan

Transação: 5.5194580554962
Inserção: 0,20669293403625
Caso: 16.474853992462
Multi: 0,0412278175354

Como você pode ver, a consulta de várias instruções é mais eficiente que a resposta mais alta.

Se você receber uma mensagem de erro como esta:

PHP Warning:  Error while sending SET_OPTION packet

Você pode precisar aumentar o max_allowed_packet no arquivo de configuração do mysql que em minha máquina é /etc/mysql/my.cnf e depois reiniciar o mysqld.


Sim, isso é possível - você pode usar INSERT ... ON DUPLICATE KEY UPDATE.

Usando seu exemplo:

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

Todos os itens a seguir se aplicam ao InnoDB.

Sinto que é importante conhecer a velocidade dos três métodos diferentes.

Existem 3 métodos:

  1. INSERT: INSERT com ON DUPLICATE KEY UPDATE
  2. TRANSACTION: onde você faz uma atualização para cada registro dentro de uma transação
  3. CASO: No qual você solicita / quando, para cada registro diferente dentro de um UPDATE

Acabei de testar isso, e o método INSERT foi 6,7x mais rápido do que o método TRANSACTION. Eu tentei em um conjunto de 3.000 e 30.000 linhas.

O método TRANSACTION ainda precisa executar cada consulta individualmente, o que leva tempo, apesar de agrupar os resultados na memória, ou algo assim, durante a execução. O método TRANSACTION também é bastante caro nos logs de replicação e consulta.

Pior ainda, o método CASE foi 41,1x mais lento que o INSERT com 30.000 registros (6,1x mais lento que TRANSACTION). E 75x mais lento no MyISAM. Os métodos INSERT e CASE chegaram a ~ 1.000 registros. Mesmo com 100 registros, o método CASE é MUITO MAIS rápido.

Portanto, em geral, acho que o método INSERT é melhor e mais fácil de usar. As consultas são menores e mais fáceis de ler e ocupam apenas 1 consulta de ação. Isso se aplica ao InnoDB e ao MyISAM.

Material bônus:

A solução para o problema INSERT de campo não padrão é desativar temporariamente os modos SQL relevantes: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES","") . Salve o sql_mode primeiro se planeja revertê-lo.

Quanto aos outros comentários que vi que dizem que o auto_increment sobe usando o método INSERT, eu testei isso também e parece não ser o caso.

O código para executar os testes é o seguinte. Também gera arquivos .SQL para remover a sobrecarga do interpretador 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).';');
}

Use uma tabela temporária

// 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`");
}

Você também pode estar interessado em usar junções nas atualizações, o que também é possível.

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: se os valores que você está atualizando não vierem de outro lugar no banco de dados, será necessário emitir várias consultas de atualização.


usar

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

Observe:

  • id deve ser uma chave exclusiva primária
  • se você usar chaves estrangeiras para fazer referência à tabela, REPLACE exclui e insere, portanto, isso pode causar um erro

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

Isso deve funcionar para você.

Há uma referência no manual do MySQL para várias tabelas.


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

Isso deve alcançar o que você está procurando. Basta adicionar mais IDs. Eu testei.





sql-update