MySQL: Generating a new “random” number which isn’t already present in a table

The Problem

You have a list of numbers in a table and you want to generate a new number, between a set of two values, which isn’t already present in the table.

tl;dr

There isn’t really a nice solution. The “optimal” solution below is about as good as it gets.

Sub-Optimal Solutions

You may have already come across this StackOverflow post. The accepted solution is totally wrong since, as the comments elude to, it’s comparing a string. Some of the other solutions are OK but will return no results when the number it generates happens to already be in the table. As such, you’ll need to run the query multiple times until you get a result. This will be a problem when you’re generating a number between 1 and a million but already have many rows in the table. You’ll have to run the query possibly hundreds of thousands of times to get a result.

The Optimal Solution

Ok, so this doesn’t generate truly random numbers. If randomisation is a requirement (e.g. because you don’t want people to predict the next number, for security reasons) then this isn’t the solution for you. That said, it’s going to cover 99% of usecases. Here’s the query:

SELECT IFNULL(IF (MAX(id_sensor) >= 65535, MIN(id_sensor) - 1, MAX(id_sensor) + 1), 101) new_id FROM sensors;

This will generate a new number between 1 and 65535 which will not be in the id_sensor column of the sensors table.

What it does is takes the number above the largest number in our table, unless that number is already our maximum (65535). If it is, it takes one number below the minimum.

If no numbers exist in the table, it’ll start at 101.

The Gotchas

There’s a few “gotchas” to this query

If you happen to have two numbers in the table, 1 and 65535 then you’re going to get no results. This shouldn’t be a problem as long as you use it from the outset
You’re going to get 0 returned if you’ve run out of numbers (as far as the MIN/MAX algorithm is concerned, at least). You need to handle this