我有一个数据库表,维护一些信息,并需要保留订单.基本上如果我列出了元素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
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
它出现在它之后.该表预计至少具有大量的行(大小为100,000),并且在每次单个插入操作时简单地更新每隔一行(因此锁定表)根本不可行.
在这种情况下,什么是更好的推荐策略？
最佳答案
如果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
但是有一个问题,如果你继续在同一区域插入数字,一些订单号可能会导致你选择的数据类型的精度太接近,足够接近以至于不能彼此区分.
为避免这种情况,您的插入程序必须检查两个现有订单号是否过于接近.在这种情况下,它可以重新分配其他附近行的一些订单号,“拉伸”上方和下方的订单号以“创建空间”以获得新值.
您还可以定期运行“清理”过程,并在表的整个或大部分中进行“拉伸”.