1

There are a few questions about this on SO, but none of them applies here. The questions are all about knowing an ID before inserting a row (and the answers are all "insert your stuff, then get the ID"). In my case, I don't want to insert anything, I just want to know the current state of an auto increment column.

  • I have a table with an auto increment column.
  • If I add a row, it will get id = 1.
  • If I delete this row and add another one, the table will have only 1 entry, but it will get id = 2.

I want to know which ID will be used next by this column.

Since I can't rely on the table to know this, I searched in the information_schema table, but didn't find such data.

Is there a place where the next (or current) auto increment value is stored ?

FrancoisBaveye
  • 1,902
  • 1
  • 18
  • 25
  • 3
    You can get that from `information_schema.TABLES`, but its pointless, because the value is obsolete at the moment you do it (someone might decide to insert a row after you get the value). – Vatev Jul 16 '15 at 09:14

1 Answers1

1

You can look it up by doing a query like : 

SHOW TABLE STATUS WHERE `Name` = 'table_name'

In the result you get a column named Auto_increment. This is the value you are looking for.

Hans Loos
  • 26
  • 2