CODEWITHSUNDEEP
phpmysqlsql
devsoft
devsoft

Reputation: 269

How get incremented value from table

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

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

For example I get 50. But if we delete from table two items I will get 48 but correct one

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

Upvotes: 4

Views: 108

Answers (6)

mluebke
mluebke

Reputation: 8799

Seems to me you're creating a race condition here.

Why exactly can you not insert the row you want to insert and then use LAST_INSERT_ID() to find it's ID?

Upvotes: 0

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385108

SHOW TABLE STATUS LIKE 'table'

The value you want is in the Auto_increment field.

Be careful about concurrency though: by the time you get around to using this value, some other client could have inserted into the table and thus your value is out of date. It's usually best to try to not need this.

Upvotes: 1

bruno.zambiazi
bruno.zambiazi

Reputation: 1482

I see two solutions for the next ID:

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

Upvotes: 0

halfdan
halfdan

Reputation: 34204

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

Upvotes: 2

Nicola Cossu
Nicola Cossu

Reputation: 56357

show table status like 'table_name'

next id value is in 'Auto_increment' field

Upvotes: 1

varzan
varzan

Reputation: 587

SELECT LAST_INSERT_ID() + 1;

gets the last ID used in an insert in an autoincrement column + 1

Upvotes: 0

Related Questions