How to reuse auto_increment values?

Question

Possible duplicate:
Fragmentation of identifier (auto_increment column) in mysql

I have this column in my database. Say its name is "threadlike." It contains unique identifiers set for each stream to distinguish.

ThreadId 9 8 7 6 5 4 3 2 1

Let's say I deleted the threads with identifiers 5 and 6.

ThreadId 9 8 7 4 3 2 1

But when there is a view after deletion, the unique identifier specified for this stream is 10. not 5. I think this is not neat.

How to get the smallest possible value in a column? (in this case 5.)

I think I can get the minimum column value using MIN () and keep +1 until I get an unused unique value, but I think it's too complicated.

Is there an easy way to do this?

Thanks.

Answer 1

I agree with the rest, where it is a very bad idea to implement your own minimum open number. But where I work, we are given a closed set of numbers, and when the number is freed, we must reuse.

Here's how we did it.

We do not delete the row, but set all the values ​​for each null column. So you do SELECT min (id) WHERE columnA NULL, and if this returns something, we will reuse it, otherwise we will insert a new row.

Answer 2

The maximum number of INTs in MySQL is 4294967295, if you create 1000 threads per minute, you will need 1440000 (1000 x 60 x 24) id per day. Thus, you have run out of ides in about 8.17 years.

The maximum number of BIGINT is 18446744073709551615, and this would be enough to create 1,000,000,000 (1 billion) identifiers per minute for 35,096 years .

So, you should be fine just losing ids as much as you want and don’t worry about them.

Answer 3

Do yourself a favor and forget about it. It will be a huge pain to realize, therefore, if there is no very convincing goal to achieve, which cannot be approached otherwise (unlikely), we are talking about massive pain without benefit.

MySql itself does not support this scenario for this very reason: a massive complication without benefits.

Answer 4

It contains unique identifiers for each thread for differentiation.

You said that. Unique identificator. For the difference.

How can you name a unique identifier? What difference can it be when today a topic has one identifier and the next day another?
How can you find it back?
How are you going to link it to other threads?

Try to think about the meaning of these words. This is called unique for this reason.

Try not to think like a child who wants “neat” and “cute” things just for fun, but like an adult who can see the consequences.

Try to learn to listen to more experienced people and follow your knowledge and experience based on advice, not just your whims.

Answer 5

So, for referential integrity, I do not recommend reusing identifiers. With maximum restrictions on int and bigint, you should never run out of unique values. Unique values ​​are the core of reference databases.

With that said, you can run a search routine (or instead of a trigger) to find the lowest id available. Then briefly turn off the inserts of identifiers, insert the data, then turn on the inserts again.

There are so many reasons not to do this! Among which there are all potential collisions between data insertion, when more than one value comes simultaneously.

Answer 6

MySQL will maintain the order of identifiers if the entire table is not truncated. Deleting rows does not affect the last identifier.