процедура mysql для обновления числовой ссылки в предыдущих строках, когда каждый обновляется

Думаю, следует рассмотреть два случая:

1. Переместить одну строку так, чтобы она отображалась раньше в упорядочивании.
2. Переместите одну строку, чтобы она отображалась позже в порядке сортировки.

В любом случае это нетривиально. Неясно, существует ли уникальное ограничение на столбец «порядок»; конечный результат, безусловно, должен иметь уникальный порядок.

Обозначение:

• «Вкл» относится к строке со значением «порядок = n» в старых значениях
• «Nn» относится к строке со значением «порядок = n» в новых значениях

В пример (иллюстрирующий случай 1):

• O3 -> N1
• O1 -> N2
• O2 -> N3

В качестве альтернативы рассмотрим перемещение id = 2, чтобы он имел порядок = 4:

• O2 -> N4
• O3 -> N2
• O4 -> N3

Вы в основном добавляете или вычитаете одну из «других» строк, где это строки в старый порядок между старой позицией перемещенной строки и новой позицией перемещенной строки.В псевдокоде с использованием $ old и $ new для идентификации позиций до и после перемещенной строки и обработки случая 1 ($ old> $ new):

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order >= $new AND order < $old THEN order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

Соответствующий код для случая 2 ($ old < $ new):

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               WHEN order > $new AND order <= $old THEN order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

Учитывая предложение WHERE в UPDATE в целом, вы можете удалить второе WHEN в CASE и заменить его простым ELSE.

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order + 1
               END CASE
 WHERE order BETWEEN $new AND $old;

UPDATE AnonymousTable
   SET order = CASE
               WHEN order = $old THEN $new
               ELSE                   order - 1
               END CASE
 WHERE order BETWEEN $old AND $new;

Я думаю, что хранимая процедура в порядке - выбор между двумя операторами на основе входных параметров $ old, $ new. Вы могли бы что-то сделать, используя разумное сочетание выражений, таких как ' ($ old - $ new) / ABS ($ old - $ new) ' и ' MIN ($ old, $ new) 'и' MAX ($ old, $ new) ', где MIN / MAX - не агрегаты, а функции компаратора для пары значений (как в Fortran, среди других языков программирования) .

Обратите внимание, что я предполагаю, что пока выполняется один оператор SQL, ограничение уникальности (если оно есть) не применяется при изменении каждой строки - только когда оператор завершается. Это необходимо, поскольку вы не можете контролировать порядок, в котором обрабатываются строки. Я знаю СУБД, где это могло вызвать проблемы; Я знаю других, где этого не произошло бы.

Все это можно сделать в одном операторе SQL, но вы хотите, чтобы хранимая процедура сортировала параметры этого оператора. Я использую IBM Informix Dynamic Server (11.50.FC6 в MacOS X 10.6.2), и это одна из СУБД, которая применяет уникальное ограничение в столбце «порядок» в конце оператора. Я разработал SQL без ограничения UNIQUE; это, конечно, тоже сработало.(И да, IDS позволяет откатить операторы DDL, такие как CREATE TABLE и CREATE PROCEDURE. Что вы сказали? В вашей СУБД нет? Как странно!)

BEGIN WORK;
CREATE TABLE AnonymousTable
(
    id      INTEGER NOT NULL PRIMARY KEY,
    title   VARCHAR(10) NOT NULL,
    order   INTEGER NOT NULL UNIQUE
);
INSERT INTO AnonymousTable VALUES(1, 'test1', 1);
INSERT INTO AnonymousTable VALUES(2, 'test2', 2);
INSERT INTO AnonymousTable VALUES(3, 'test3', 3);
INSERT INTO AnonymousTable VALUES(4, 'test4', 4);

SELECT * FROM AnonymousTable ORDER BY order;

CREATE PROCEDURE move_old_to_new(old INTEGER, new INTEGER)
    DEFINE v_min, v_max, v_gap, v_inc INTEGER;
    IF old = new OR old IS NULL OR new IS NULL THEN
        RETURN;
    END IF;
    LET v_min = old;
    IF new < old THEN
        LET v_min = new;
    END IF;
    LET v_max = old;
    IF new > old THEN
        LET v_max = new;
    END IF;
    LET v_gap = v_max - v_min + 1;
    LET v_inc = (old - new) / (v_max - v_min);
    UPDATE AnonymousTable
       SET order = v_min + MOD(order - v_min + v_inc + v_gap, v_gap)
     WHERE order BETWEEN v_min AND v_max;
END PROCEDURE;

EXECUTE PROCEDURE move_old_to_new(3,1);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(1,3);
SELECT * FROM AnonymousTable ORDER BY order;

INSERT INTO AnonymousTable VALUES(5, 'test5', 5);
INSERT INTO AnonymousTable VALUES(6, 'test6', 6);
INSERT INTO AnonymousTable VALUES(7, 'test7', 7);
INSERT INTO AnonymousTable VALUES(8, 'test8', 8);

EXECUTE PROCEDURE move_old_to_new(3,6);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(6,3);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(7,2);
SELECT * FROM AnonymousTable ORDER BY order;
EXECUTE PROCEDURE move_old_to_new(2,7);
SELECT * FROM AnonymousTable ORDER BY order;

ROLLBACK WORK;

Пары вызовов хранимой процедуры с перевернутыми числами каждый раз восстанавливала исходный порядок. Ясно, что я мог бы переопределить переменную v_inc так, чтобы вместо ± 1 она была « LET v_inc = v_inc - v_min + v_gap; », и тогда выражение MOD было бы просто ' MOD (порядок + v_inc, v_gap) '. Я не проверял, работает ли это с отрицательными числами.

Адаптация к MySQL или другой СУБД оставлена ​​в качестве упражнения для читателя.