0

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).

Question 1: Is there a (My)SQL statement to find the next reusable free number?

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?

chiborg
  • 26,978
  • 14
  • 97
  • 115
  • This sounds like a really brittle system. Why are you limited to four-digit IDs? – Martin Bean Oct 19 '11 at 20:25
  • 3
    (admittedly I don't know the situation, but I would be inclined to just use a 5-digit number - then you don't have to worry about things like "when he says 3403, does he mean the current customer with that number, or is this thing hanging over from a previous customer somehow?") – marnir Oct 19 '11 at 20:26
  • 2
    This is a **terrible idea**. Roll-your-own ID systems never work out well. This is that with the added limitation of a 4 digit number, which also implies to me you aren't storing it as a numeric value which is also a **terrible idea** – JNK Oct 19 '11 at 20:27
  • 1
    In addition to what @JNK says - re-using old customer numbers also sounds like a **terrible idea**. What if an old customer has a complaint and files their old (now reused) customer number? – Pekka Oct 19 '11 at 20:29
  • My first reaction when I heard this requirement from my customer was "Ewww" too ;-) But in my case the system won't have many customers and the deleted customer ids come from customers who were created erronously by automated processes. I'm limited to 4 digits because the 5 digit unique numbers are used for something else (double eww, I know). – chiborg Oct 19 '11 at 21:23

5 Answers5

7

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

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
2

If you MUST use this model (and I would recommend against it) then I would create a pool of "available" numbers and when creating the account, just grab the TOP 1 from that. Then, when a user is deleted return the number to the pool.

Jared Peless
  • 1,120
  • 9
  • 11
1

This is to find the first available slot:

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

This was run against a production Redmine instance to find the first missing id. Adjust accordingly to your needs.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • @user384706 It might have been clearer had Adrian written the join as `i2.id = i1.id + 1` this means that it looks for ids that are one greater than existing ids in the table and returns the first id where there wasn't one found. Another way to write it would have been `WHERE NOT EXISTS (SELECT id FROM issues i2 WHERE i2.id = i1.id + 1)` although you would have to profile the statements to see which performs better. – Neil Oct 19 '11 at 20:43
1

The recommendations to use a separate table to track the IDs that are in use will work, but if you do not want to use a separate table to track used IDs you could do a self join to find a gap in the id numbers. The self join is pretty simple:

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

In MS SQL Server I use TOP 1 to get the topmost result, but it may be different syntax in MySQL.

Parmenion
  • 299
  • 1
  • 3
0

The above answer (by Adrian Carneiro) is fantastic, and works unless the table uses a different field as primary key and does NOT have a key for 'id'.

Given a table with a primary key of 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)

We'd expect the lowest free number to be 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)

But, because MySQL / MariaDB is selecting them in primary key order, this fails, and gives the next highest after "acc001". By adding a key to the uid column and only performing the SELECT on the "uid" column, MySQL/MariaDB will use the index to retrieve data (instead of reading the table). Since the index is "ordered", the result is different :-

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)

Make sure your table has a key for the Customer ID field (and that customer ID field is numeric).

This works because the optimiser can retrieve all necessary data for the select from the index (aka accounts.myi, not accounts.myd), not the table data.

Rich Armstrong
  • 34
  • 4