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

»»»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 DatabaseNameTableName 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 DatabaseNameTableName 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.

By | 2017-08-06T08:50:56+00:00 August 6th, 2017|Categories: Databases|Tags: , , |0 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.

Connect with LK through Tech Journey on Facebook, Twitter or Google+.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!