有一个表table,有一个列column和另一个列userId
table可以具有任意数量的具有相同userId的行。但是,在SELECT column, userId FROM table的集合中,永远不应包含重复的(列,用户ID)行。
这些行将经常被创建,读取,更新,删除和创建。
我希望每个userId都具有本地column ID,如下所示:

+--------+--------+
| column | userId |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      3 |      1 |
|      4 |      1 |
|      5 |      1 |
|    ... |    ... |
|      1 |      2 |
|      2 |      2 |
|      3 |      2 |
|      4 |      2 |
|      5 |      2 |
|    ... |    ... |
+--------+--------+


当删除一行时,我想获取某些column的第一个可用列userId id。我会:

SELECT AVAILABLE_ID(column)
 FROM table WHERE userId = 1 ORDER BY column ASC LIMIT 1


要么

SELECT FIRST_AVAILABLE_ID(column)
 FROM table WHERE userId = 1


因此,如果我们看到表table的这种状态:

+--------+--------+
| column | userId |
+--------+--------+
|      1 |      1 |
|      2 |      1 |
|      3 |      1 |
|      5 |      1 |
+--------+--------+


我想收到:

+--------+
| column |
+--------+
|      4 |
+--------+


而且,如果我要为某些userId插入第一行,则希望该列为:

+--------+
| column |
+--------+
|      1 |
+--------+


如果两者之间没有缺失的间隙,我只想在下一个可用的SELECT中输入column
另外,表table具有大量创建,更新,删除操作的内容,因此我需要任何可以快速处理成千上万行的解决方案。
我认为此查询未优化:

SELECT * FROM (
    SELECT t1.column+1 AS Id
    FROM table t1
    WHERE userId = 1 AND NOT EXISTS(SELECT * FROM table t2 WHERE userId = 1 AND t2.column = t1.column + 1 )
    UNION
    SELECT 1 AS column
    WHERE userId = 1 AND NOT EXISTS (SELECT * FROM table t3 WHERE userId = 1 AND t3.column = 1)) ot
ORDER BY 1 LIMIT 1


现在,为了更详细地解释为什么我需要这样做:
原因纯粹是化妆品。
我正在研究一种策略游戏,玩家可以在那里拥有部队。部队可以有两种状态:已分组或未分组。如果将它们分组,则几行将具有相同的group_id。然后,我将它们全部加在一起成为一个单行,并与查询结果集中的其他行合并,其中某些行可以分组或不分组。如果将它们分组,我希望每个玩家的分组部队相对于玩家其他部队具有唯一的group_number。
所以我可以像这样显示它们:

第一军

第二军

第三军

...

第一百军

等等

这对于应用程序的功能不是很关键,但是我发现拥有这样的编号系统,军队更容易记忆并且更易于识别,然后显示一些“随机”长ID

最佳答案

数据库擅长跟踪存在的数据,但不擅长跟踪丢失的数据。

您可以通过以下方式找到差距:

select t1.col+1 as avail_col
from mytable as t1
left outer join mytable as t2
  on t1.userid = t2.userid and t1.col+1 = t2.col
where t1.userid = 1234 /* whatever userid you search for */
  and t2.col is null
order by avail_col limit 1;


您需要在(userid,col)上建立索引来优化此功能。

该解决方案非常简单,但是存在一些缺陷,即在为给定的用户ID创建第一行时,它不起作用(除非它不返回任何行,您知道位置1是可用的),随后它永远不会告诉您位置1是否是第一个可用间隙。

另外,请注意race conditions。您的查询可能会发现一个空白,但是在您的代码插入新行以使用该空白之前,另一个并发请求可能正在做同样的事情,找到相同的空白并填充它。防止这种情况的唯一方法是:


确保一次处理一个给定用户ID的数据的请求不超过一个。
在选择间隙时,使用locking read锁定给定用户ID的所有行。


目前尚不清楚为什么需要填补这些空白。在大多数情况下,当我看到类似的问题时,应用程序需要更改其设计,以避免要求填补空白。



您已在问题中添加了详细信息,您想使用它来为军队分配名称:


  第1军,第2军,第3军,...


您可以考虑创建另一个表“ unused_army_names”或其他内容。在游戏开始时,每个user_id填充100行。

用户创建军队时,请进行锁定读取以从该表中选择第一个条目,并在插入时将其从表中删除

START TRANSACTION;

INSERT INTO armies (army_name, user_id)
SELECT @army_name := army_name, user_id
FROM unused_army_names
WHERE user_id = 1234
ORDER BY army_name LIMIT 1
FOR UPDATE;

DELETE FROM unused_army_names
WHERE user_id = 1234 AND army_name = @army_name;

COMMIT;


因为我使用FOR UPDATE,所以它将锁定我在读取它们时选择的行,因此,如果另一个并发请求尝试执行相同的操作,它将停止并等待获取自己的锁。一旦我的第一个事务提交,它将释放锁,而另一个事务进行。到那时,我已经从未使用的军队表中删除了军队4,其他事务将读取下一个可用的军队名称。

我使用user-defined variable记住军队名称,因此可以将其删除。一个人也可以通过三个步骤来做到这一点:SELECT以获取军队名称,INSERT进入军队表,从unused_army_names表中删除。

通过使用事务来包装这两个更改(并假设您使用支持事务的InnoDB),可以确保它们对其他客户端而言是单个原子更改。没有人能看到部分完成的数据。

然后,当一支军队失散时,将其放回原处:

START TRANSACTION;

DELETE FROM armies
WHERE user_id = 1234 AND army_name = ?;

INSERT INTO unused_army_names (army_name, user_id) VALUES (?, 1234);

COMMIT;


我假设在代码的这一点上,您知道哪支军队丢失了,您可以将军队名称作为参数传递给两个查询。

09-10 06:27
查看更多