数据库Schema设计：行程照片自定义排序实现方案咨询

你当前设计的连续整数order_no需要全量更新的核心原因，是排序值没有预留可插入的缓冲空间，只要调整字段设计逻辑，完全可以避免每次调整顺序都更新全量关联行：

方案1：带间隔的非连续整数编号 #

• 给每个行程下的照片分配排序值时，刻意留足缓冲间隔，比如初始添加的4张照片直接分配1000、2000、3000、4000作为order_no，不用从1开始做无间隔的连续编号
• 调整顺序时，只要在两个相邻照片的order_no之间取中间值即可：比如要把原来排最后的photo4（order_no=4000）移到最前面，因为当前最靠前的photo1的order_no=1000，直接给photo4赋值500即可，全程不需要修改其他3张照片的任何字段
• 只有当相邻两个排序值的间隔完全用完（比如两个值之间已经插满了连续的数），才需要针对这两个值覆盖的极小范围做一次批量重排，无需更新整个行程下的所有照片
• 配套要求：给(trip_id, order_no)创建联合唯一索引，避免同行程下出现重复排序值，导致排序结果不稳定

方案2：浮点型排序值 #

• 把order_no字段类型设为双精度浮点数，初始赋值可以直接取1.0、2.0、3.0、4.0
• 任意位置插入新值时，直接取相邻两个浮点数的中间值即可：比如把排最后的照片移到最前面，直接赋值0.5，完全不需要修改其他行
• 双精度浮点数的精度足够支撑单行程下数万次连续插入/调整，就算真到精度临界值，做一次全量重排的成本也远低于日常调整时的全量更新

方案3：邻接链表式排序存储 #

• 放弃数字排序值，在Photos表新增两个字段：prev_photo_id、next_photo_id，分别存储当前照片在排序链路中的前一个、后一个照片ID
• 调整位置时仅需要修改链路断裂位置的4个指针：比如把排最后的photo4移到最前面，只需要完成三类更新：
  • 原链路头photo1的prev_photo_id从NULL改为photo4的ID
  • photo4的next_photo_id从NULL改为原photo1的ID，prev_photo_id从原关联的photo3改为NULL
  • 原链路中photo3的next_photo_id从指向photo4改为NULL
• 排序展示时从链路起点（prev_photo_id为NULL的节点）顺着next_photo_id遍历即可，全程不需要批量更新多行
• 注意：所有指针修改必须放在数据库事务里，避免出现断链、环链的问题；如果需要支持按排序分页查询，这种方案的查询成本会比数字排序高

内容的提问来源于stack exchange，提问作者user15608655