How do you get the next id?

gerardg

Without actually adding a record? Mainly just curious - I think the latest version of phpmyadmin displays what the next ID would be for an auto_increment column. I scanned the docs but didn't see anything.

mattw

I would think you could do something like:

SELECT idcolumn FROM table ORDER BY idcolumn DESC LIMIT 0,1

and then add 1 to your result.

Would this work?

Matt Wade
codewalkers.com

Anon

select max(idcolumn)+1 from table_name;

You can't use this value though, because someone else may come along and insert something into that table and the auto_inc column will increment and your value is now worthless.

[deleted]

Note the words 'would be', you cannot actually tell what the next number is going to be untill you insert a record.

Why not? because databases make absolutely sure that it is not possible to insert the same number twice.

In MySQL you get auto_increment numbers that only 'update' when a record is inserted, so you cannot find out what the next number would be without inserting a record. But when you insert a record you force the number to be updated, so even though you can find out what the number was at the time you were wondering about which number it was, as soon as you find out which number it was the number has already changed.

Real databases use sequences, which do not require you to insert a record, but every time you query a sequence, it will give you a number and update itself for the next request.

gerardg

phpmyadmin uses some means to get the 'next autoindex' and it's not by adding one to max. Guess I'll dig through the code.

gerardg

Right, and if records from the end of the table have been deleted max+1 won't be the id used.

[deleted]

Remember what you said before, this is only to satisfy your curiosity, there is no way of getting the true new value.

gerardg

Found it - "show table status [like 'tablename']". That's the true next auto_increment value is it not?

[deleted]

No.

As the name describes, SHOW TABLE STATUS gives the status of the table at the time you request it.

But 0.0001 seconds after requesting this information, another script can insert a new record and update the value, leaving you with invalid data.

gerardg

Yes it is!!! :-)

I'm fully aware of the possible race conditions 😛