如何找到下一个免费的唯一 4 位数号码

Question

在我的数据库应用程序中，我需要为每个客户提供唯一的 4 位数字字段。直到 9999，我都可以使用自动增量，但之后我将不得不重用已删除的客户数量（给定时间不会超过 5000 个客户，但在整个生命周期中可能会有超过 9999 个客户）系统）。

问题 1：是否有 (My)SQL 语句来查找下一个可重复使用的免费号码？

问题 2：如果我获得号码，将其分配给新客户并将该客户全部保存在一笔交易中，那么同时发生的类似交易将由数据库排序，这样号码就不会冲突，对吗？

Answer 1

您最好存储一个定义了所有 10,000 个可能值的表，并在每个值上添加一个“正在使用”标志。这样，释放号码以供重复使用就是设置“inuse=false”的简单更新。

还使查找最低可用值变得简单。

SELECT idstring
FROM idstringtable
ORDER BY idstring ASC
WHERE (available = 1)
LIMIT 1

使用适当的锁/事务来做到这一点将防止两个或多个请求获得相同的 ID，并且由于它是一个小表，因此执行全局表锁不会显着影响性能。

否则，您将不得不在用户表中翻找，试图找到编号序列中的第一个“间隙”。

Answer 2

如果您必须使用此模型（我建议不要使用它），那么我将创建一个“可用”号码池，然后在创建帐户时，只需从中获取前 1 个号码即可。然后，当用户被删除时，将该号码返回到池中。

Answer 3

这是为了查找第一个可用插槽：

select i1.id + 1 as FirstAvailable
from issues i1 left join issues i2 on (i1.id = i2.id - 1)
where i2.id is null
limit 1

这是针对生产 Redmine 实例运行的，以查找第一个丢失的 id。根据您的需要进行调整。

Answer 4

使用单独的表来跟踪正在使用的 ID 的建议是可行的，但如果您不想使用单独的表来跟踪已使用的 ID，则可以执行自连接来查找 ID 号中的间隙。自连接非常简单：

select top 1 t1.id + 1
  from table t1
  left join table t2 on t1.id = t2.id - 1
 where t1.id < 10000
   and t2.id is null

在 MS SQL Server 中，我使用 TOP 1 来获取最上面的结果，但在 MySQL 中可能有不同的语法。

Answer 5

上面的答案（由 Adrian Carneiro 提供）非常棒，并且除非表使用不同的字段作为主键并且没有“id”键，否则该答案有效。

给定一个主键为 userid 的表：-

MariaDB [unixua]> select userid, uid from accounts;
+---------+----------+
| userid  | uid      |
+---------+----------+
| acc0001 | 89814678 |
| acc0002 | 38000474 |
| acc0005 | 38000475 |
| acc0017 | 38000478 |
+---------+----------+
4 rows in set (0.00 sec)

我们预计最低的可用编号为 38000476。

MariaDB [unixua]> SELECT t1.uid +1 FROM accounts t1 
LEFT JOIN accounts t2 ON (t1.uid +1 = t2.uid) 
WHERE t2.uid IS NULL AND t1.uid>38000474 LIMIT 1;
+-----------+
| t1.uid +1 |
+-----------+
|  89814679 |
+-----------+
1 row in set (0.00 sec)

但是，因为 MySQL / MariaDB 按主键顺序选择它们，所以这会失败，并给出“acc001”之后的下一个最高编号。
通过向 uid 列添加键并仅对“uid”列执行 SELECT，MySQL/MariaDB 将使用索引来检索数据（而不是读取表）。由于索引是“有序的”，因此结果是不同的：-

MariaDB [unixua]> alter table accounts add unique index (uid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [unixua]> SELECT t1.uid +1 FROM accounts t1 
LEFT JOIN accounts t2 ON (t1.uid +1 = t2.uid) 
WHERE t2.uid IS NULL AND t1.uid>38000474 LIMIT 1;
+-----------+
| t1.uid +1 |
+-----------+
|  38000476 |
+-----------+
1 row in set (0.00 sec)

确保您的表具有“客户 ID”字段的键（并且该客户 ID 字段是数字）。

这是有效的，因为优化器可以从索引（又名accounts.myi，而不是accounts.myd）中检索选择所需的所有数据，而不是表数据。