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

ответ soulmerge заставлял меня думать, и я думаю, что это лучшее решение. Что вам нужно сделать, это выбрать строки с идентификатором, используя IN (), а затем использовать CASE для установки значения.

 UPDATE mytable SET display_order = CASE id WHEN 10 THEN 1 WHEN 23 THEN 2 WHEN 4 THEN 3 END CASE WHERE id IN (10, 23, 4) 

Мне нужно это в моем текущем приложении. В PHP я получаю сериализованный (и упорядоченный) набор идентификаторов из встроенной функции сортировки jQuery UI. Итак, массив выглядит так:

 $new_order = array(4, 2, 99, 15, 32); // etc 

Чтобы сгенерировать один запрос на обновление MySQL, я делаю следующее:

 $query = "UPDATE mytable SET display_order = (CASE id "; foreach($new_order as $sort => $id) { $query .= " WHEN {$id} THEN {$sort}"; } $query .= " END CASE) WHERE id IN (" . implode(",", $new_order) . ")"; 

«Взрыв» в конце просто дает мне мой список идентификаторов для IN (). Это прекрасно работает для меня.

Сначала вы должны убедиться, что столбец не имеет индекса UNIQUE, иначе mysql сообщит вам, что ограничение прерывается во время запроса. После этого вы можете делать такие вещи, как:

 -- Move #10 down (ie swap #10 and #11) UPDATE mytable SET display_order = CASE display_order WHEN 10 THEN 11 WHEN 11 THEN 10 END CASE WHERE display_order BETWEEN 10 AND 11; -- Move #4 to #10 UPDATE mytable SET display_order CASE display_order WHEN 4 THEN 10 ELSE display_order - 1 END CASE WHERE display_order BETWEEN 4 AND 10; 

Но вы должны убедиться, что делаете что-то одним шагом. замена в два этапа приведет к сломанной нумерации, если не используется идентификаторы. то есть:

 -- Swap in two steps will not work as demostrated here: UPDATE mytable SET display_order = 10 WHERE display_order = 11; -- Now you have two entries with display_order = 10 UPDATE mytable SET display_order = 11 WHERE display_order = 10; -- Now you have two entries with display_order = 11 (both have been changed) 

И вот ссылка на оператор CASE mysql .

Немного поздно, но это может быть полезно кому-то еще:

 UPDATE mytable SET display_order = FIND_IN_SET(rowId, '1,9,2,6,23') WHERE rowId in (1,9,2,6,23) 

Вы могли бы попытаться включить его в несколько утверждений, я не думаю, что это возможно в одном. Например, предположим, вы собираетесь обновить 10-ю строку. Вы хотите, чтобы каждая запись после 10 была набита.

 UPDATE table SET col=col+1 WHERE col > 10 UPDATE table SET col=10 WHERE id = X ... 

Но действительно сложно кататься во всей логике. Потому что некоторые записи, возможно, нуждаются в декременте и т. Д. Вы хотите избежать дубликатов и т. Д.

Подумайте об этом с точки зрения времени разработчика и выгоды.

Потому что, даже если кто-то сортирует это один раз в день, накладные расходы минимальны по сравнению с фиксацией в хранимой процедуре или псевдооптимизацией этой функции, поэтому вы не запускаете 20 запросов. Если это не выполняется 100 раз в день, 20 запросов отлично.

Я думаю об этой проблеме, и решение, которое я придумал, имеет десятичное число в качестве порядка и изменение номера элемента для числа между следующим и предыдущим пунктом

 Order Item ----- ---- 1 Original Item 1 2 Original Item 2 3 Original Item 3 4 Original Item 4 5 Original Item 5 

Если вы измените позицию 4 на вторую позицию, вы получите:

 Order Item ----- ---- 1 Original Item 1 1.5 Original Item 4 2 Original Item 2 3 Original Item 3 5 Original Item 5 

Если вы измените позицию 3 на третью позицию, вы получите:

 Order Item ----- ---- 1 Original Item 1 1.5 Original Item 4 1.75 Original Item 3 2 Original Item 2 5 Original Item 5 

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

Таким образом вам нужно только обновить строку, подлежащую повторной закачке.

Если вам нужно перетащить строки, это хорошая реализация для linked list .

Наличие упорядоченных строк со linked list означает, что вы будете обновлять не более 3 строк за раз – даже если вы переместите весь блок (пока он смежный).

Создайте таблицу ваших строк следующим образом:

 CREATE TABLE t_list ( id INT NOT NULL PRIMARY KEY, parent INT NOT NULL, value VARCHAR(50) NOT NULL, /* Don't forget to create an index on PARENT */ KEY ix_list_parent ON (parent) ) id parent value 1 0 Value1 2 3 Value2 3 4 Value3 4 1 Value4 

и используйте этот запрос MySQL для выбора строк в порядке:

 SELECT @r := ( SELECT id FROM t_list WHERE parent = @r ) AS id FROM ( SELECT @r := 0 ) vars, t_list 

Это пересечет ваш связанный список и вернет упорядоченные элементы:

 id parent value 1 0 Value1 4 1 Value4 3 4 Value3 2 3 Value2 

Чтобы переместить строку, вам необходимо обновить parent элемент строки, parent элемент ее текущего дочернего элемента и parent элемент строки, которую вы вставляете ранее.

См. Эту серию статей в своем блоге о том, как сделать это эффективно в MySQL :

• Сортировка списков – как выбрать упорядоченные элементы из связанного списка
• Сортировка списков: перемещение элементов – как перемещать один элемент
• Сортировка списков: добавление элементов – как вставить один элемент
• Сортировка списков: удаление элементов – удаление одного элемента
• Сортировка списков: движущиеся блоки – как перемещать смежный блок
• Сортировка списков: удаление блоков – как удалить смежный блок

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

Это отличный способ сортировки элементов в базе данных. Именно то, что я искал;)

Вот улучшенная версия версии Jamon Holmgren. Эта функция полностью динамична:

 function reOrderRows($tablename, $ordercol, $idsarray){ $query = "UPDATE $tablename SET $ordercol = (CASE $ordercol "; foreach($idsarray as $prev => $new) { $query .= " WHEN $prev THEN $new\n"; } $query .= " END) WHERE $ordercol IN (" . implode(",", array_keys($idsarray)) . ")"; mysql_query($query); } 

Это предполагает, что у вас есть столбец $ ordercol в вашей таблице $ tablename только для целей заказа.

$ Idsarray – это массив в массиве format ("current_order_num" => "updated_order_num").

Поэтому, если вы просто хотите обменять две строки в своей таблице (представьте, что у вас есть, например, значки «вверх» и «вниз» на вашей веб-странице), вы можете использовать эту функцию поверх предыдущей:

 function swapRows($tablename, $ordercol, $firstid, $secondid){ $swaparray = array("$firstid" => "$secondid", "$secondid" => "$firstid"); reOrderRows($tablename, $ordercol, $swaparray); } 

Вы можете создать временную таблицу и заполнить первичный ключ строк, которые вы хотите изменить, и новые значения display_order для этих строк, а затем использовать версию таблицы UPDATE с несколькими таблицами для обновления таблицы за один раз. Я бы не стал предполагать, что это быстрее, но не без тестирования обоих подходов.

Добавьте идентификатор (или другой ключ) в таблицу и обновите, где id (или ключ) = id (или ключ) измененной строки.

Конечно, вам нужно убедиться, что либо нет дублирующихся значений display_order, либо что вы в порядке со связками в display_order, отображаемом в любом порядке, или вы вводите второй тай-брейк в список по списку ,

Вы можете удалить повторную вставку всех строк, которые будут выполнять всю операцию всего за два запроса (или три, если вам нужно выбрать все данные). Я не буду рассчитывать на то, что это будет быстрее, и вам придется делать это внутри транзакции, иначе вы будете слишком долго заниматься резервным копированием. Это также может привести к фрагментации таблиц.

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

Например, позиция 10 перемещается вниз с двух до двенадцати

 UPDATE table SET display_order = display_order -1 WHERE display_order BETWEEN 10 AND 12 UPDATE table SET display_order = 12 WHERE row_id = [id of what was row 10] 

Соберите новый порядок во временной переменной и поместите кнопку «Сохранить этот заказ» в область администратора. Затем сохраните порядок для строк с одним раундом.

Вы получите лучшее время отклика, отмененные изменения, меньшее количество измененных строк (потому что на низком уровне в dbms практически не было доступных обновлений, но сохраните новый экземпляр всей строки и удалите старый).

В конце концов, это будет более дешевое решение для полного переупорядочения и сэкономит некоторое кодирование при оптимизации обновлений.