CODEWITHSUNDEEP
mysql
user114671
user114671

Reputation: 532

Can I find out the next auto_increment to be used?

Is it possible to find out what the next auto increment will be for my primary key without executing an INSERT INTO query? Some rows are deleted meaning that it's not as easy as just adding one to a SELECT MAX query on the PK. Many thanks.

Upvotes: 5

Views: 2368

Answers (4)

Valter
Valter

Reputation: 89

This is also possible:

(SELECT (SELECT your_primary_key FROM Your_Table ORDER BY your_primary_key DESC LIMIT 1)+1);

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

If you really want to know next auto_increment value try SHOW TABLE STATUS returns next Auto_increment field, e.g.:

SHOW TABLE STATUS WHERE name = your_table_name;

or

SELECT Auto_increment
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = your_table_name

Upvotes: 7

Dan
Dan

Reputation: 1888

You can get the value by executing 

SHOW TABLE STATUS WHERE Name = nameOfTableHere

and then retrieving the column 'Auto_Increment' from the result

Upvotes: 2

Rich O'Kelly
Rich O'Kelly

Reputation: 41767

Try the following:

SELECT Auto_increment 
  FROM information_schema.tables 
  WHERE table_name= 'tableName'
  AND table_schema = DATABASE();

Upvotes: 0

Related Questions