php - 如何在 MySQL 中实现事务安全的随机序列

Question

所以我的应用需要让用户生成随机字母数字代码，例如 A6BU31、38QV3B、R6RK7T。目前它们由 6 个字符组成，而 I 和 O 未被使用(因此我们有 34^6 种可能性)。然后将这些代码打印出来并用于其他用途。

我现在必须确保许多用户可以为每个请求“保留”最多 100 个代码，因此用户 A 可能想要获得 50 个代码，用户 B 想要生成 10 个代码，依此类推。这些代码在所有用户中必须是唯一的，因此用户 A 和用户 B 可能不会同时收到代码 ABC123。

我目前的方法(使用 PHP 和 MySQL)为此有两个 InnoDB 表:

• 一个(“存储库”)包含大量预生成代码(因为随着时间的推移，冲突的可能性会增加，我不想尝试插入如果失败尝试另一个代码方法)。存储库仅包含代码和一个自动递增的 ID(因此我可以对它们进行排序，见下文)。
• 另一个表保存保留键(即代码 + 拥有用户)。

每当用户想要保留N个 key 时，我计划执行以下操作

BEGIN;
INSERT INTO revered_codes (code,user_id)
  SELECT code FROM repository WHERE 1 ORDER BY id LIMIT N;
DELETE FROM repository WHERE 1 ORDER BY id LIMIT N;
COMMIT;

这应该有效，但我不确定。看起来我正在构建一个 WTF 解决方案。

插入后我必须选择刚刚保留的代码以将它们显示给用户。这是棘手的部分，因为我真的不知道如何在交易完成后识别刚刚保留的代码。我当然可以在我的 reserved_codes 表中添加另一列，其中包含某种随机标记，但这似乎更加 WTFy。

我最喜欢的解决方案是有一个随机数序列，这样我就可以在 reserved_codes 表中执行 INSERT 操作。

那么，如何在 MySQL 中实现这种唯一的、随机的和事务安全的序列呢？一个想法是对 reserved_codes 表进行定期自动递增，并从该数字列中导出随机代码值，但我想知道是否有更好的方法。

更新:我忘了说拥有一个相当小的保留代码表是有利的，因为我稍后必须再次找到单个代码来更新它们(reserved_codes 有几个属性)。所以让保留表缓慢增长是好的(而不是拥有超过 ~1mio 预生成代码的巨大索引)。

Answer 1

如果您已经有一个存储库表，我只需添加一个用户列，然后运行此查询:

UPDATE repository SET user_id = ? WHERE user_id IS NULL LIMIT N;

之后，您可以再次选择记录。这有两个明显的缺点:

• 您需要一个关于user_id的索引>
• 除了将其绑定(bind)到用户外，您不能将表中的代码用于任何其他用途。