MariaDB如何自动重排行并更新sortorder排序字段的取值

你的需求完全可以通过MariaDB的SQL语句实现，无需依赖PHP应用层处理，执行效率更高，也避免了多次数据库交互的开销。

以下是适配不同版本MariaDB的实现方案，默认按你要求的间隔10重排，可自行调整间隔参数：

方案1：MariaDB 10.2+ 支持窗口函数（推荐写法） #

首先执行查询验证新排序值是否符合预期，避免误更新：

SELECT 
    id,
    title,
    sortorder AS old_sortorder,
    (ROW_NUMBER() OVER (ORDER BY sortorder ASC)) * 10 AS new_sortorder
FROM 你的实际表名;

确认结果无误后执行更新操作：

UPDATE 你的实际表名 t1
INNER JOIN (
    SELECT 
        id,
        (ROW_NUMBER() OVER (ORDER BY sortorder ASC)) * 10 AS new_sort
    FROM 你的实际表名
) t2 ON t1.id = t2.id
SET t1.sortorder = t2.new_sort;

方案2：低版本MariaDB 用用户变量实现 #

如果你的数据库版本不支持窗口函数，可以用变量写法实现：

-- 初始化排序计数变量
SET @row_num = 0;
-- 按原sortorder升序重排更新
UPDATE 你的实际表名
SET sortorder = (@row_num := @row_num + 1) * 10
ORDER BY sortorder ASC;

注意事项 #

• 执行更新前请务必先备份表数据，或先运行查询语句确认生成的新排序值符合预期
• 上述SQL中*10就是排序的固定间隔，如果需要间隔100，直接修改为*100即可
• 如果业务中存在多分组独立排序的需求（比如不同分类下的排序互不干扰），只需要在窗口函数中增加PARTITION BY 分组字段参数，或调整变量写法的排序逻辑即可适配

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