Várias atualizações em MySQL

votos
315

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

Edit: Por exemplo, eu tenho o seguinte

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

Eu quero combinar todas as seguintes atualizações 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;
Publicado 06/08/2008 em 15:12
fonte usuário
Em outras línguas...                            


17 respostas

votos
542

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

Usando o seu exemplo:

INSERT IGNORE  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);
Respondeu 06/08/2008 em 15:33
fonte usuário

votos
107

Desde que você tem valores dinâmicos, você precisa usar um IF ou CASE para as colunas a ser atualizado. Fica meio feio, mas ele deve funcionar.

Usando o seu exemplo, você poderia fazê-lo gosto:

UPDATE tabela SET Col1 = id CASE 
                          QUANDO 1 então 1 
                          QUANDO 2, então dois 
                          QUANDO 4 ENTÃO 10 
                          MAIS Col1 
                        FIM, 
                 Col2 = id CASE 
                          QUANDO 3 depois 3 
                          QUANDO 4 ENTÃO 12 
                          MAIS Col2 
                        FIM
             ONDE EM ID de (1, 2, 3, 4);
Respondeu 17/09/2008 em 15:55
fonte usuário

votos
78

A questão é antiga, mas eu gostaria de estender o assunto com outra resposta.

Meu ponto é, a maneira mais fácil de conseguir isso é apenas para embrulhar várias consultas com uma transação. A resposta aceita INSERT IGNORE ... ON DUPLICATE KEY UPDATEé um bom truque, mas um deve estar ciente dos seus inconvenientes e limitações:

  • Como foi dito, se acontecer de você lançar a consulta com linhas cujas chaves primárias não existem na tabela, a consulta insere novos registros "meia-boca". Provavelmente não é o que você quer
  • Se você tem uma tabela com um campo não nulo, sem valor padrão e não quero tocar neste campo na consulta, você vai ter "Field 'fieldname' doesn't have a default value"aviso MySQL, mesmo se você não inserir uma única linha em tudo. Ele vai trazer-lhe problemas, se você decidir ser rigoroso e vire avisos de MySQL em exceções de tempo de execução em seu aplicativo.

Eu fiz alguns testes de desempenho para três variantes sugeridas, incluindo a INSERT IGNORE ... ON DUPLICATE KEY UPDATEvariante, uma variante com "/ quando / caso, então" cláusula e uma abordagem ingênua com a transação. Você pode obter o código python e os resultados aqui . A conclusão geral é que a variante com a declaração caso acaba por ser duas vezes mais rápido como duas outras variantes, mas é muito difícil escrever código correto e injeção-seguro para isso, então eu, pessoalmente, ficar com a abordagem mais simples: usando transações.

Edit: Apreciação do Dakusan provar que minhas estimativas de desempenho não são bastante válido. Por favor, veja esta resposta para uma outra pesquisa, mais elaborado.

Respondeu 24/06/2013 em 21:14
fonte usuário

votos
46

Não sei por que outra opção útil ainda não é mencionado:

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;
Respondeu 26/09/2013 em 17:01
fonte usuário

votos
20

Toda a seguinte diz respeito a InnoDB.

Sinto-me saber as velocidades dos 3 métodos diferentes é importante.

Existem 3 métodos:

  1. INSERÇÃO IGNORE: INSERIR IGNORE com ON DUPLICATE KEY UPDATE
  2. OPERAÇÃO: Onde você faz uma atualização para cada registro em uma transação
  3. CASO: Em que você um caso / quando para cada registro diferente dentro um UPDATE

Eu só testei isso, e o INSERT IGNORE método era 6.7x mais rápido para mim do que o método de transação. Tentei em um conjunto de ambos os 3.000 e 30.000 linhas.

O método transação ainda tem de executar cada um individualmente consulta, o que leva tempo, embora lotes os resultados na memória, ou algo assim, durante a execução. O método de operação também é bastante caro em ambos os logs de replicação e de consulta.

Pior ainda, o método do caso foi 41.1x mais lento do que o INSERT método w / 30.000 registros (6,1x mais lento do que transação) IGNORE. E 75x mais lento em MyISAM. INSERÇÃO ignorar e métodos CASE quebrou mesmo em ~ 1.000 registros. Mesmo em 100 registros, o método do caso é pouco mais rápido.

Assim, em geral, eu sinto o INSERT IGNORE método é tanto melhor e mais fácil de usar. As consultas são menores e mais fáceis de ler e só ocupam uma consulta de ação. Isso se aplica tanto InnoDB e MyISAM.

material bônus:

A solução para o INSERT IGNORE problema não-default-campo é para desligar temporariamente os modos SQL relevantes: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES",""). Certifique-se de salvar o sql_modeprimeiro, se você está pensando em reverter isso.

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

Código para executar os testes é o seguinte. Ele também gera arquivos .sql para remover intérprete php sobrecarga

<?
//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 IGNORE  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 IGNORE  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).';');
}
Respondeu 03/10/2016 em 11:58
fonte usuário

votos
8

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 IGNORE  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`");
}
Respondeu 07/04/2011 em 08:34
fonte usuário

votos
8
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 MySQL para várias tabelas.

Respondeu 06/08/2008 em 15:14
fonte usuário

votos
3

Há uma configuração que você pode alterar chamado de "múltiplas declaração que desativa do MySQL 'mecanismo de segurança' implementadas para evitar (mais de um) de comando da injeção. Típico para a implementação 'brilhante' do MySQL, como também impede usuário de fazer consultas eficientes.

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

Se você estiver usando PHP, você pode usar mysqli fazer declarações múltiplas (eu acho que o PHP tem fornecido com mysqli por um tempo agora)

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

Respondeu 06/03/2011 em 22:32
fonte usuário

votos
2

Você pode apelidar à mesma mesa para lhe dar os id você deseja inserir pelo (se você estiver fazendo uma atualização de linha por linha:

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

Além disso, ele deve parecer óbvio que você também pode atualizar a partir de outras mesas também. Neste caso, a atualização funciona como uma declaração "SELECT", dando-lhe os dados da tabela que você está especificando. Você está afirmando explicitamente na sua consulta os valores de atualização assim, a segunda tabela não é afetado.

Respondeu 02/01/2013 em 20:48
fonte usuário

votos
2

Você pode também estar interessado em usar junta-se sobre as 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.

Edit: Se os valores que está atualizando não estão vindo de outro lugar no banco de dados, você vai precisar para emitir várias consultas de atualização.

Respondeu 06/08/2008 em 15:20
fonte usuário

votos
1

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

No PHP, você usa multi_querymétodo de instância mysqli.

Desde o manual do php

MySQL opcionalmente permite ter várias instruções em uma cadeia de instrução. O envio de várias instruções ao mesmo tempo reduz cliente-servidor rodada viagens, mas requer tratamento especial.

Aqui está o resultado em comparação com outros 3 métodos de atualização 30.000 cru. Código pode ser encontrada aqui que se baseia na resposta do @Dakusan

Transação: 5,5194580554962
Insert: ,20669293403625
caso: 16,474853992462
multi: ,0412278175354

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

Se você receber mensagem de erro como este:

PHP Warning:  Error while sending SET_OPTION packet

Pode ser necessário aumentar o max_allowed_packetarquivo de configuração em mysql que na minha máquina é /etc/mysql/my.cnfe, em seguida, reiniciar o mysqld.

Respondeu 05/07/2017 em 16:23
fonte usuário

votos
-1

usar

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

Observe:

  • id tem que ser uma chave única primária
  • se você usar chaves estrangeiras para fazer referência a tabela, REPLACE exclusões em seguida, insere, então isso pode causar um erro
Respondeu 15/03/2016 em 16:59
fonte usuário

votos
-3

Sim .it é possível usando INSERIR IGNORE ON instrução SQL update CHAVE DUPLICATE .. sintaxe: INSERIR IGNORE INTO table_name (A, B, C) VALUES (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = valores (a), b = VALORES (b), c = VALORES (c)

Respondeu 09/08/2014 em 11:16
fonte usuário

votos
-3

A seguir irá atualizar todas as linhas em uma tabela

Update Table Set
Column1 = 'New Value'

O próximo irá atualizar todas as linhas onde o valor da Column2 é mais do que 5

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

Há toda Unkwntech exemplo de atualização mais de uma tabela s'

UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567'
Respondeu 06/08/2008 em 15:18
fonte usuário

votos
-4

Com PHP eu fiz isso. Use ponto e vírgula, dividi-lo em conjunto e, em seguida, enviar via 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();
Respondeu 29/09/2015 em 22:11
fonte usuário

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

Isso deve conseguir o que you'r procurando. Basta adicionar mais id. Eu testei.

Respondeu 06/08/2008 em 15:22
fonte usuário

votos
-7
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`) 

// Você apenas construí-lo em 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);

Assim, você pode atualizar a tabela buraco com uma consulta

Respondeu 28/08/2013 em 16:40
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more