How to Get Current Auto_Increment Sequence Number for MySQL / MariaDB Table

Most tables in database have a column with auto increment sequence number that serves as unique identifier for the rows of data. The auto_increment value is always set to increase when new data is added to the table. Over the time, the sequence number of auto_increment value for unique identifier may no longer in sync with total number of rows in table, especially when a lot of deletion or merging operations involved.

This tutorial shows you how to check and get current auto_increment value for a table. Note that the auto_increment value shown by the database is the NEXT sequence number to be used by database, i.e. to be inserted with new row.

Method 1

Run the following query:

SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;

Replace DatabaseName</em with actual database name, and TableName with actual table name. The auto_increment value is returned in one of the column.

Method 2

Run the following query:

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND   TABLE_NAME   = 'TableName';

Replace DatabaseName</em with actual database name, and TableName with actual table name. The query returns the auto_increment value directly.

Method 3

Run the following query:

SHOW CREATE TABLE TableName;

Replace TableName with actual table name. The query returns the CREATE TABLE query for the table, with the auto_increment value shown at the bottom.