Get the first available numbers not listed in my MySQL database table

Question

Any SQL to get the first numbers not listed in my MySQL database table? Example:

Table: Users

ID | Name | Number ------------------------ 1 | John | 1456 2 | Phil | 345 3 | Jenny | 345612 

In this case, SQL should return me a list of rows with numbers from 1 to 344 and from 346 to 1455 and from 1457 to 345611

Any suggestions? Maybe with some procedure?

Answer 1

I like @pst's answer, but suggest a different alternative.

• Create a new table of unassigned numbers, insert several thousand rows there.

• Present some of these numbers to the user.

• When a number is used, remove it from the unassigned numbers table.

• Generate more unassigned numbers periodically as needed.

Generating these unassigned numbers may use the random method suggested by @pst, but using this method, you move the uncertainty as to how long it takes to create a list of unassigned numbers in a batch task, rather than doing it at the front end while the user waits . This is probably not a problem if the use of the space space is sparse, but as the number space is used more, it becomes more serious.

Answer 2

Given the comments, my first approach would be to use a "random number" probe. This approach involves:

• Number indexed; and
• There are “significantly fewer” users than the available numbers.

an approach:

• Choose N (i.e. 1-10) numbers randomly on the client;
• Request a database for Number IN (ns..) or Number = n for N=1 ; then
• If the number is available, it can be detected based on the fact that you did not find the required entry.

In this case, the size N=1 is most likely “in order”, and this is most trivial to implement, although searching for 6 free queries will require at least 6 queries to the database. The higher N , the less number of trips to the database.

Be sure to use transactions.

Answer 3

Just use the auto zoom column. The database will automatically assign the next number. You do not even need to know what it is during insertion. Just tell the user the number that he received, do not give him any choice at all.

Answer 4

 SELECT 'start', 1 AS number FROM tableA UNION SELECT 'min', number - 1 number FROM tableA UNION SELECT 'max', number + 1 number FROM tableA ORDER BY number 

You can check the answer at http://www.sqlfiddle.com/#!2/851de/6

You can then compare the missing numbers when filling out the next time.

Answer 5

Based on your comments, the approach below may work for you. This does not answer your specific question, but probably fits your requirements.

I’m going to assume that your requirements cannot change (for example, present 6 possible choices to users). Honestly, I think this is a bit of a strange requirement, but it does some interesting SQL. :-)

Here's my approach: generate 10 random numbers. Filter everything already in the database. Present 6 of these random numbers to your user. Random identifier numbers have very good transaction cost properties compared to sequential identifier numbers, so this should scale very well if your application becomes popular.

 SELECT temp.i FROM ( SELECT 18 AS i -- 10 random UNION SELECT 42 -- numbers. UNION SELECT 88 UNION SELECT 191 -- Let assume UNION SELECT 192 -- you generated UNION SELECT 193 -- these in the UNION SELECT 1000 -- application UNION SELECT 123456 -- layer. UNION SELECT 1092930 UNION SELECT 9892919 ) temp LEFT JOIN mytable ON (temp.i = mytable.i) WHERE mytable.i IS NULL -- filter out collisions LIMIT 6 -- limit results to 6 

---

SQL Blog Polling Time !!!

• Why WHERE mytable.i IS NULL line “ WHERE mytable.i IS NULL ” filter conflicts? (Hint: how can mytable.i be null when it is the primary key?)

---

Here are some test data:

 CREATE TABLE mytable (i BIGINT PRIMARY KEY) ; INSERT INTO mytable VALUES (88), (3), (192), (123456) ; 

Run the query above and here is the result. Note that 88, 192, and 123456 have been filtered out, as they will interfere with test data.

 +---------+ | i | +---------+ | 18 | | 42 | | 191 | | 193 | | 1000 | | 1092930 | +---------+ 

And how to generate these random numbers? Probably rand () * 9223372036854775807 will work. (Assuming you don't want negative numbers!)