基于同表查询更新列：MariaDB按position顺序重设间隔值

现有一张表，数据按position列排序，结构及初始数据如下：

+------------+-------+----------+
| identifier | name  | position |
+------------+-------+----------+
|          1 | test1 |       23 |
|          2 | test4 |      144 |
|          3 | test2 |       68 |
|          4 | test3 |       96 |
+------------+-------+----------+

需求是：按照position列的现有排序，将position列的值按固定间隔（示例为50）重新赋值，更新后的数据如下：

+------------+-------+----------+
| identifier | name  | position |
+------------+-------+----------+
|          1 | test1 |       50 |
|          2 | test4 |      200 |
|          3 | test2 |      100 |
|          4 | test3 |      150 |
+------------+-------+----------+

之前可以通过代码循环实现：

rows = SELECT * FROM table ORDER BY position
for (i = 0; i < count(rows); i++) {
    UPDATE table SET position = ((i + 1) * 50) WHERE identifier = rows[i].identifier
}

但数据量较大时，循环更新效率极低，需要用单条SQL在MariaDB 10中完成。

解决方案 #

利用MariaDB 10支持的窗口函数ROW_NUMBER()，可以直接生成按原position排序的序号，再关联更新：

UPDATE your_table t1
JOIN (
    SELECT identifier, ROW_NUMBER() OVER (ORDER BY position) AS row_num
    FROM your_table
) t2 ON t1.identifier = t2.identifier
SET t1.position = t2.row_num * 50;

说明 #

1. 子查询中，ROW_NUMBER() OVER (ORDER BY position)会按照原position的升序顺序，为每一行生成从1开始的连续序号
2. 通过identifier将原表和子查询关联，把原表的position设置为序号乘以间隔值（示例中为50，可根据需求修改）
3. 单条SQL完成全表更新，避免了循环带来的多次IO操作，适合大数据量场景

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