mysql - 更新表以增加每个用户 ID 的值

Question

我有下表(称为 node_items):

id  node_id position
==  ======= ========
1      1       1
2      1       1
3      2       1
4      2       1
5      2       1
6      2       1
7      3       1
8      3       1
9      3       1
10     3       1

position 字段应该标记项目在节点中的位置。它们目前都设置为 1。我想通过为每个 node_id 的每个项目设置一个增量位置来修复这个表。所以，我追求的结果是:

id  node_id position
==  ======= ========
1      1       1
2      1       2
3      2       1
4      2       2
5      2       3
6      2       4
7      2       5
8      3       1
9      3       2
10     3       3

所以我需要以某种方式按 node_id 对每个项目进行分组，并将位置设置为初始值 1，并为每个具有该 node_id 的后续项目增加它。

注意:为了让我的解释简单，上面的 node_ids 按顺序显示 (1,1, 2, 2,2,2,2,3,3,3)，但通常情况并非如此。

我可以使用什么查询来增量更新每个 node_id 的位置值？

Answer 1

可以通过 UPDATE 修复表一次:

SET @n = 0, @p = 1;

UPDATE node_items
SET position = (@p := IF(node_id=@n, @p+1, 1)),
    node_id = (@n := node_id)
ORDER BY node_id, id;

在您插入/更新/删除数据时让表维护位置值更难。基本上，在允许并发更新的情况下不能这样做。您必须在每个需要写入表的 session 中锁定表。这使得并发 session 串行运行。

您可以在这里阅读我关于此的旧帖子:Some sort of “different auto-increment indexes” per a primary key values