Add +1 to last entry with PHP/MySQL (auto-increment)

Question

Possible Duplicate:
How to fill in the “holes” in auto-incremenet fields?

We are currently using auto-increment on a table where entries come and go constantly. The problem with this, is that eventually the auto-increment id becomes huge, as that is how auto-increment works.

We would like to have it always add +1 to the last entry.

For example:
We have 4 entries and id 4 is deleted. Next added entry should get id 4, and not 5.

I am not sure if this has been asked before. But after searching I was only able to find solutions on how to get the next auto-increment number, which is not what I am looking for at all.

I strongly suggest against such scheme. If you re-use id's, you can end up with references to wrong rows, instead of broken references. It can be really hard to debug and can lead to security vulnerabilities. — Ilmo Euro, Oct 30 '12 at 12:53
What's the problem with autoincrement values? It's designed for exactly this, shouldn't matter how "huge" it gets; and "rolling your own" using last entry + 1 is going to give you constant problems — Mark Baker, Oct 30 '12 at 12:53
Could you explain why you are doing this? The idea of auto-incriment is so that you always have a unique key for your DB table. Typically, these keys do not need to be changed (and regardless of the number, it won't slow your DB down - even if the numbers are 1, 2, 3 and 7463, there are still only 4 entries). — David Gard, Oct 30 '12 at 12:54
This does actually get asked pretty often, and the answer is always the same - it is highly inadvisable to attempt to fill in the gaps. Many solutions to renumbering on the fly are prone to race conditions. — Michael Berkowski, Oct 30 '12 at 12:54

Jonathan Muller · Accepted Answer · 2012-10-31T10:34:02.567

1

YOU SHOULD NOT DO THAT because auto_increment is designed this way for good reasons (like if you have a backup and you want to restore it when it contains old deleted id that has been rewrote, how do you do ?)

But to answer your question: You have to use

ALTER TABLE `table` AUTO_INCREMENT = MAX(id)+1;

After a delete, you can make a trigger

edited Oct 31 '12 at 10:34

answered Oct 30 '12 at 12:55

Jonathan Muller

7,348
2
23
31

This will break his data consistency. (See my post) – tftd Oct 30 '12 at 13:19
1

Then I edited it to use id+1 instead of id. He still will get holes on his table if he dont delete the last id, but whatever he should not change the behaviour of auto_increment we all agree on that – Jonathan Muller Oct 30 '12 at 13:21
1

Shouldn't it be `MAX(id)+1` instead of `MAX(id+1)` ? – Buksy Oct 30 '12 at 18:43
You are totally right, I edit that – Jonathan Muller Oct 31 '12 at 10:33

score 0 · Answer 2 · answered Oct 30 '12 at 12:54

0

If you really want to do that (I personally encourage you to use auto-increment), you need to perform a transaction in order to get the last ID and insert the new row setting its ID incrementing it according to the other one.

answered Oct 30 '12 at 12:54

Carlos

4,949
2
20
37

score -1 · Answer 3 · answered Oct 30 '12 at 12:56

It's not a good idea to do this, the ID should always stay unique, no matter if the record exists or is deleted.

However if you really want to do it, you can to it with something like

select max(id) + 1 of bla;

But you need the right transaction level for it because if you don't you have a possibility of duplicated ids.

score -3 · Answer 4 · answered Oct 30 '12 at 12:55

-3

You can choose maximum id value and just increment it.

SELECT MAX(`id`)+1 as `new id` FROM `table`;

If you have 5 entries and 3rd gets deleted, this will still get you 6 as next id.

answered Oct 30 '12 at 12:55

Buksy

11,571
9
62
69

1

But if 2 queries run at the same time, they'll both get 6 as the next id. But auto_increment would also have correctly provided 6 & 7. – Michael Berkowski Oct 30 '12 at 12:55
I think this is obvious. You will have to lock the table. – Buksy Oct 30 '12 at 12:59

Add +1 to last entry with PHP/MySQL (auto-increment)

4 Answers4