[RESOLVED] Next value on auto-increment

[Dasher]

Is there any way to determine the next value of a auto-increment column.

"SELECT (MAX(ID)+1) AS NEXTID FROM mytable" does it most of the time, but if the last entry were deleted the value returned would be wrong.

i.e. if last entry resulted in ID = 12 and then the row were deleted leaving the last number as 11, the next number would be 13 not 12. The rows would be numbered 1,2,3,4,5,6,7,8,9,10,11,13,....

Anyway to know that in advance?

I have another column that has a string which has a portion of it that should be equal to the auto-increment value in ID.

Or alternately can the auto-increment sequence be forced.

[triassic]

SHOW TABLE STATUS LIKE 'tablename'
gives an auto_increment value

[Dasher]

Exactly what I was looking for. That seems to work nicely.

In a short test I had a table with one entry, it reported Auto_increment as 2; I added a row, it reported 3, I deleted a row, it still reported 3, and added a row and the row was row 3, Auto_increment reported 4. -- perfect.

Thanks for your help.