How to get the next auto-increment id in mysql

Question

You can use

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

or if you do not wish to use information_schema you can use this

SHOW TABLE STATUS LIKE 'table_name'

You can get the next auto-increment value by doing:

SHOW TABLE STATUS FROM tablename LIKE Auto_increment
/*or*/
SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'tablename'

Note that you should not use this to alter the table, use an auto_increment column to do that automatically instead.
The problem is that last_insert_id() is retrospective and can thus be guaranteed within the current connection.
This baby is prospective and is therefore not unique per connection and cannot be relied upon.
Only in a single connection database would it work, but single connection databases today have a habit of becoming multiple connection databases tomorrow.

See: SHOW TABLE STATUS

This will return auto increment value for the MySQL database and I didn't check with other databases. Please note that if you are using any other database, the query syntax may be different.

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = 'your_database_name';

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = database();

The top answer uses PHP MySQL_ for a solution, thought I would share an updated PHP MySQLi_ solution for achieving this. There is no error output in this exmaple!

$db = new mysqli('localhost', 'user', 'pass', 'database');
$sql = "SHOW TABLE STATUS LIKE 'table'";
$result=$db->query($sql);
$row = $result->fetch_assoc();

echo $row['Auto_increment'];

Kicks out the next Auto increment coming up in a table.