Can I find out the next auto_increment to be used?

Question

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.

Answer 1

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