PostgreSQL表增删改行时基于列重排的实现及优化咨询

1. 插入指定顺序的新问题 #

如果要插入questionOrder=5的新问题，得先把现有序号≥5的记录全部加1，避免序号冲突。必须放在事务里执行，防止并发操作导致数据混乱：

BEGIN;
-- 先调整现有记录的序号
UPDATE questions 
SET questionOrder = questionOrder + 1 
WHERE questionOrder >= 5;
-- 插入新问题（替换...为你的实际字段）
INSERT INTO questions (questionOrder, ...) 
VALUES (5, ...);
COMMIT;

要是担心并发冲突，可以加行锁确保操作原子性：

BEGIN;
UPDATE questions 
SET questionOrder = questionOrder + 1 
WHERE questionOrder >= 5
FOR UPDATE;
INSERT INTO questions (questionOrder, ...)
VALUES (5, ...);
COMMIT;

2. 删除记录后重新调整顺序 #

删除记录后有两种调整方式，按需选择：

• 方式一：仅调整受影响的记录
  假设删除的是questionOrder=3的记录，把后面所有序号减1即可：

BEGIN;
DELETE FROM questions WHERE questionOrder = 3;
UPDATE questions 
SET questionOrder = questionOrder - 1 
WHERE questionOrder > 3;
COMMIT;

• 方式二：全局重新生成连续序号
  如果序号已经混乱，直接按现有顺序重新分配1、2、3...的连续序号：

WITH ordered_questions AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY questionOrder) AS new_order
    FROM questions
)
UPDATE questions q
SET questionOrder = o.new_order
FROM ordered_questions o
WHERE q.id = o.id;

3. 更合理的表结构与自动重排方案 #

方案一：触发器实现全自动调整 #

写一个触发器函数，绑定到表上后，增删改questionOrder时会自动处理序号，不用手动写SQL：
首先创建触发器函数：

CREATE OR REPLACE FUNCTION adjust_question_order()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入时调整后续序号
    IF TG_OP = 'INSERT' THEN
        UPDATE questions
        SET questionOrder = questionOrder + 1
        WHERE questionOrder >= NEW.questionOrder
        AND id != NEW.id;
    -- 更新序号时，根据前后变化调整中间记录
    ELSIF TG_OP = 'UPDATE' THEN
        IF NEW.questionOrder > OLD.questionOrder THEN
            UPDATE questions
            SET questionOrder = questionOrder - 1
            WHERE questionOrder > OLD.questionOrder
            AND questionOrder <= NEW.questionOrder
            AND id != NEW.id;
        ELSIF NEW.questionOrder < OLD.questionOrder THEN
            UPDATE questions
            SET questionOrder = questionOrder + 1
            WHERE questionOrder >= NEW.questionOrder
            AND questionOrder < OLD.questionOrder
            AND id != NEW.id;
        END IF;
    -- 删除时调整后续序号
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE questions
        SET questionOrder = questionOrder - 1
        WHERE questionOrder > OLD.questionOrder;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

然后绑定触发器到表：

CREATE TRIGGER trigger_adjust_question_order
AFTER INSERT OR UPDATE OF questionOrder OR DELETE ON questions
FOR EACH ROW EXECUTE FUNCTION adjust_question_order();

方案二：用浮点数替代整数序号 #

把questionOrder改成numeric类型，插入新问题时直接用中间值（比如插在4和5之间就设为4.5），完全不用修改其他记录：

-- 插入到questionOrder=5的前面
INSERT INTO questions (questionOrder, ...)
VALUES ( (SELECT questionOrder FROM questions WHERE questionOrder=5) - 0.5, ...);

如果前端需要显示整数序号，查询时用窗口函数转换即可：

SELECT *, ROW_NUMBER() OVER (ORDER BY questionOrder) AS display_order
FROM questions;

方案三：链表式结构 #

放弃存序号，改用prev_question_id和next_question_id字段，每个问题记录上一个和下一个问题的ID。调整顺序时只需要修改相邻问题的关联ID，不用批量更新：
查询时用递归CTE获取完整顺序：

WITH RECURSIVE question_sequence AS (
    -- 找到第一个问题（prev_question_id为NULL的记录）
    SELECT id, question_text, next_question_id
    FROM questions
    WHERE prev_question_id IS NULL
    UNION ALL
    SELECT q.id, q.question_text, q.next_question_id
    FROM questions q
    JOIN question_sequence qs ON q.prev_question_id = qs.id
)
SELECT * FROM question_sequence;

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