Wiele aktualizacji w MySQL

Ponieważ masz wartości dynamiczne, musisz użyć if lub CASE, aby kolumny miały zostać zaktualizowane. Robi się paskudnie, ale powinno zadziałać.

Używając Twojego przykładu, możesz to zrobić tak:

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

Pytanie jest stare, ale chciałbym rozszerzyć temat o inną odpowiedź.

Chodzi mi o to, że najprostszym sposobem, aby to osiągnąć, jest po prostu owinięcie wielu zapytań transakcją. Zaakceptowana odpowiedź INSERT ... ON DUPLICATE KEY UPDATE to fajny hack, ale trzeba mieć świadomość jej wad i ograniczeń:

• Jak już zostało powiedziane, jeśli zdarzy się uruchomić zapytanie z wierszami, których klucze podstawowe nie istnieją w tabeli, zapytanie wstawia nowe rekordy "half-baked". Prawdopodobnie to nie jest to, czego chcesz
• Jeśli masz tabelę z polem not null bez wartości domyślnej i nie chcesz dotykać tego pola w zapytaniu, otrzymasz ostrzeżenie MySQL "Field 'fieldname' doesn't have a default value", nawet jeśli w ogóle nie wstawisz jednego wiersza. Wpędzi cię to w kłopoty, jeśli zdecydujesz się być surowy i zamienisz Ostrzeżenia mysql w wyjątki w aplikacji.

Zrobiłem kilka testów wydajności dla trzech sugerowanych wariantów, w tym wariant INSERT ... ON DUPLICATE KEY UPDATE, wariant z klauzulą "case / when / then" i naiwne podejście do transakcji. Możesz uzyskać kod Pythona i wyniki tutaj . Ogólny wniosek jest taki, że wariant z case statement okazuje się dwa razy szybszy niż dwa inne warianty, ale dość trudno jest napisać poprawny i bezpieczny dla wtrysku kod, więc osobiście trzymam się najprostszego podejścia: korzystania z transakcji.

Edit: ustalenia Dakusan dowodzą, że moje szacunki wydajności nie są całkiem poprawne. Proszę zobaczyć tę odpowiedź dla innej, bardziej rozbudowanej badania.

Nie wiem, dlaczego Inna przydatna opcja nie jest jeszcze wymieniona:

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;

Wszystkie poniższe elementy mają zastosowanie do InnoDB.

Istnieją 3 metody:

1. INSERT: INSERT with on DUPLICATE KEY UPDATE
2. transakcja: w przypadku aktualizacji każdego rekordu w transakcji
3. CASE: w którym przypadku / gdy dla każdego innego rekordu w aktualizacji

Właśnie to przetestowałem, a metoda INSERT była 6.7 x szybsza dla mnie niż Metoda transakcji. Próbowałem na zestawie 3000 i 30000 rzędów.

Metoda transakcji nadal musi uruchamiać każde zapytanie z osobna, co wymaga czasu, chociaż podczas wykonywania zapisuje wyniki w pamięci lub coś takiego. Metoda transakcji jest również dość kosztowna zarówno w dziennikach replikacji, jak i zapytań.

Ogólnie rzecz biorąc, uważam, że metoda wstawek jest zarówno najlepsza, jak i najłatwiejsza w użyciu. Zapytania są mniejsze i łatwiejsze do odczytania i zajmują tylko 1 zapytanie akcji. Dotyczy to zarówno InnoDB, jak i MyISAM.

Bonus stuff:

Rozwiązaniem problemu INSERT non-default-field jest tymczasowe wyłączenie odpowiednich trybów SQL: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""). Pamiętaj, aby zapisać sql_mode najpierw, jeśli planujesz go przywrócić.

Co do innych komentarzy to widziałem, że auto_increment idzie w górę przy użyciu metody INSERT, to chyba tak jest w InnoDB, ale nie MyISAM.

Kod do uruchomienia testów jest następujący. To również wyjścia .Pliki SQL do usunięcia interpretera php

<?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).';');
}

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

W instrukcji MySQL dla wielu tabel znajduje się odniesienie.

Użyj tymczasowej tabeli

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

Dlaczego nikt nie wspomina o wielu wypowiedziach w jednym zapytaniu?

W php używasz metody multi_query instancji mysqli.

Z instrukcji php

MySQL opcjonalnie pozwala na posiadanie wielu poleceń w jednym łańcuchu poleceń. Wysyłanie wielu poleceń naraz zmniejsza podróż w obie strony klient-serwer, ale wymaga specjalnej obsługi.

Oto wynik w porównaniu z innymi metodami 3 w aktualizacji 30,000 raw. Kod można znaleźć tutaj co jest oparte na odpowiedzi od @ Dakusan

Transakcja: 5.5194580554962
Insert: 0.20669293403625
Sprawa: 16.474853992462
Multi: 0.0412278175354

Jak widać, Zapytanie o wiele poleceń jest bardziej efektywne niż najwyższa odpowiedź.

Jeśli otrzymasz komunikat o błędzie w następujący sposób:

PHP Warning:  Error while sending SET_OPTION packet

Może być konieczne zwiększenie max_allowed_packet w pliku konfiguracyjnym mysql, który na moim komputerze jest /etc/mysql/my.cnf, a następnie ponowne uruchomienie mysqld.

Istnieje ustawienie, które możesz zmienić o nazwie "Multi statement", które wyłącza "mechanizm bezpieczeństwa" MySQL zaimplementowany w celu zapobiegania (więcej niż jednej) poleceniu iniekcji. Typowe dla "genialnej" implementacji MySQL, uniemożliwia również użytkownikowi wykonywanie wydajnych zapytań.

TUTAJ ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) to kilka informacji na temat implementacji c ustawienia.

Jeśli używasz PHP, możesz użyć mysqli do wykonywania wielu poleceń (myślę, że php został wysłany z mysqli na chwilę teraz)

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

Możesz użyć aliasu tej samej tabeli, aby podać id, przez które chcesz wstawić (jeśli robisz aktualizację wiersz po wierszu:

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

Ponadto powinno się wydawać oczywiste, że można również aktualizować z innych tabel. W takim przypadku aktualizacja może służyć jako polecenie "SELECT", podając dane z tabeli, którą podajesz. Wyraźnie określasz w zapytaniu wartości aktualizacji, więc druga tabela nie ma wpływu.

Możesz być również zainteresowany używaniem łączy w aktualizacjach, co jest również możliwe.

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: jeśli aktualizowane wartości nie pochodzą z innego miejsca w bazie danych, musisz wysłać wiele zapytań o aktualizację.

And now the easy way

update my_table m, -- let create a temp table with populated values
    (select 1 as id, 20 as value union -- this part will be generated
     select 2 as id, 30 as value union -- using a backend code
     -- for loop 
     select N as id, X as value
        ) t
set m.value = t.value where t.id=m.id -- now update by join - quick

Wziąłem odpowiedź od @newtover i rozszerzyłem ją za pomocą nowej funkcji json_table w MySql 8. Pozwala to na utworzenie procedury składowanej do obsługi obciążenia, a nie budowania własnego tekstu SQL w kodzie:

drop table if exists `test`;
create table `test` (
  `Id` int,
  `Number` int,
  PRIMARY KEY (`Id`)
);
insert into test (Id, Number) values (1, 1), (2, 2);

DROP procedure IF EXISTS `Test`;
DELIMITER $$
CREATE PROCEDURE `Test`(
    p_json json
)
BEGIN
    update test s
        join json_table(p_json, '$[*]' columns(`id` int path '$.id', `number` int path '$.number')) v 
        on s.Id=v.id set s.Number=v.number;
END$$
DELIMITER ;

call `Test`('[{"id": 1, "number": 10}, {"id": 2, "number": 20}]');
select * from test;

drop table if exists `test`;

Jest kilka ms wolniej niż czysty SQL, ale jestem szczęśliwy, aby wziąć hit zamiast generować tekst sql w kodzie. Nie jestem pewien, jak wydajny jest z ogromnymi zestawami rekordów (obiekt JSON ma maksymalny rozmiar 1Gb), ale używam go cały czas podczas aktualizacji 10K wierszy na raz.

Użyj

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

Uwaga:

• id musi być głównym unikalnym kluczem
• Jeśli używasz kluczy obcych do odwoływać się do tabeli, zastępować usuwa następnie wstawia, więc może to cause an error

Tak .. jest to możliwe przy użyciu polecenia INSERT ON DUPLICATE KEY UPDATE SQL.. składnia: INSERT INTO table_name (A,b, C) VALUES(1,2,3), (4,5,6) Przy aktualizacji duplikatu klucza a = wartości(a),b=wartości(b), c=wartości(C)

Poniższy tekst zaktualizuje wszystkie wiersze w jednej tabeli

Update Table Set
Column1 = 'New Value'

Następny zaktualizuje wszystkie wiersze, w których wartość Column2 jest większa niż 5

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

Istnieje wszystkie Unkwntech przykład aktualizacji więcej niż jednej tabeli

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

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

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

/ / po prostu budujesz go w php jak

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

Więc możesz zaktualizować tabelę otworów jednym zapytaniem