How to get the next auto-increment id in mysql

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();

For MySQL 8 use SHOW CREATE TABLE to retrieve the next autoincrement insert id:

SHOW CREATE TABLE mysql.time_zone

Result:

CREATE TABLE `time_zone` (
  `Time_zone_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1784 DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Time zones'

See the AUTO_INCREMENT=1784 at the last line of returned query.

Compare with the last value inserted: select max(Time_zone_id) from mysql.time_zone

Result:

+-------------------+
| max(Time_zone_id) |
+-------------------+
|              1783 |
+-------------------+

Tested on MySQL v8.0.20.

If return no correct AUTO_INCREMENT, try it:

ANALYZE TABLE `my_table`;
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE (TABLE_NAME = 'my_table');

This clear cache for table, in BD

using the answer of ravi404:

CREATE FUNCTION `getAutoincrementalNextVal`(`TableName` VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    DECLARE Value BIGINT;

    SELECT
        AUTO_INCREMENT INTO Value
    FROM
        information_schema.tables
    WHERE
        table_name = TableName AND
        table_schema = DATABASE();

    RETURN Value;

END

using in your insert query, to create a SHA1 Hash. ex.:

INSERT INTO
    document (Code, Title, Body)
VALUES (                
    sha1( getAutoincrementalNextval ('document') ),
    'Title',
    'Body'
);

Improvement of @ravi404, in case your autoincrement offset IS NOT 1 :

SELECT (`auto_increment`-1) + IFNULL(@@auto_increment_offset,1) 
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = your_table_name
AND table_schema = DATABASE( );

(auto_increment-1) : db engine seems to alwaus consider an offset of 1. So you need to ditch this assumption, then add the optional value of @@auto_increment_offset, or default to 1 : IFNULL(@@auto_increment_offset,1)

For me it works, and looks simple:

 $auto_inc_db = mysql_query("SELECT * FROM my_table_name  ORDER BY  id  ASC ");
 while($auto_inc_result = mysql_fetch_array($auto_inc_db))
 {
 $last_id = $auto_inc_result['id'];
 }
 $next_id = ($last_id+1);


 echo $next_id;//this is the new id, if auto increment is on

SELECT AUTO_INCREMENT AS next_id FROM information_schema.tables WHERE table_name = 'table name' AND table_schema = 'database name of table name'