Многочисленные обновления в MySQL

mysql sql sql-update


Я знаю,что вы можете вставить несколько строк одновременно,есть ли способ обновить несколько строк одновременно (как в одном запросе)в 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;



Answer 1 Michiel de Mare


Да,это возможно-ты можешь использовать 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);



Answer 2 Harrison Fisk


Поскольку у вас есть динамические значения,вам необходимо использовать IF или CASE для обновления столбцов.Это становится немного некрасиво,но должно работать.

Используя свой пример,вы могли бы сделать это как..:

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



Answer 3 Roman Imankulov


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

Я хочу сказать, что самый простой способ добиться этого - просто обернуть транзакцией несколько запросов. Принятый ответ INSERT ... ON DUPLICATE KEY UPDATE - хороший прием, но следует помнить о его недостатках и ограничениях:

  • Как уже говорилось,если вы случайно запустили запрос со строками,первичных ключей которых нет в таблице,то запрос вставляет новые "полузапеченные" записи.Вероятно,это не то,чего вы хотите.
  • Если у вас есть таблица с ненулевым полем без значения по умолчанию и вы не хотите касаться этого поля в запросе, вы получите предупреждение MySQL "Field 'fieldname' doesn't have a default value" даже если вы этого не сделаете. t вообще вставить одну строку. У вас возникнут проблемы, если вы решите быть строгим и превратите предупреждения mysql в исключения времени выполнения в своем приложении.

Я провел несколько тестов производительности для трех из предложенных вариантов, включая вариант INSERT ... ON DUPLICATE KEY UPDATE , вариант с предложением case / when / then и наивный подход с транзакцией. Вы можете получить код Python и результаты здесь . Общий вывод заключается в том, что вариант с оператором case оказывается в два раза быстрее, чем два других варианта, но для него довольно сложно написать правильный и безопасный для инъекций код, поэтому я лично придерживаюсь самого простого подхода: использования транзакций.

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




Answer 4 newtover


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

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;



Answer 5 Dakusan


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

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

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

  1. ИНСЕРТ:ИНСЕРТ с ДВИЖЕННЫМИ УПРАВЛЕНИЯМИ КЕЙ.
  2. ТРАНСАКТИРОВКА:Где вы делаете обновление для каждой записи в рамках транзакции.
  3. CASE:в котором вы делаете/когда для каждой отдельной записи в UPDATE

Я только что проверил это, и метод INSERT был для меня в 6,7 раза быстрее, чем метод TRANSACTION. Я пробовал набор из 3000 и 30 000 строк.

Метод "ТРАНСАКТИРОВАНИЕ" все равно должен выполнять каждый индивидуальный запрос,что занимает время,хотя во время выполнения он и заполняет результаты в памяти,или что-то вроде того.Метод 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,похоже,что так и есть в InnoDB,но не в MyISAM.

Код для запуска тестов следующий.Он также выводит .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).';');
}