5

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.

user114671
  • 532
  • 1
  • 9
  • 27

4 Answers4

7

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
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • if you use the first example using `from` you have to specify a database name, not table name, you can add `WHERE name = your_table_name' to get just that table, of if you're already using the database drop the `FROM database_name` and just use the `WHERE` like the answer below – aron.duby Apr 15 '13 at 15:34
2

You can get the value by executing 

SHOW TABLE STATUS WHERE Name = nameOfTableHere

and then retrieving the column 'Auto_Increment' from the result

Dan
  • 1,878
  • 13
  • 17
0

This is also possible:

(SELECT (SELECT your_primary_key FROM Your_Table ORDER BY your_primary_key DESC LIMIT 1)+1);
Valter
  • 89
  • 3
  • If you're going to answer a question for which an answer has already been accepted - two years after it was answered - then please give a correct answer. This is incorrect as it doesn't take into account the fact that rows have been deleted - so you cannot just add one to the SELECT MAX - as I said in my opening post. – user114671 Feb 13 '14 at 14:42
  • sorry - I am guilty. I forgot LIMIT. Updated to working solution. SELECT Auto_increment... did not work in my situation. That's why I added alternative solution. – Valter Feb 14 '14 at 08:24
0

Try the following:

SELECT Auto_increment 
  FROM information_schema.tables 
  WHERE table_name= 'tableName'
  AND table_schema = DATABASE();
Rich O'Kelly
  • 41,274
  • 9
  • 83
  • 114