Множественные обновления в MySQL

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'

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

Да, это возможно - вы можете использовать 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 для обновляемых столбцов. Это выглядит некрасиво, но должно работать.

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

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

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

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

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

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

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

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

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;

Да .. это возможно с помощью инструкции sql INSERT ON DUPLICATE KEY UPDATE .. синтаксис: INSERT INTO имя_таблицы (a, b, c) VALUES (1,2,3), (4,5,6) ПРИ ДВОЙНОМ ОБНОВЛЕНИИ КЛЮЧА a = ЗНАЧЕНИЯ (a), b = ЗНАЧЕНИЯ (b), c = ЗНАЧЕНИЯ (c)

использовать

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

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

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

Все следующее применимо к InnoDB.

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

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

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

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

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

Хуже того, метод CASE был на 41,1x медленнее, чем метод INSERT с 30 000 записей (в 6,1 раза медленнее, чем TRANSACTION). И 75x медленнее в 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.

<?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 optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Вот результат по сравнению с другими 3 методами в обновлении 30,000 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.

А теперь легкий путь

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 тыс. Строк за раз.