Can I find out the next auto-increment to be used?

Question

Is it possible to find out what the next automatic increment will be for my primary key without executing an INSERT INTO query? Some rows are deleted, which means it's not as simple as adding it to a SELECT MAX query on a PC. Many thanks.

Answer 1

If you really want to know the following auto_increment value, try SHOW TABLE STATUSreturning the following field Auto_increment, for example:

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

Answer 2

You can get the value by doing

SHOW TABLE STATUS WHERE Name = nameOfTableHere

and then extracting the column "Auto_Increment" from the result

Answer 3

:

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

Answer 4

:

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