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

Сначала убедитесь, что столбец не имеет уникального индекса, в противном случае 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 .

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

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

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

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

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

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

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

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

You could delete an re-insert all the rows - that would do the whole operation in just two queries (or three if you need to select all the data). I wouldn't count on it being faster, and you'd have to do it inside a transaction or you'll be heading for your backups before too long. It could also lead to table fragmentation.

A better option might be to record each change as a history then do something like this:

Example, position 10 is moved down two to 12th

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 обновления практически невозможны, но сохраните новый экземпляр всей строки и удалите старую).

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

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

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

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

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

Если вы измените элемент 3 на 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

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

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

If you need to drag you rows, this is a good implementation for a linked list.

Having your rows ordered with a linked list means that you will update at most 3 rows at a time -- even if you move the whole block (as long as it's contiguous).

Create a table of your rows like this:

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

and use this MySQL query to select the rows in order:

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

This will traverse your linked list and return the ordered items:

id   parent  value

1    0       Value1
4    1       Value4
3    4       Value3
2    3       Value2

To move a row, you'll need to update the parent of the row, the parent of its current child, and the parent of the row you're inserting before.

See this series of articles in my blog on how to do it efficiently in MySQL:

• Sorting lists - how to select ordered items from a linked list
• Sorting lists: moving items - how to move a single item
• Sorting lists: adding items - how to insert a single item
• Sorting lists: deleting items - how to delete a single item
• Sorting lists: moving blocks - how to move a contiguous block
• Sorting lists: deleting blocks - how to delete a contiguous block

There are lots of articles because there are some issues with row locking which should be handled slightly differently for each case.