Set Auto Increment position, always for the smallest ID

Question

I have a database Mysql, where contains a table called usuarios

This table contains a column ID, where is Autoincrement and kind INT

I need you to:

• Whenever I enter the first result the ID be equal to 1

But if you delete and re-enter another result:

• ID is no longer 1 and becomes 2

---

How do I always have the smallest ID which has not been used?

Answer 1

This is standard and will always happen.

---

If you have 2 overlapping transactions you are making INSERTs:

• Transaction 1 makes a INSERT, gets the ID 26 (for example)
• Transaction 2 makes a INSERT, gets the ID 27

So:

• If transaction 1 fails, reverse operation
• Transaction 2 is completed with ID 27

---

If the consecutive values were guaranteed, each transaction would have to happen one after the other, i.e., would totally lose scalability. I wouldn’t worry about this type of control, because this type of standardization of data seems ineffective.

If the question is for enlightening effect I see no problem.

But for projects I do not recommend!

Answer 2

One simple way to achieve this is by using a gatilho || trigger in the table where you want to control the auto_increment.

Try to adapt the example below to your reality.

Let’s imagine a tabela_qualquer who has the spine id int not null auto_increment. The trigger will take the maximum value of this column and increment 10 before entering 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 doubt in the syntax I suggest to consult the Documentación Oficial.