在我的数据库应用程序中，我要求每个客户有一个唯一的4位数字字段.直到9999年，我都可以使用自动增量功能，但是此后，我将不得不重用已删除的客户数量(在给定的时间内，客户数量不会超过5000，但是在整个生命周期中，客户数量可能会超过9999)系统).
In my db application I have a requirement for a unique, 4-digit number field for each customer. Up until 9999 I can just use autoincrements, but after that I will have to reuse numbers of customers that have been deleted (there won't be more than 5000 customers at a given time but there may be more than 9999 customers over the lifetime of the system).
问题1:是否有(My)SQL语句来查找下一个可重用的免费电话号码?
Question 1: Is there a (My)SQL statement to find the next reusable free number?
问题2:如果我得到该号码，将其分配给一个新客户并将其全部保存在一次交易中，则同时发生的类似交易将由数据库进行排序，因此号码不会发生冲突，对吧?
Question 2: If I get the number, assign it to a new customer and save the customer all in one transaction, similar transactions taking place at the same time will be sequentialized by the database so the numbers won't collide, right?
推荐答案最好存储一个表，该表定义了所有10,000个可能的值，并在每个表上都标有使用中"标志.这样，释放该号码以重复使用是对设置"inuse = false"的简单更新.
You'd be better off storing a table with all 10,000 possible values defined, and an "in-use" flag on each. That way, releasing the number for re-use is a simple update to set "inuse=false".
也使查找最低可用价值变得简单
Also makes finding the lowest available value a simple
SELECT idstring FROM idstringtable ORDER BY idstring ASC WHERE (available = 1) LIMIT 1使用适当的锁/事务执行此操作将防止两个或多个请求获得相同的ID，并且由于它是一个小表，因此进行全局表锁不会显着影响性能.
Doing that with appropriate locks/transactions would prevent two or more requests getting the same ID, and since it's a small table, doing a global table lock would not significantly impact performance.
否则，您将被困在用户表周围，试图查找编号顺序中的第一个间隙".
Otherwise, you'd be stuck rummaging around your users table, trying to find the first "gap" in the numbering sequence.