Несколько обновлений в 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;
6.08.2008 14:12:09
16 ОТВЕТОВ
РЕШЕНИЕ

Да, это возможно - вы можете использовать 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);
641
6.08.2008 14:33:41
Если дубликатов нет, я не хочу вставлять эту строку. что должен делать id? потому что я получаю информацию с другого сайта, который поддерживает таблицы с идентификаторами. Я вставляю значения относительно этого идентификатора. если на сайте появятся новые записи, то я в итоге вставлю только идентификаторы и количество, кроме всей остальной информации. если и только если есть запись для идентификатора, он должен обновиться, иначе он должен пропустить. что мне делать?
Jayapal Chandran 30.08.2010 13:30:54
Примечание: этот ответ также предполагает, что ID является первичным ключом
JM4 12.12.2012 18:01:47
@JayapalChandran вы должны использовать INSERT IGNORE вместе с ON DUPLICATE KEY UPDATE. dev.mysql.com/doc/refman/5.5/en/insert.html
Haralan Dobrev 16.09.2013 14:15:39
@HaralanDobrev Использование INSERT IGNORE по-прежнему вставляет недублированные записи. которого Джаяпал хотел избежать. INSERT IGNORE просто превращает любые ошибки в предупреждение :( stackoverflow.com/questions/548541/…
Takehiro Adachi 24.10.2013 17:32:50
Этот ответ предполагает, что идентификатор является уникальным ключом (может быть первичным, как говорили другие), но что более важно, он предполагает, что других уникальных ключей не существует. Если есть, он может бросить гаечный ключ в работах.
Steve Horvath 3.12.2014 05:43:23
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

Это должно работать для тебя.

В руководстве MySQL есть ссылка для нескольких таблиц.

8
24.06.2010 23:56:42

Следующее обновит все строки в одной таблице

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'
-3
23.05.2017 12:10:54

Вас также может заинтересовать использование объединений в обновлениях, что также возможно.

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.

Изменить: если значения, которые вы обновляете, не поступают откуда-то еще из базы данных, вам нужно будет выполнить несколько запросов на обновление.

2
6.08.2008 14:20:23
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

Это должно достичь того, что вы ищете. Просто добавьте больше идентификаторов. Я проверил это.

-5
24.06.2010 23:55:25

Поскольку у вас есть динамические значения, вам нужно использовать 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);
127
29.12.2012 23:48:10
возможно, не так красиво писать для динамического обновления, но интересно взглянуть на функциональность оболочки ...
me_ 7.02.2017 21:55:10
@ user2536953, это может быть хорошо для динамического обновления тоже. Например, я использовал это решение в цикле в php:$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 )).');';
Boolean_Type 15.02.2017 08:43:28

Существует параметр, который вы можете изменить, называемый «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();

Надеюсь, это поможет.

3
6.03.2011 21:32:06
Это то же самое, что отправлять запросы отдельно. Единственное отличие состоит в том, что вы отправляете все это в одном сетевом пакете, но ОБНОВЛЕНИЯ будут по-прежнему обрабатываться как отдельные запросы. Лучше заключить их в одну транзакцию, тогда изменения будут внесены в таблицу сразу.
Marki555 12.04.2014 15:01:28
Как обернуть их в одну транзакцию? Покажите нам, пожалуйста.
TomeeNS 22.09.2014 18:20:20
@TomeeNS Использовать mysqli::begin_transaction(..)до отправки запроса и mysql::commit(..)после. Или используйте START TRANSACTIONкак первый и COMMITкак последний оператор в самом запросе.
Juha Palomäki 15.11.2016 23:55:47

Используйте временную таблицу

// 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`");
}
9
7.04.2011 09:20:05

Вы можете создать псевдоним той же таблицы, чтобы получить идентификаторы, по которым вы хотите вставить (если вы обновляете строку за строкой:

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

Кроме того, должно показаться очевидным, что вы также можете обновлять данные из других таблиц. В этом случае обновление удваивается как оператор «SELECT», предоставляя вам данные из таблицы, которую вы указываете. Вы явно указываете в своем запросе значения обновления, поэтому вторая таблица не изменяется.

3
2.01.2013 19:48:31

Вопрос старый, но я хотел бы расширить тему другим ответом.

Суть в том, что самый простой способ добиться этого - просто заключить несколько запросов в транзакцию. Принятый ответ 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 оказывается в два раза быстрее, чем два других варианта, но написать для него правильный и безопасный для инъекций код довольно сложно, поэтому я лично придерживаюсь самого простого подхода: использования транзакций.

Редактировать: Выводы Дакусана доказывают, что мои оценки производительности не совсем верны. Пожалуйста, посмотрите этот ответ для другого, более сложного исследования.

86
23.05.2017 11:33:24
Используя транзакции, очень хороший (и простой) совет!
mTorres 30.07.2014 17:24:21
Что делать, если мои таблицы не InnoDB Type?
TomeeNS 22.09.2014 18:26:33
Может ли кто-нибудь предоставить ссылку на то, на что похожи транзакции? И / или код для безопасного для инъекций кода для варианта с оператором case?
François M. 21.07.2015 16:44:46
Я нахожу информацию о скорости в этом посте ложной. Я написал об этом в посте ниже. stackoverflow.com/questions/3432/multiple-updates-in-mysql/…
Dakusan 3.10.2016 12:07:22
@Dakusan, отличный ответ. Большое спасибо за расширение, комментирование и исправление моих результатов.
Roman Imankulov 4.10.2016 07:04:14
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);

Таким образом, вы можете обновить таблицу отверстий одним запросом

-7
21.01.2014 06:34:31
Я не downvote, но я думаю , что это возражение делать множество, когда он не нужен (и вы по - прежнему делать это, когда вы настраиваете somethingк something)
v010dya 30.12.2014 09:04:16

Не уверен, почему еще одна полезная опция еще не упомянута:

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;
71
10.02.2015 12:37:02
Это лучшее. Особенно, если вы извлекаете значения для обновления из другого запроса SQL, как я делал.
v010dya 30.12.2014 08:55:15
Это было здорово для обновления таблицы с огромным количеством столбцов. Вероятно, я буду часто использовать этот запрос в будущем. Спасибо!
Casper Wilkes 9.01.2015 20:18:56
Я пробовал этот тип запроса. Но когда записи достигают 30к граничный сервер останавливается. Есть ли другое решение?
Bhavin Chauhan 27.09.2016 06:27:45
Это выглядит великолепно. Я попытаюсь объединить это с предложением WHERE, где первичные ключи не обновляются, а используются для определения столбцов, которые нужно изменить.
nl-x 2.03.2020 13:30:22
@BhavinChauhan Вы пытались использовать временную таблицу вместо join-select, чтобы обойти проблему?
nl-x 2.03.2020 13:32:49

Да .. это возможно с помощью оператора INSERT ON DUPLICATE KEY UPDATE sql .. синтаксис: INSERT INTO table_name (a, b, c) ЗНАЧЕНИЯ (1,2,3), (4,5,6) ON DUPLICATE KEY UPDATE a = ЗНАЧЕНИЯ (а), б = ЗНАЧЕНИЯ (б), с = ЗНАЧЕНИЯ (с)

-3
9.08.2014 10:16:59

использование

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

Пожалуйста, обратите внимание:

  • id должен быть первичным уникальным ключом
  • если вы используете внешние ключи для ссылки на таблицу, REPLACE удаляет, а затем вставляет, так что это может вызвать ошибку
-1
15.03.2016 16:59:23

Все следующее относится к InnoDB.

Я чувствую, что знание скорости 3 различных методов очень важно.

Есть 3 метода:

  1. INSERT: INSERT с ON DUPLICATE KEY UPDATE
  2. СДЕЛКА: где вы делаете обновление для каждой записи в транзакции
  3. СЛУЧАЙ: В каком случае вы / когда для каждой отдельной записи в ОБНОВЛЕНИИ

Я только что проверил это, и метод 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).';');
}
39
8.03.2020 07:13:22
Вы делаете работу Господа здесь;) Очень ценится.
chili 29.01.2018 12:14:01
Тестируя некоторую производительность между GoLang и PHP, используя 40 тыс. Строк на MariaDB, я получал 2 секунды на PHP и более 6 секунд на golang ... Ну, мне всегда говорили, что GoLang будет работать быстрее, чем PHP !!! Итак, я начинаю задаваться вопросом, как улучшить производительность ... Используя INSERT ... ON DUPLICATE KEY UPDATE ... Я получил 0,74 секунды на Golang и 0,86 секунды на PHP !!!!
Diego Favero 2.04.2019 18:13:13
Суть моего кода заключается в том, чтобы ограничить результаты синхронизации строго инструкциями SQL, а не кодом для языка или библиотек. GoLang и PHP - это два совершенно разных языка, предназначенных для совершенно разных вещей. PHP предназначен для скриптовой среды с одним прогоном в одном потоке с преимущественно ограниченной и пассивной сборкой мусора. GoLang предназначен для долго работающих скомпилированных приложений с агрессивной сборкой мусора и многопоточностью как одной из основных функций языка. Они едва ли могут быть более разными с точки зрения языковой функциональности и причины. [Продолжение]
Dakusan 3.04.2019 03:35:43
Поэтому при выполнении тестов обязательно ограничивайте измерения скорости строго вызовами функции «Запрос» для оператора SQL. Сравнение и оптимизация других частей исходного кода, которые не являются строго запросом запроса, подобны сравнению яблок и апельсинов. Если вы ограничите свои результаты этим (имея предварительно скомпилированные строки и готовые к работе), тогда результаты должны быть очень похожими. Любые различия на этом этапе являются ошибкой библиотеки языка SQL, а не обязательно самого языка. На мой взгляд, решение INSERT ON DUPLICATE было и всегда будет лучшим вариантом. [Продолжение]
Dakusan 3.04.2019 03:41:23
Что касается вашего комментария о том, что GoLang быстрее, то это невероятно широкое утверждение, которое не учитывает множество предостережений или нюансов этих языков и их конструкции. Java является интерпретируемым языком, но я узнал 15 лет назад, что он может почти соответствовать (и, возможно, даже иногда) превосходить C по скорости в определенных сценариях. И C - это скомпилированный язык, и самый распространенный из системных языков низкого уровня, кроме ассемблера. Мне очень нравится то, что делает GoLang, и он определенно обладает мощью и гибкостью, чтобы стать одной из самых распространенных и оптимизированных систем [Продолжение]
Dakusan 3.04.2019 03:46:42

Почему никто не упоминает несколько операторов в одном запросе ?

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

6
5.07.2017 16:23:20
Все приведенные ниже сравнения выполняются в соответствии с тестом INSERT. Я просто запустил тест в тех же условиях, и без транзакций он был в 145 раз медленнее на 300 строк и 753 медленнее для 3000 строк. Первоначально я начал с 30 000 строк, но я пошел приготовить себе обед и вернулся, и он все еще шел. Это имеет смысл, поскольку выполнение отдельных запросов и сброс каждого из них в базу данных будет смехотворно дорогим. Особенно с репликацией. Включение транзакций имеет большое значение. При 3000 строк это заняло в 1,5 раза больше, а при 30000 строк - 2,34 . [продолжение]
Dakusan 26.11.2017 22:04:38
Но вы были правы в том, что это быстро (с транзакциями). И на 3000, и на 30000 строк это было быстрее, чем все, кроме метода INSERT. Нет абсолютно никакого способа получить лучшие результаты при выполнении 1 запроса, чем 30 000 запросов, даже если они объединены в специальный вызов MySQL API. Выполнение только 300 строк, это было НАМНОГО быстрее, чем все другие методы (к моему удивлению), что соответствует примерно той же кривой графика, что и метод CASE. Это быстрее можно объяснить двумя способами. Во-первых, метод INSERT всегда вставляет 2 строки из-за «ON DUPLICATE KEY [продолжение]
Dakusan 26.11.2017 22:18:14
ОБНОВЛЕНИЕ », вызывающее как« ВСТАВКА », так и« ОБНОВЛЕНИЕ ». Другое дело, что из-за поиска в индексе меньше работы в процессоре SQL для редактирования только одной строки за раз. Я не уверен, как вы получили другие результаты, чем я, но ваш дополнительный тест выглядит солидно. На самом деле я даже не уверен, как репликация будет обрабатывать этот вызов. Это также будет работать только для выполнения вызовов UPDATE. Вызовы вставки всегда будут самыми быстрыми с одним запросом INSERT.
Dakusan 26.11.2017 22:21:58
Я делал 300 ОБНОВЛЕНИЙ за один раз для таблицы, чтобы исправить ошибку в цикле for, что заняло 41 секунду. Помещение одних и тех же запросов UPDATE в один $mysqli->multi_query($sql)заняло «0» секунд. Однако последующие запросы завершились неудачно, из-за чего я сделал это отдельной «программой».
Chris K 11.01.2018 18:30:14
Спасибо. Был в состоянии обновить около 5 тысяч строк (больше не проверял), используя несколько запросов. Если кто-то ищет решение для PDO: stackoverflow.com/questions/6346674/…
Scofield 20.02.2019 19:20:31