Несколько обновлений в MySQL
Я знаю, что вы можете вставить несколько строк одновременно, есть ли способ обновить несколько строк одновременно (как в одном запросе) в MySQL?
Изменить: например, у меня есть следующее
Name id Col1 Col2
Row1 1 6 1
Row2 2 2 3
Row3 3 9 5
Row4 4 16 8
Я хочу объединить все следующие обновления в один запрос
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;
21 ответ
Да, это возможно - вы можете использовать INSERT ... ON DUPLICATE KEY UPDATE.
Используя ваш пример:
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);
Поскольку у вас есть динамические значения, вам нужно использовать IF или CASE для обновляемых столбцов. Это немного уродливо, но это должно сработать.
Используя ваш пример, вы можете сделать это следующим образом:
ОБНОВЛЕНИЕ таблицы SET Col1 = CASE id
КОГДА 1 ТОГДА 1
КОГДА 2 ТО 2
КОГДА 4 ТО 10
ELSE Col1
КОНЕЦ,
Col2 = CASE ID
КОГДА 3 ТО 3
КОГДА 4 ТО 12
ELSE Col2
КОНЕЦ
ГДЕ ИД В (1, 2, 3, 4);
Вопрос старый, но я бы хотел расширить тему другим ответом.
Моя точка зрения заключается в том, что самый простой способ добиться этого - просто заключить несколько запросов в транзакцию. Принятый ответ INSERT ... ON DUPLICATE KEY UPDATE это хороший взлом, но нужно помнить о его недостатках и ограничениях:
- Как уже говорилось, если вам случится запустить запрос со строками, первичные ключи которых не существуют в таблице, запрос вставит новые "недоделанные" записи. Вероятно, это не то, что вы хотите
- Если у вас есть таблица с ненулевым полем без значения по умолчанию и вы не хотите прикасаться к этому полю в запросе, вы получите
"Field 'fieldname' doesn't have a default value"Предупреждение MySQL, даже если вы вообще не вставляете ни одной строки. Это вызовет у вас проблемы, если вы решите быть строгим и превратите предупреждения mysql в исключения времени выполнения в вашем приложении.
Я провел несколько тестов производительности для трех предложенных вариантов, включая INSERT ... ON DUPLICATE KEY UPDATE вариант, вариант с предложением "case / when / then" и наивный подход с транзакцией. Вы можете получить код Python и результаты здесь. Общий вывод заключается в том, что вариант с оператором case оказывается в два раза быстрее, чем два других варианта, но написать для него правильный и безопасный для инъекций код довольно сложно, поэтому я лично придерживаюсь самого простого подхода: использования транзакций.
Редактировать: Выводы Dakusan доказывают, что мои оценки производительности не совсем верны. Пожалуйста, посмотрите этот ответ для другого, более сложного исследования.
Не уверен, почему еще одна полезная опция еще не упомянута:
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;
Все следующее относится к InnoDB.
Я чувствую, что знание скорости 3 различных методов очень важно.
Есть 3 метода:
- INSERT: INSERT с ON DUPLICATE KEY UPDATE
- СДЕЛКА: где вы делаете обновление для каждой записи в транзакции
- СЛУЧАЙ: В каком случае вы / когда для каждой отдельной записи в ОБНОВЛЕНИИ
Я только что проверил это, и метод INSERT был для меня в 6,7 раза быстрее, чем метод TRANSACTION. Я примерил набор из 3000 и 30000 строк.
Метод TRANSACTION по-прежнему должен запускать каждый отдельный запрос, который занимает время, хотя он выполняет пакетирование результатов в памяти или что-то во время выполнения. Метод TRANSACTION также довольно дорог в журналах репликации и запросов.
Хуже того, метод CASE был в 41,1 раза медленнее, чем метод INSERT, с 30 000 записей (в 6,1 раза медленнее, чем TRANSACTION). И в 75 раз медленнее в MyISAM. Методы INSERT и CASE не превышают 1000 записей. Даже при 100 записях метод CASE БЫСТРО быстрее.
В общем, я считаю, что метод INSERT является лучшим и простым в использовании. Запросы меньше и их легче читать, и они занимают только 1 запрос действия. Это относится как к InnoDB, так и к MyISAM.
Бонусные вещи:
Решение проблемы INSERT, отличной от поля по умолчанию, состоит в том, чтобы временно отключить соответствующие режимы SQL: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TABLES",""),"STRICT_ALL_TABLES",""), Обязательно сохраните sql_mode Во-первых, если вы планируете вернуть его.
Что касается других комментариев, я видел, что auto_increment увеличивается с использованием метода INSERT, я тоже это проверял, и, похоже, это не так.
Код для запуска тестов выглядит следующим образом. Он также выводит.SQL файлы для удаления накладных расходов интерпретатора 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).';');
}
Использовать временную таблицу
// 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`");
}
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'
Это должно работать для тебя.
В руководстве MySQL есть ссылка для нескольких таблиц.
Почему никто не упоминает несколько операторов в одном запросе?
В php вы используете multi_query метод экземпляра mysqli.
Из руководства по PHP
MySQL опционально позволяет иметь несколько операторов в одной строке операторов. Одновременная отправка нескольких операторов сокращает количество циклов между клиентом и сервером, но требует особой обработки.
Вот результат по сравнению с другими 3 методами в обновлении 30000 raw. Код можно найти здесь, который основан на ответе @Dakusan
Транзакция: 5.5194580554962
Вставка: 0,20669293403625
Дело: 16.474853992462
Мульти: 0.0412278175354
Как видите, запрос с несколькими утверждениями более эффективен, чем самый высокий ответ.
Если вы получаете сообщение об ошибке, подобное этому:
PHP Warning: Error while sending SET_OPTION packet
Возможно, вам придется увеличить max_allowed_packet в конфигурационном файле mysql, который на моей машине /etc/mysql/my.cnf и затем перезапустите mysqld.
Никто еще не упомянул, что для меня было бы намного проще сделать это - использовать редактор SQL, который позволяет выполнять несколько отдельных запросов. Этот снимок экрана из Sequel Ace, я предполагаю, что Sequel Pro и, возможно, другие редакторы имеют аналогичную функциональность. (Это, конечно, предполагает, что вам нужно запускать это только как одноразовую вещь, а не как интегрированную часть вашего приложения/сайта).
Существует параметр, который вы можете изменить, называемый "multi оператор", который отключает "механизм безопасности" MySQL, реализованный для предотвращения (более чем одной) команды внедрения. Типичная для "блестящей" реализации MySQL, она также не позволяет пользователю выполнять эффективные запросы.
Здесь ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html) приведена некоторая информация о реализации параметра в C.
Если вы используете PHP, вы можете использовать mysqli для нескольких операторов (я думаю, что php уже поставляется с mysqli)
$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();
Надеюсь, это поможет.
Вы можете создать псевдоним той же таблицы, чтобы получить идентификаторы, по которым вы хотите вставить (если вы обновляете строку за строкой:
UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id;
Кроме того, должно показаться очевидным, что вы также можете обновлять данные из других таблиц. В этом случае обновление удваивается как оператор "SELECT", предоставляя вам данные из таблицы, которую вы указываете. Вы явно указываете в своем запросе значения обновления, поэтому вторая таблица не изменяется.
Вас также может заинтересовать использование объединений в обновлениях, что также возможно.
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.
Изменить: если значения, которые вы обновляете, не поступают откуда-то еще из базы данных, вам нужно будет выполнить несколько запросов на обновление.
А теперь легкий путь
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
Я взял ответ от @newtover и расширил его, используя новую функцию json_table в MySql 8. Это позволяет вам создать хранимую процедуру для обработки рабочей нагрузки, а не создавать собственный текст SQL в коде:
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`;
Это на несколько мс медленнее, чем чистый SQL, но я рад принять удар, а не генерировать текст sql в коде. Не уверен, насколько он эффективен с огромными наборами записей (максимальный размер объекта JSON составляет 1 ГБ), но я использую его все время при обновлении 10 000 строк за раз.
Самый простой способ
UPDATE companies
SET vat_number =
(CASE id WHEN 1 THEN '123'
WHEN 2 THEN '345'
WHEN 3 THEN '456'
END)
WHERE id IN (1,2,3);
Да.. это возможно с помощью оператора INSERT ON DUPLICATE KEY UPDATE sql.. синтаксис: INSERT INTO table_name (a,b,c) ЗНАЧЕНИЯ (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE a= ЗНАЧЕНИЯ (а), B = ЗНАЧЕНИЯ (б), с = ЗНАЧЕНИЯ (с)
Использование
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
Пожалуйста, обратите внимание:
- id должен быть первичным уникальным ключом
- если вы используете внешние ключи для ссылки на таблицу, REPLACE удаляет, а затем вставляет, так что это может вызвать ошибку
Следующее обновит все строки в одной таблице
Update Table Set
Column1 = 'New Value'
Следующая обновит все строки, где значение Column2 больше 5
Update Table Set
Column1 = 'New Value'
Where
Column2 > 5
Существует весь пример Unkwntech по обновлению более чем одной таблицы.
UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567'
С PHP я сделал это. Используйте точку с запятой, разбейте ее на массив и отправьте через цикл.
$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();
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`)
// Вы просто строите это как php
$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);
Таким образом, вы можете обновить таблицу отверстий одним запросом