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

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

  1. <form action="handler.php"  method="post" name="multiple_update">
  2.   <p>
  3.     <label>Apple iPhone 7:</label><!-- название товара -->
  4.     <input type="hidden" name="item_id[]" value="7"><!-- ID товара в базе -->
  5.     <input type="text" name="item_price[]" value="250"><!-- текущая цена товара -->
  6.   </p>
  7.   <p>
  8.     <label>Samsung Galaxy S10:</label>
  9.     <input type="hidden" name="item_id[]" value="3">
  10.     <input type="text" name="item_price[]" value="300">
  11.   </p>
  12.   <p>
  13.     <label>Nokia Lumia 1000:</label>
  14.     <input type="hidden" name="item_id[]" value="5">
  15.     <input type="text" name="item_price[]" value="150">
  16.   </p>
  17.   <p>
  18.     <input type="submit" value="Сохранить">
  19.   </p>
  20. </form>

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

  1. Array (
  2.   [item_id] => Array (
  3.     [0] => 7
  4.     [1] => 3
  5.     [2] => 5
  6.   )
  7.   [item_price] => Array (
  8.     [0] => 250
  9.     [1] => 300
  10.     [2] => 150
  11.   )
  12. )

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

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

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

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

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

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

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

  1. if(isset($_POST['item_id'], $_POST['item_price'])){
  2.   try {
  3.     require 'db.php'; // Подключение к БД
  4.    
  5.     $item_id = $_POST['item_id'];
  6.     $item_price = $_POST['item_price'];
  7.    
  8.     // Объединяем массивы данных
  9.     $allData = array_map( null, $item_id, $item_price );
  10.    
  11.     // Подготавливаем строку запроса
  12.     $query = "INSERT INTO `products` (`prod_id`,`price`) VALUES ";
  13.     $query .= substr(str_repeat('(?, ?),', count($allData)), 0, -1);
  14.     $query .= " ON DUPLICATE KEY UPDATE `price` = VALUES(`price`)";
  15.     //echo $query; // можно вывести строку запроса для контроля
  16.    
  17.     $dbh = DB::getInstance();
  18.     $sth = $dbh->prepare($query);
  19.    
  20.     // создаём одномерный массив всех значений из двумерного (см. ниже²)
  21.     $iterator = new RecursiveIteratorIterator(new RecursiveArrayIterator($allData));
  22.     $allData = iterator_to_array($iterator, false);
  23.    
  24.     // Привязываем параметры запроса к переменным
  25.     foreach($allData as $key => &$val){
  26.       $sth->bindParam(($key+1), $val);
  27.     }
  28.    
  29.     // выполняем запрос
  30.     $sth->execute();
  31.     echo ($sth->rowCount() > 0 ? 'Данные успешно обновлены!' : 'Не произведено ни одной замены'),
  32.       '<br><a href="./">Вернутся</a> на страницу редактирования';
  33.   } catch(PDOException $e){
  34.     echo 'Ошибка при выполнении запроса: ', $e->getMessage();
  35.   }
  36. }

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


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

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 и т.д. И вот для того, чтобы эти данные можно было использовать без риска - их и фильтруют. Кстати, в некоторых случаях, можно обойтись без них. К примеру, если вы знаете, что будет передано какое-то числовое значение, то можно обойтись обычным явным приведением к типу.
  1. $id = (int)$_POST['id'];
  2. // или
  3. $id = intval($_POST['id']);
  4. // аналогично и с числами с плавающей точкой
  5. $price = (float)$_POST['price'];
  6. $price = floatval($_POST['price']);
yurikfirst 28.08.2016 23:30
  1. $id = (int)$_POST['id'];
  2. // или
  3. $id = intval($_POST['id']);
  4. // аналогично и с числами с плавающей точкой
  5. $price = (float)$_POST['price'];
  6. $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