is it possible to using last id where it's deleted mysql?

Question

so i have same problem with this question Auto Increment after delete in MySQL

and according to that answer, and considering for each condition that provided on that answer, i've to say that i must do the auto increment after delete on mysql.

i have table lets say

table : product
fields : id, name

so if i have ids of 16,17,18 and then i deleted id 16, and i make the new one, i want to make the new id was on "16" not on 19. according to that answer

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

i must say that yes, it's possible the fields cant provide large enough key space

what should i do

note: the id on that table its unique and didnt connected to any other table, i just want to avoid if the ids cant provide large enough key space again

This looks like a very good idea to go into rabbit's hole... — Chetan, Oct 23 '20 at 06:26
so what's the best solution? do i really to change the type of id from int(10) into bigint? — 18Man, Oct 23 '20 at 06:28
If you really ever reach the 2 billion products you will have completely other problems, like scaling for performance etc — Thallius, Oct 23 '20 at 06:30
Also, note the number in parentheses following an INT declaration is almost entirely meaningless — Strawberry, Oct 23 '20 at 07:11
So I guess you are deleting and adding new ones every second? Then you should completely redesign your concept — Thallius, Oct 24 '20 at 09:57

spike 王建 · Accepted Answer · 2020-10-23T11:03:56.673

How to achieve it?

To be honest, this is achieveable.Insert record's corresponding id value into an ordered queue every time it is deleted, and take the smallest value from the queue as the id to be insered(if the queue is empty, use id generated by database).

Is it worth doing?

I will assume that your primary key is the id, whcih is a clustered index in mysql. So this way may cause more page splits, which will slow insertion performance.
And you need to ensure that the id in the ordered queue can only be consumed by one inserting thread at the same time.If you inserted fail, you need to re-enqueue the id.If the size of the ordered queue is too large, the sorting operation will also slow performance.
At last, you can't rely on id for analysis. eg. The record with max id is the latest record.

is it possible to using last id where it's deleted mysql?

1 Answers1

How to achieve it?

Is it worth doing?