如何在SQL中表示并插入到有序列表中？

仉昱

2023-03-14

问题内容：

我想在SQL表中表示列表“ hi”，“ hello”，“再见”，“早安”，“ howdy”（按此顺序）：

pk | i | val
------------
1  | 0 | hi
0  | 2 | hello
2  | 3 | goodbye
3  | 4 | good day
5  | 6 | howdy

“ pk”是主键列。忽略其值。

“ i”是“索引”，用于定义“ val”列中值的顺序。它仅用于建立顺序，否则值并不重要。

我遇到的问题是在保持顺序的同时将值插入到列表中。例如，如果我想插入“嘿”，并且希望它出现在 “你好”和“再见”之间，则我必须将“再见”和“早安”的“
i”值转移（但最好不要将“你好”）为新条目腾出空间。

那么，是否有标准的SQL模式来执行移位操作，但仅移位必需的元素？（请注意，一个简单的“ UPDATE table SET i = i + 1 WHERE
i> = 3”无效，因为它违反了对“ i”的唯一性约束，并且不必要地更新了“ howdy”行。）

或者，有没有更好的方法来表示有序列表？我想您可以使“ i”为浮点值并在其中选择值，但是当不存在这样的值时，您必须进行单独的重新平衡操作。

或者，如果我要将“ i”设为varchar，是否存在一些用于在任意其他字符串之间生成字符串值的标准算法？

还是应该将其表示为链接列表？我避免这样做是因为我也希望能够执行SELECT .. ORDER BY来按顺序获取所有元素。

问题答案：

您可以通过使用级联触发器轻松地实现此目的，该触发器将与插入/更新操作中的新索引条目相等的任何“索引”条目更新为索引值+1。这将级联所有行，直到第一个间隙停止级联-
有关PostgreSQL实现，请参见此博客条目中的第二个示例。

该方法应独立于所使用的RDBMS起作用，前提是它支持在更新/插入之前
触发的触发器。如果您在代码中实现了所需的行为（增加所有后续索引值，直到遇到间隔），则基本上可以完成您的工作，但是以一种更简单，更有效的方式进行。

或者，如果您可以使用SQL Server的限制，请检查architectureid类型。虽然主要用于定义嵌套层次结构，但也可以将其用于平面html" target="_blank">排序。它有点类似于您使用浮点数的方法，因为它允许通过分配小数值来在两个位置之间插入，从而避免了更新其他条目的需要。