如何选择第一个可用的id,在哪里使用mysql?

p1iqtdky  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(363)

有一张table table 有一列 column 还有另一个专栏 userId . 表 table 可以有任意数量的具有相同用户标识的行。然而,在收集 SELECT column, userId FROM table 不应该有重复(column,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 . 我会:

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的这种状态 table :

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

我想收到:

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

如果我要为某个userid插入第一行,我希望列是:

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

如果两者之间没有缺失的空隙,我只想 SELECT 下一个可用 column . 还有,table 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

现在,更详细地解释一下为什么我需要这样做:原因纯粹是装腔作势。我正在做一个战略游戏,玩家可以有军队。部队可以有两种状态:分组或不分组。如果对它们进行分组,则有几行将具有相同的组id。然后,我将它们全部相加为一行,并与查询结果集中的其他行合并,其中一些行可以分组,也可以不分组。如果他们是分组的,我希望每个玩家的组队相对于其他玩家的部队有唯一的组队号码。所以我可以展示给他们看:
第一军,
第二陆军,
第三集团军,
...
第100军,
等等
这对应用程序的功能来说并不重要,但我发现有这样的编号系统,军队更容易记忆和识别,然后,比如说,显示一些“随机”的长id

vd8tlhqk

vd8tlhqk1#

数据库擅长跟踪存在的数据,但不擅长跟踪丢失的数据。
你可以这样找到一个缺口:

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是否是第一个可用的间隙。
另外,要注意比赛情况。您的查询可能会找到一个缺口,但在代码插入新行以使用缺口之前,另一个并发请求可能正在执行相同的操作,找到相同的缺口,然后填充它。防止这种情况的唯一方法是:
确保一次处理给定用户id的数据的请求不超过一个。
当您为间隙选择时,使用锁定读取锁定给定userid的所有行。
不清楚你为什么要填补这些空白。在大多数情况下,当我看到类似的问题时,应用程序需要更改其设计以避免填补空白。
您在问题中添加了详细信息,您希望使用此选项为军队指定名称:
第一陆军、第二陆军、第三陆军。。。
您可以考虑创建另一个表“unused\u army\u names”或其他什么。在游戏开始时,为每个用户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 ,这将在读取时锁定我选择的行,因此如果另一个并发请求尝试相同的操作,它将停止并等待获取自己的锁。一旦我的第一个事务提交,它就会释放锁,而另一个事务继续。到那时,我已经从未使用的army表中删除了army 4,另一个事务将读取下一个可用的army名称。
我使用一个用户定义的变量来记住军队的名字,这样我就可以删除它。您还可以通过三个步骤完成此操作:选择以获取陆军名称、插入到armies表、从unused\u army\u names表中删除。
通过使用事务 Package 这两个更改(并且假设您使用支持事务的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;

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

相关问题