How to obtain free records

On some occasions, we need to obtain or know records whose values are not being occupied.

Either because we want to insert a new value, or we want to restructure our table after a while.

To do this, you can use the following script

SELECT t.id + 1
FROM Table1 t
WHERE NOT EXISTS (
    SELECT * 
    FROM Table1 t2
    WHERE t2.id = t.id + 1
)
LIMIT 1

We must replace the name of the table, and the attributes we are looking for.

Now let's look at an example. Let's suppose that we want to search the first free record within creature

The first thing we have to identify is your primary key: guid

SELECT t.`guid` + 1
FROM `creature` t
WHERE NOT EXISTS (
    SELECT * 
    FROM `creature` t2
    WHERE t2.`guid` = t.`guid` + 1
)
LIMIT 1

When running the query, we will get as a result in this case, the number 15.

What we must do now is to check that this value is not being used, by means of a SELECT

SELECT * FROM `creature` WHERE `guid`=15;

To corroborate that the information is correct, I leave you the first 16 records.

As you can see, number 15 is available.