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
The first thing we have to identify is your primary key:
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 * 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.
Note: the table has many more attributes, but we show only some, so that the table would not be so extensive.