Well, it's the following I have a MySQL table, where it contains a table designated by users.
In this table, it contains a column named ID, where it is AutoIncrement and type INT.
What I want to know, if it is possible to do in some configuration is:
Whenever I enter the first result the id is = 1, but if you delete and re-insert another result, the ID is no longer 1 and becomes 2.
How can I always be the smallest id that has not been "busy"?
Thank you.
This is standard and will always happen.
If you have 2 overlapping transactions that are doing INSERTs:
Then:
If consecutive values were guaranteed, each transaction would have to happen one after another, ie it would lose its scalability completely. I would not worry about this type of control, as this type of data standardization seems impractical.
If the question is for clarifying effect I see no problem, but for projects I do not recommend.
A simple way to do this is to use a gatilho || trigger in the table where you want to control auto_increment .
Try to adapt the example below to your reality.
Let's imagine a tabela_qualquer that has the id int not null auto_increment column. The trigger will grab the maximum value from this column and increment 10 before inserting data.
create trigger usr_tg_tabela_qualquer BEFORE INSERT ON tabela_qualqer
FOR EACH ROW
begin
DECLARE NEW_ID INT;
SELECT IFNULL(MAX(id),0) + 10 INTO NEW_ID FROM tabela_qualquer;
SET NEW.id = NEW_ID;
end;
Any questions in the syntax suggest I refer to Official Documentation .