Wiele aktualizacji w MySQL
Wiem, że można wstawiać wiele wierszy na raz, czy jest sposób na aktualizację wielu wierszy na raz (jak w jednym zapytaniu) w MySQL?
Edytuj: Na przykład mam następujące
Name id Col1 Col2
Row1 1 6 1
Row2 2 2 3
Row3 3 9 5
Row4 4 16 8
Chcę połączyć wszystkie poniższe aktualizacje w jedno zapytanie
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;
18 answers
Tak, to możliwe - możesz użyć INSERT ... PRZY AKTUALIZACJI DUPLIKATU KLUCZA.
Używając Twojego przykładu:
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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-06 14:33:41
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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-29 23:48:10
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:33:24
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-10 12:37:02
Wszystkie poniższe elementy mają zastosowanie do InnoDB.
Wydaje mi się, że znajomość prędkości 3 różnych metod jest ważna.Istnieją 3 metody:
- INSERT: INSERT with on DUPLICATE KEY UPDATE
- transakcja: w przypadku aktualizacji każdego rekordu w transakcji
- 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ń.
Co gorsza, metoda CASE była 41,1 x wolniejsza niż metoda INSERT w / 30 000 rekordów (6,1 x wolniejsza niż transakcja). I 75x wolniej w MyISAM. Metody INSERT I CASE pobiły nawet przy ~1000 rekordach. Nawet przy 100 rekordach metoda CASE jest ledwo szybsza.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).';');
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-12-28 19:16:20
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'
To powinno zadziałać.
W instrukcji MySQL dla wielu tabel znajduje się odniesienie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-24 23:56:42
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`");
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-12-28 19:17:08
Dlaczego nikt nie wspomina o wielu wypowiedziach w jednym zapytaniu?
W php używasz metody multi_query
instancji mysqli.
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-05 16:23:20
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();
Mam nadzieję, że to pomoże.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-06 21:32:06
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-01-02 19:48:31
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ę.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-06 14:20:23
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-09-04 17:08:58
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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2021-01-29 08:02:38
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-15 16:59:23
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-09 10:16:59
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'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:10:54
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'
To powinno osiągnąć to, czego szukasz. Po prostu dodaj więcej identyfikatorów.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-06-24 23:55:25
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-01-21 06:34:31