Обновление нескольких записей одним запросом в MySQL

Начну сразу с абстрактного примера. Представьте такую ситуацию: у вас интернет магазин и одним из функционалов в админке, вы сделали возможность изменить вручную цены сразу нескольким товарам. Мы рассматриваем именно ручное изменение, а не по какой-нибудь формуле, например, увеличить цену на N процентов. Как бы вы реализовали это обновление? Как обновляли бы поля цен для каждого определенного товара? Первое, что пришло бы на ум новичку (да и не только) - это запросы в цикле. И в таком случае, количество запросов к базе было бы равным количеству обновляемых товаров. Ну, иногда и без такой конструкции не обойтись. Но если ситуация позволяет, то обновить несколько записей можно одним запросом. И сейчас, я покажу, как это можно сделать.
Продолжим эксперимент на вышеуказанном примере. Выводим из БД нужные данные, где нам необходимы названия товаров, их текущие цены и их ID. Эти все значения распределяем в форме:

<form action="handler.php"  method="post" name="multiple_update">
  <p>
    <label>Apple iPhone 7:</label><!-- название товара -->
    <input type="hidden" name="item_id[]" value="7"><!-- ID товара в базе -->
    <input type="text" name="item_price[]" value="250"><!-- текущая цена товара -->
  </p>
  <p>
    <label>Samsung Galaxy S10:</label>
    <input type="hidden" name="item_id[]" value="3">
    <input type="text" name="item_price[]" value="300">
  </p>
  <p>
    <label>Nokia Lumia 1000:</label>
    <input type="hidden" name="item_id[]" value="5">
    <input type="text" name="item_price[]" value="150">
  </p>
  <p>
    <input type="submit" value="Сохранить">
  </p>
</form>

Хочу обратить внимание на одинаковые названия и квадратные скобки в именах полей! Вы, конечно же, знаете, но для тех, кто не в курсе, поясню: квадратные скобки указывают, что значения передаются массивом, а ключами будут являться значения до скобок. То есть, отправив форму и выведя результат на экран, мы получим следующую картину:

Array (
  [item_id] => Array (
    [0] => 7
    [1] => 3
    [2] => 5
  )
  [item_price] => Array (
    [0] => 250
    [1] => 300
    [2] => 150
  )
)

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

if(isset($_POST['item_id'], $_POST['item_price'])){
  $item_id = $_POST['item_id'];
  $item_price = $_POST['item_price'];
  foreach($_POST['item_id'] as $k => $v){ // (см. ниже¹)
    // Выполнять запросы не будем, а просто выведем их для наглядности
    echo "UPDATE `products` SET `price` = ".$item_price[$k]." WHERE `prod_id` = ".$v;
  }
}

// (прим.¹)кстати, если у вас только два значения, то цикл можно организовать таким образом,
foreach(array_combine($_POST['item_id'],$_POST['item_price']) as $val_id => $val_price){
  echo "UPDATE `products` SET `price` = ".$val_price." WHERE `prod_id` = ".$val_id;
}
// если же, допустим, вы будете обновлять еще и названия товаров, то такой способ не подойдёт

Смысл, я думаю, что понятен. Обновляем три товара - выполняем три отдельных запроса. Ладно, если три, но, а если пару десятков? Как-то не комильфо ;) Так вот, для того, чтоб построить для такой операции один единственный запрос, нам поможет, как бы это странно не выглядело, оператор INSERT. В MySQL для этого оператора предусмотрен синтаксис: "INSERT ... ON DUPLICATE KEY UPDATE ..." Если описать его простым языком, то получится что-то вроде: "Добавить новую запись в таблицу, но если запись с таким ключом (уникальным или первичным) существует, то просто обновить в ней данные." Особенность с ключами нужно учитывать, иначе фокус не удастся... То есть, в запросе должно фигурировать одно или несколько полей с уникальным (UNIQUE) или первичным ключом (PRIMARY KEY). Тогда при выполнении запроса и совпадении значений в таких полях, вставка новой записи будет проигнорирована, а записи остальных полей обновлены данными. В нашем случае, поле "prod_id" - это ключ первичный, поэтому без опасений перейдём к генерированию запроса.

if(isset($_POST['item_id'], $_POST['item_price'])){
  $item_id = $_POST['item_id'];
  $item_price = $_POST['item_price'];
  // Объединяем массивы
  $allData = array_map( null, $item_id, $item_price );
  // Генерируем запрос
  $query = "INSERT INTO `products` (`prod_id`,`price`) VALUES ";
  $params = array();
  foreach($allData as $val){
    $params[] = '('.(int)$val[0] .','.(float)$val[1].')';
  }
  $query .= implode(',', $params) . " ON DUPLICATE KEY UPDATE `price` = VALUES(`price`)";
  // Запрос готов.
  echo $query; // можно вывести его на экран для проверки
  // Остается его выполнить
}

Тут важно следить за порядком! Если при объединении массивов данных, мы ставим первым (после "null") ID товара, то учитываем, что в цикле $val[0] - это тоже айдишник товара и в перечислении полей - "INSERT INTO `products` (`prod_id`,`price`)" - первое должно быть соответствующее. И так далее для остальных данных. По поводу синтаксиса функции array_map() с использование первого параметра "null", дабы не раздувать тему, предлагаю почитать в документации.

Данный пример уже готов к использованию, если вы всё ещё пользуетесь устаревшим расширением mysql_*, но если вы уже счастливчик, который перешёл на PDO или MySQLi, то процесс подготовки данных и запроса, нужно адаптировать. Тут всё зависит от ваших личных предпочтений и особенностей работы с БД, но в качестве примера, покажу, как это можно сделать для PDO и запроса с неименованными плейсхолдерами.

if(isset($_POST['item_id'], $_POST['item_price'])){
  try {
    require 'db.php'; // Подключение к БД
    
    $item_id = $_POST['item_id'];
    $item_price = $_POST['item_price'];
    
    // Объединяем массивы данных
    $allData = array_map( null, $item_id, $item_price );
    
    // Подготавливаем строку запроса
    $query = "INSERT INTO `products` (`prod_id`,`price`) VALUES ";
    $query .= substr(str_repeat('(?, ?),', count($allData)), 0, -1);
    $query .= " ON DUPLICATE KEY UPDATE `price` = VALUES(`price`)";
    //echo $query; // можно вывести строку запроса для контроля
    
    $dbh = DB::getInstance();
    $sth = $dbh->prepare($query);
    
    // создаём одномерный массив всех значений из двумерного (см. ниже²)
    $iterator = new RecursiveIteratorIterator(new RecursiveArrayIterator($allData));
    $allData = iterator_to_array($iterator, false);
    
    // Привязываем параметры запроса к переменным
    foreach($allData as $key => &$val){
      $sth->bindParam(($key+1), $val);
    }
    
    // выполняем запрос
    $sth->execute();
    echo ($sth->rowCount() > 0 ? 'Данные успешно обновлены!' : 'Не произведено ни одной замены'),
      '<br><a href="./">Вернутся</a> на страницу редактирования';
  } catch(PDOException $e){
    echo 'Ошибка при выполнении запроса: ', $e->getMessage();
  }
}

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


Не бойтесь экспериментировать. Это касается как неординарных запросов, так и новых технологий в области работы с БД. Только перед любыми экспериментами, не забывайте создавать дамп базы! ;)

Incode Pro logo

12 комментариев

Гость 06.01.2015 23:51
Фильтрация переменных, входных данных?
Incode 07.01.2015 21:36
Фильтрация переменных, входных данных?
Замечание справедливое, но только в контексте отдельно взятой статьи. Речь в данном случае не шла о безопасности, а о схематической реализации отдельно взятой задачи. Если в каждой из статей добавлять рекомендации о том, как лучше избежать SQL-инъекций и закрытии прочих дыр, то статьи будут разрастаться до неприглядных размеров. Однако, чтоб не оставлять без внимания эту неотъемлемую часть обработки данных, дополню уже в комментарии:

! Не рекомендуется обращаться непосредственно к суперглобальным массивам: $_POST, $_GET, $_COOKIE и т.д. Вместо этого желательно использовать функции фильтрации, например, filter_input(), filter_input_array() и др.
yurikfirst 28.08.2016 13:38
! Не рекомендуется обращаться непосредственно к суперглобальным массивам: $_POST, $_GET, $_COOKIE и т.д. Вместо этого желательно использовать функции фильтрации, например, filter_input(), filter_input_array() и др.

Здравствуйте, а как использовать эти функции в данном примере в PDO? Или в MySQLi?
Incode 28.08.2016 17:11
как использовать эти функции в данном примере в PDO? Или в MySQLi?
Если бы вы прочитали не первые пару предложений, а дочитали бы до конца, то заметили бы пример с использование PDO. Я с MySQLi не работаю, но принцип подготавливаемых запросов там идентичен с PDO.
yurikfirst 28.08.2016 17:23
Спасибо, PDO пример видел, я про эти функции.. безопасность
filter_input(), filter_input_array() и др.
yurikfirst 28.08.2016 19:26
Статья, кстати отличная, спасибо, помогла!
Incode 28.08.2016 22:02
я про эти функции.. безопасность
@yurikfirst, эти функции никакого отношения ни к PDO, ни к MySQLi не имеют. Это функции, которые помогают обезопасить/валидировать данные, приходящие от клиента. Когда приходит запрос с данными на сервер, совершенно не обязательно, что они будут использованы в запросе, а, например, для для каких-то расчетов или работы с файлами, API и т.д. И вот для того, чтобы эти данные можно было использовать без риска - их и фильтруют. Кстати, в некоторых случаях, можно обойтись без них. К примеру, если вы знаете, что будет передано какое-то числовое значение, то можно обойтись обычным явным приведением к типу.
$id = (int)$_POST['id'];
// или 
$id = intval($_POST['id']);
// аналогично и с числами с плавающей точкой
$price = (float)$_POST['price'];
$price = floatval($_POST['price']);
yurikfirst 28.08.2016 23:30
$id = (int)$_POST['id'];
// или 
$id = intval($_POST['id']);
// аналогично и с числами с плавающей точкой
$price = (float)$_POST['price'];
$price = floatval($_POST['price']);

Спасибо, теперь ясно!
Гость 23.07.2018 18:28
Огромное спасибо за такую толковую статью. Очень помогла.
Гость 28.10.2018 22:48
Спасибо. Добавил страничку в избранное!
Гость 19.12.2018 05:38
вы в первом примере через foreach прогоняете item_id[] и уже в теле цикла присутствует
item_price[$k]....откуда он взялся
Гость 18.05.2019 06:57
Статься просто отличная) Спасибо!!!
Ваш комментарий:
X