MySQL可用的任务表顺序重排SQL在PostgreSQL执行返回false如何解决

核心错误原因 #

• 你写的new_ordering = new_ordering + ordering_inc是相等比较逻辑，不是赋值操作：PL/pgSQL中单等号=默认作为比较运算符使用，这里的逻辑是判断new_ordering是否等于new_ordering + 1，结果永远为false，拼接后就得到了所有行都是p/false的结果
• PostgreSQL不支持MySQL非标准的:=在DML表达式中赋值并返回结果的语法：MySQL的@var := 表达式是专属的非标准语法，PostgreSQL没有对应的兼容实现，直接写:=会触发语法错误
• 额外注意：order是SQL标准保留字，作为字段名使用时必须用双引号"包裹，否则会被识别为ORDER BY的关键字触发语法错误

正确实现方案 #

PostgreSQL中不需要写存储过程块，直接用窗口函数ROW_NUMBER()就能实现批量生成递增值的需求，写法更简洁，性能也更好：

UPDATE tasks t
SET "order" = CONCAT('p/', sub.rn)
FROM (
  -- 这里ORDER BY可以根据你的实际排序需求调整，比如原MySQL是按主键升序就写ORDER BY id
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM tasks
) sub
WHERE t.id = sub.id;

如果你确实需要用变量实现递增逻辑，也可以用PL/pgSQL循环写法（性能略低于窗口函数方案，仅做参考）：

DO $$
DECLARE
  ordering_inc integer := 1;
  new_ordering integer := 0;
  task_rec record;
BEGIN
  -- 按需要的排序遍历tasks表
  FOR task_rec IN SELECT id FROM tasks ORDER BY id LOOP
    new_ordering := new_ordering + ordering_inc;
    UPDATE tasks SET "order" = CONCAT('p/', new_ordering) WHERE id = task_rec.id;
  END LOOP;
END $$;

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