Несколько обновлений в MySQL

голоса
315

Я знаю, что вы можете вставить несколько строк сразу, есть ли способ обновить несколько строк сразу (как, в одном запросе) в MySQL?

Edit: Например, у меня есть следующий

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;
Задан 06/08/2008 в 15:12
источник пользователем
На других языках...                            


17 ответов

голоса
542

Да, это возможно - вы можете использовать INSERT IGNORE ... дублирование KEY UPDATE.

Используя ваш пример:

INSERT IGNORE  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);
Ответил 06/08/2008 d 15:33
источник пользователем

голоса
107

Поскольку у вас есть динамические значения, вам нужно использовать IF или СЛУЧАЙ для столбцов, которые будут обновлены. Это становится своего рода уродливое, но он должен работать.

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

ОБНОВЛЕНИЕ таблица SET Стлб1 = ИДЕНТИФИКАТОР 
                          КОГДА 1 THEN 1 
                          КОГДА 2 ТО 2 
                          КОГДА 4 ТО 10 
                          ELSE Col1 
                        КОНЕЦ, 
                 Col2 = СЛУЧАЙ идентификатор 
                          КОГДА 3 ТОГДА 3 
                          КОГДА 4 ТО 12 
                          ELSE Col2 
                        КОНЕЦ
             WHERE ID IN (1, 2, 3, 4);
Ответил 17/09/2008 d 15:55
источник пользователем

голоса
78

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

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

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

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

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

Ответил 24/06/2013 d 21:14
источник пользователем

голоса
46

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

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;
Ответил 26/09/2013 d 17:01
источник пользователем

голоса
20

Все из перечисленных ниже относится к InnoDB.

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

Есть 3 способа:

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

Я только проверил это, и ВСТАВИТЬ ИГНОРИРУЙТЕ метод был 6.7x быстрее для меня , чем метод TRANSACTION. Я попробовал на множестве обоих 3000 и 30000 строк.

Метод TRANSACTION еще должен работать каждый по отдельности запрос, который занимает много времени, хотя партии результатов в памяти, или что-то, во время выполнения. Метод TRANSACTION также довольно дорогой в обеих репликации и запросов журналов.

Еще хуже, метод случай был 41.1x медленнее , чем INSERT IGNORE метод ж / 30000 записей (6.1x медленнее , чем ТРУДЫАМИ). И 75x медленнее MyISAM. ВСТАВИТЬ ИГНОРИРУЙТЕ и методы СЛУЧАЙ безубыточным на ~ 1000 записей. Даже на 100 записей, метод СЛУЧАЙ едва быстрее.

Таким образом, в общем, я чувствую ВСТАВКУ ИГНОРИРУЙТЕ метод является лучшим и простым в использовании. Запросы меньше и легче читать и принимать только до 1 запроса действия. Это относится и к InnoDB и MyISAM.

Бонус материал:

Решение для INSERT IGNORE проблема не по умолчанию, поле временно отключить соответствующие режимы SQL: SET SESSION sql_mode=REPLACE(REPLACE(@@SESSION.sql_mode,"STRICT_TRANS_TA‌​BLES",""),"STRICT_AL‌​L_TABLES",""). Убедитесь в том , чтобы сохранить sql_modeпервый , если вы планируете его возвращаясь.

Что касается других замечаний, которые я видел, что говорят, что auto_increment идет вверх, используя INSERT IGNORE метода, я проверил, что тоже, и это, кажется, не так.

Код для запуска тестов заключается в следующем. Он также выводит .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 IGNORE  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 IGNORE  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).';');
}
Ответил 03/10/2016 d 11:58
источник пользователем

голоса
8

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

// 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 IGNORE  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`");
}
Ответил 07/04/2011 d 08:34
источник пользователем

голоса
8
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'

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

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

Ответил 06/08/2008 d 15:14
источник пользователем

голоса
3

Существует параметр, который может изменить называется «мульти оператор», который отключает в MySQL «механизм безопасности» реализован для предотвращения (более одного) команда впрыска. Типичный для «блестящей» реализации MySQL, он также предотвращает пользователь от выполнения эффективных запросов.

Здесь ( http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html ) некоторая информация о выполнении C от параметра.

Если вы используете PHP, вы можете использовать MySQLi делать мульти заявления (я думаю, что РНР поставляется с 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();

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

Ответил 06/03/2011 d 22:32
источник пользователем

голоса
2

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

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

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

Ответил 02/01/2013 d 20:48
источник пользователем

голоса
2

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

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: Если значения вы обновляете не приходят откуда-то в базе данных, вам необходимо оформить несколько запросов обновления.

Ответил 06/08/2008 d 15:20
источник пользователем

голоса
1

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

В PHP используется multi_queryметод Mysqli экземпляра.

Из инструкции PHP

MySQL необязательно позволяет иметь несколько операторов в одной строке заявления. Отправка нескольких операторов одновременно уменьшает клиент-сервер кругорейсов но требует специальной обработки.

Вот результат по сравнению с другими 3 методов обновления 30000 сырыми. Код можно найти здесь , который основан на ответ от @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а затем перезапустить туздЫ.

Ответил 05/07/2017 d 16:23
источник пользователем

голоса
-1

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

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

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

  • Идентификатор должен быть первичным уникальным ключом
  • если вы используете внешние ключи для ссылки на таблицу, ЗАМЕНА удалений затем вставляет, так что это может привести к ошибке
Ответил 15/03/2016 d 16:59
источник пользователем

голоса
-3

Да ..Это можно с помощью INSERT IGNORE ON KEY UPDATE DUPLICATE SQL Statement .. синтаксис: INSERT IGNORE INTO table_name (а, б, в) VALUES (1,2,3), (4,5,6) ON KEY UPDATE DUPLICATE а = ЗНАЧЕНИЕ (а), B = ЗНАЧЕНИЯ (б), с = ЗНАЧЕНИЯ (с)

Ответил 09/08/2014 d 11:16
источник пользователем

голоса
-3

Ниже будут обновлены все строки в одной таблице

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'
Ответил 06/08/2008 d 15:18
источник пользователем

голоса
-4

С 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();
Ответил 29/09/2015 d 22:11
источник пользователем

голоса
-5
UPDATE tableName SET col1='000' WHERE id='3' OR id='5'

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

Ответил 06/08/2008 d 15:22
источник пользователем

голоса
-7
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);

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

Ответил 28/08/2013 d 16:40
источник пользователем

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more