mysql过程在更新前一行中的数字引用时进行更新

Question

有一张这样的桌子

 ______________________
| id | 标题| 订购|
| ---------------------- |
| 1 | test1 | 1 |
| ----- | -------- | ------- |
| 2 | test2 | 2 |
| ----- | -------- | ------- |
| 3 | test3 | 3 |
| ----- | -------- | ------- |
| 4 | test4 | 4 |
'----------------------'

当我在mysql shell中引入对行的单个更新时

 $ sql> UPDATE`table` SET order = 1 WHERE id = 3;

然后过程或方法在更新较低的值之前重新采样顺序列中的订单列，以使其顺序更新

 ______________________
| id | 标题| 订购|
| ---------------------- |
| 1 | test1 | 2 |
| ----- | -------- | ------- |
| 2 | test2 | 3 |
| ----- | -------- | ------- |
| 3 | test3 | 1 |
| ----- | -------- | ------- |
| 4 | test4 | 4 |
'----------------------'

任何帮助，将不胜感激，谢谢！

Answer 1

我认为有两种情况需要考虑：

1. 移动一行，使其在顺序中更早出现。
2. 移动一行，以便在以后的排序中出现。

无论哪种方式都是不平凡的。目前尚不清楚“订单”列是否存在唯一约束；最终结果当然应该具有唯一的顺序。

符号：

• “开”指的是旧值中值为“ order = n”的行
• “ Nn”是指新值中具有“ order = 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;

给定整个UPDATE的WHERE子句，您可以删除CASE中的第二个WHEN并将其替换为简单的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语句时，唯一性约束（如果有）在每行更改时均不强制执行-
仅在语句完成时执行。这是必需的，因为您实际上无法控制行的处理顺序。我知道DBMS会在哪里引起麻烦；我知道别人不会。

所有这些都可以在一个SQL语句中完成-但您确实希望存储过程对该语句的参数进行分类。我使用IBM Informix Dynamic Server（在MacOSX 10.6.2上为11.50.FC6），它是在语句末尾对“order”列实施唯一约束的DBMS之一。我在没有UNIQUE约束的情况下进行了SQL的开发；当然也可以。（是的，IDS允许您回滚CREATE
TABLE和CREATE PROCEDURE之类的DDL语句。您说什么？您的DBMS没有？太古怪了！）

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(order + v_inc,v_gap)‘。我没有检查这是否适用于负数。

读者可以自己练习如何适应MySQL或其他DBMS。