我有一个数据库表，它维护一些信息，并且是保持秩序所必需的。本质上，如果我列出了元素1到5，并且我想添加一个新元素，那么它可以插入到现有行中的任何位置，要么是最后一行，要么是5行之后，要么是1之前的开始，要么是中间的某个位置，比如3行之后。有没有一种方法可以使用MySQL INSERT语句并指定应该在哪一行之后插入索引来实现这一点？
我想不会。因此，我的策略是创建另一列"order_number"，它基本上记录元素的顺序。例如，如果记录表有主键(record_id)和并列的order_number，则它看起来如下：
record_id order_number
1 1
2 2
3 3
4 4
5 5
要在第3行之后向该行添加一个新元素，生成的结束表将如下所示：
record_id order_number
1 1
2 2
3 3
**6** **4** <------ added row
4 **5** <-- changed order_number
5 **6** <-- changed order_number
在这种情况下，我可以通过简单地选择我想要的数据并提供order by order_number asc子句来清楚地实现我想要的顺序。
但是，正如您所看到的，要执行简单的Insert，它需要我更新每隔一行的order_number该表预计至少有大量的行(数量级为100000)，在每次插入操作时简单地每隔一行更新一次(因此锁定表)根本不可行。
在这种情况下，什么是更好的推荐策略？
如果不显示order_number，而仅用于排序，我建议您使用十进制数据类型，而不是整数。这样，当您必须在两个现有行之间插入一行时，您可以将两个现有订单号的平均值设置为order_number。
在您的示例中：
record_id order_number
1 1.0
2 2.0
3 3.0
**6** 3.5 <---- added row
4 4.0 <-- no change
5 5.0 <-- no change
但是，有一个问题是，如果您一直在同一区域插入数字，某些顺序号可能会导致与您选择的数据类型的精度过于接近，接近到无法相互区分。
为了避免这种情况，插入过程必须检查两个现有订单号是否太近。在这种情况下，它可以重新分配附近其他行的一些订单号，"拉伸"上面和下面的订单号，为新值"腾出空间"。
您还可以有一个周期性运行的"清理"过程，并在整个或大部分表中进行"拉伸"。
我找到了一个类似问题的答案：https://stackoverflow.com/a/6333717/1010050
总之，它会将所有记录ID增加到您将要添加的记录ID以下，以保持一致性。这仍然需要更新所有的记录ID，所以它不是最有效的。与您的方法相比，它确实有好处，可以维护数据库中的物理订单，而不仅仅是像您那样的虚拟订单。
我可以想到的另一种方法是记录每个记录的子记录ID和父记录ID，而不是订单号，类似于双链接列表。在中间插入一个元素只需要更新另外两个记录，而不考虑表的大小。这与物理排序错误的解决方案有着相同的缺点，因此以有序的方式从表中读取数据的成本会更高。
例如：
record_id parent_id child_id
0 NULL 1
1 0 2
2 1 NULL
当我们在record_id = 1之后插入一条记录时，该表变为：
record_id parent_id child_id
0 NULL 1
1 0 3
2 3 NULL
3 1 2
注意ID 1和2的parent_id和child_id必须如何更改
我认为在这两种解决方案之间，最需要考虑的是什么是最常见的操作：按顺序读取值，或者在中间的某个位置写入新值。如果它正在读取，那么更新记录ID将是维护数据库物理顺序的最佳选择。如果你在写，那么你可以使用我建议的类似于双链表的方法，或者你自己的排序方法来进行优化。
问题更新后的总结：看到更新大多数记录是不可行的，那么我找到的另一个答案肯定是无效的。然而，将其视为双重链接列表的解决方案仍然是可行的。