如何使用MySQL在哪里选择第一个可用的id？

Question

有一个表table，还有一个列column和另一个列userId。表table可以包含具有相同userId的任意数量的行。但是，在SELECT column, userId FROM table集合中，不应该有重复(column，userId)行的列。这些行通常会被创建、读取、更新、删除和创建。我希望每个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 |
|    ... |    ... |
+--------+--------+

当一行被删除时，我想获取某个userId的第一个可用的列column 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。所以我可以像这样展示它们：

第一军团，

第二集团军

第三集团军

..。

第100集团军

等

这对应用程序的功能并不重要，但我发现有了这样的编号系统，军队更容易记忆，更容易识别，然后，比如说，显示一些“随机”的长id

Answer 1

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

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

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)上建立一个索引来优化它。

这个解决方案非常简单，但它有几个缺陷，当您为给定的userid创建第一行时，它不起作用(除非它没有返回任何行，您知道位置1是可用的)，并且随后它永远不会告诉您位置1是否是第一个可用的间隙。

另外，要当心race conditions。您的查询可能会找到一个间隙，但是在您的代码插入新行以使用该间隙之前，另一个并发请求可能正在做同样的事情，找到相同的间隙并填充它。防止这种情况发生的唯一方法是：

• 保证一次只有一个请求在处理给定用户is的数据。
• 在您为间隔进行选择时，使用locking read锁定给定用户is的所有行。

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

您在您的问题中添加了详细信息，即您希望使用此命令为军队分配名称：

第一军团，第二军团，第三军团，

您可以考虑创建另一个表"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来记住军队的名字，这样我就可以删除它。也可以通过三个步骤来完成此操作:选择以获取军队名称，插入到armies表中，从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;

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