Reputation: 532
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
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
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
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
Reputation: 41767
Try the following:
SELECT Auto_increment
FROM information_schema.tables
WHERE table_name= 'tableName'
AND table_schema = DATABASE();
Upvotes: 0