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

Ответ 1

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

"implode" в конце просто дает мне свой ID-список для IN(). Это прекрасно работает для меня.

Ответ 2

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

-- Move #10 down (i.e. 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.

Ответ 3

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

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

Ответ 4

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

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

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

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

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

Ответ 5

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

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

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

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

Ответ 6

Если вам нужно перетащить строки, это хорошая реализация для 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:

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

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

Ответ 7

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

Здесь улучшенная версия версии 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); 
}

Ответ 8

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

Ответ 9

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

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

Ответ 10

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

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

Пример: позиция 10 перемещается вниз с двух до 12-го

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]

Ответ 11

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

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

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