How to get an extra value from a table

Question

I need to get the next id from the table (auto_increment).

I could just use SELECT * from table ORDER BY id DESC LIMIT 1;

For example, I get 50. But if we remove from two elements of the table, I get 48, but fix one

will be 51. How to get the correct value, even we will delete something from the table?

Answer 1

 show table status like 'table_name' 

next id value is in the field "Auto_increment"

Answer 2

You can use only SHOW TABLE STATUS LIKE 'tablename' to get the value of auto_increment. A simpler solution might be: SELECT MAX(id) + 1 FROM table , but this is an error if the last record was deleted.

Answer 3

 SHOW TABLE STATUS LIKE 'table' 

The value you want is in the Auto_increment field.

Be careful with concurrency, though: by the time you get close to using this value, some other client could get into the table, and thus your value is out of date. It is usually best to try not to need it.

Answer 4

 SELECT LAST_INSERT_ID() + 1; 

gets the last identifier used in the insert in the auto-increment column + 1

Answer 5

I see two solutions for the following ID:

1) Select a larger column value with maximum function. Example: select max (id) from the table; 2) Using the SHOW STATUS LIKE command and get the correct array index. Take a look: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 6

It seems to me that you are creating the conditions for the race here.

Why can't you insert the row you want to insert and then use LAST_INSERT_ID () to find its ID?