如何实现用户偏好的记录排序？避免移动时批量更新的方案

当需要在SQL表中支持用户自定义排序，又不想在移动记录时批量更新中间条目，以下几种方案可以解决这个问题：

一、使用浮点数字段替代整数排序列 #

不再用连续的整数（如1、2、3...）作为排序值，而是给每条记录分配间隔较大的浮点数值（初始可设为10、20、30...）。

• 移动记录时：只需要将目标记录的排序值设为目标位置前后两条记录排序值的中间值即可。比如要把排序值为500的记录移到排序值100和110之间，直接把它的排序值改成105，无需修改任何中间记录。
• 间隙不足时：当两条记录的排序值差距过小（比如小于1），可以后台批量重新分配排序值——按当前顺序给每条记录重新赋值为row_number() * 10，重新拉开间隙，这个操作可以定时执行，或者在检测到间隙不足时触发。

示例SQL：

-- 移动记录时的更新语句
UPDATE your_table SET sort_order = 105 WHERE id = 123;

-- 查询排序后的记录
SELECT * FROM your_table ORDER BY sort_order;

二、采用邻接列表（链表）结构 #

给表添加两个字段prev_id和next_id，分别存储当前记录的上一条和下一条记录ID，形成链表结构。

• 移动记录时：仅需修改4条记录的指针（移到开头/结尾时只需修改2条）：
  1. 更新原位置前后记录的指针，跳过被移动的记录；
  2. 更新目标位置前后记录的指针，插入被移动的记录。
• 查询排序结果：通过递归CTE（公共表表达式）遍历链表，得到有序的记录列表。

示例SQL（以PostgreSQL为例）：

-- 移动记录的更新逻辑（假设将记录A(id=123)移到D(id=45)和E(id=67)之间）
-- 1. 处理原位置的前后记录
UPDATE your_table SET next_id = (SELECT next_id FROM your_table WHERE id=123) WHERE id = (SELECT prev_id FROM your_table WHERE id=123);
UPDATE your_table SET prev_id = (SELECT prev_id FROM your_table WHERE id=123) WHERE id = (SELECT next_id FROM your_table WHERE id=123);

-- 2. 处理目标位置的插入
UPDATE your_table SET next_id = 123 WHERE id=45;
UPDATE your_table SET prev_id = 123 WHERE id=67;
UPDATE your_table SET prev_id=45, next_id=67 WHERE id=123;

-- 递归查询排序后的记录
WITH RECURSIVE sorted_records AS (
    SELECT id, content, next_id
    FROM your_table
    WHERE prev_id IS NULL  -- 定位排序的第一条记录
    UNION ALL
    SELECT t.id, t.content, t.next_id
    FROM your_table t
    JOIN sorted_records sr ON t.prev_id = sr.id
)
SELECT id, content FROM sorted_records;

三、使用独立排序表+间隙填充 #

创建一个单独的排序表（如record_sort），包含record_id和rank_value两个字段，主表和排序表通过record_id关联。

• 初始设置：给rank_value分配间隔较大的整数（如100、200、300...），预留足够的间隙。
• 移动记录时：直接修改目标记录的rank_value为目标位置前后值的中间整数，比如在100和200之间设为150，无需修改其他记录。
• 间隙不足时：重新生成rank_value，按当前排序顺序给每条记录赋值为row_number() * 100，重新拉开间隙。

示例SQL：

-- 移动记录的更新
UPDATE record_sort SET rank_value = 150 WHERE record_id = 123;

-- 查询排序后的主表记录
SELECT t.*
FROM your_table t
JOIN record_sort rs ON t.id = rs.record_id
ORDER BY rs.rank_value;

各方案对比 #

• 浮点数字段：实现简单，查询高效，但需注意浮点精度问题，间隙不足时需重新分配值。
• 邻接列表：更新操作极高效（仅修改少量记录），但查询依赖递归，复杂度稍高，适合频繁调整排序的场景。
• 独立排序表：分离排序逻辑和主数据，灵活性高，适合主表结构复杂的场景。

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