Я знаю, что вы можете вставить несколько строк одновременно, есть ли способ обновить несколько строк одновременно (как в одном запросе) в 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;
Да, это возможно - вы можете использовать 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);
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'
Это должно работать для тебя.
В руководстве MySQL есть ссылка для нескольких таблиц.
Следующее обновит все строки в одной таблице
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'
Вас также может заинтересовать использование объединений в обновлениях, что также возможно.
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 tableName SET col1='000' WHERE id='3' OR id='5'
Это должно достичь того, что вы ищете. Просто добавьте больше идентификаторов. Я проверил это.
Поскольку у вас есть динамические значения, вам нужно использовать 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);
$commandTxt = 'UPDATE operations SET chunk_finished = CASE id '; foreach ($blockOperationChecked as $operationID => $operationChecked) $commandTxt .= " WHEN $operationID THEN $operationChecked "; $commandTxt .= 'ELSE id END WHERE id IN ('.implode(', ', array_keys(blockOperationChecked )).');';
Существует параметр, который вы можете изменить, называемый «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();
Надеюсь, это поможет.
mysqli::begin_transaction(..)
до отправки запроса и mysql::commit(..)
после. Или используйте START TRANSACTION
как первый и COMMIT
как последний оператор в самом запросе. Используйте временную таблицу
// 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 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id;
Кроме того, должно показаться очевидным, что вы также можете обновлять данные из других таблиц. В этом случае обновление удваивается как оператор «SELECT», предоставляя вам данные из таблицы, которую вы указываете. Вы явно указываете в своем запросе значения обновления, поэтому вторая таблица не изменяется.
Вопрос старый, но я хотел бы расширить тему другим ответом.
Суть в том, что самый простой способ добиться этого - просто заключить несколько запросов в транзакцию. Принятый ответ 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 оказывается в два раза быстрее, чем два других варианта, но написать для него правильный и безопасный для инъекций код довольно сложно, поэтому я лично придерживаюсь самого простого подхода: использования транзакций.
Редактировать: Выводы Дакусана доказывают, что мои оценки производительности не совсем верны. Пожалуйста, посмотрите этот ответ для другого, более сложного исследования.
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);
Таким образом, вы можете обновить таблицу отверстий одним запросом
something
к something
)Не уверен, почему еще одна полезная опция еще не упомянута:
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;
Да .. это возможно с помощью оператора INSERT ON DUPLICATE KEY UPDATE sql .. синтаксис: INSERT INTO table_name (a, b, c) ЗНАЧЕНИЯ (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = ЗНАЧЕНИЯ (а), б = ЗНАЧЕНИЯ (б), с = ЗНАЧЕНИЯ (с)
использование
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
Пожалуйста, обратите внимание:
- id должен быть первичным уникальным ключом
- если вы используете внешние ключи для ссылки на таблицу, REPLACE удаляет, а затем вставляет, так что это может вызвать ошибку
Все следующее относится к 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).';');
}
Почему никто не упоминает несколько операторов в одном запросе ?
В 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.
$mysqli->multi_query($sql)
заняло «0» секунд. Однако последующие запросы завершились неудачно, из-за чего я сделал это отдельной «программой».