How to read and reset AUTO_INCREMENT in MySQL

This tutorial provides the query to reset AUTO_INCREMENT in MySQL using InnoDB Table. It shows how to update the AUTO_INCREMENT attribute of an InnoDB Table.

Precautions

Do not use the ALTER command on tables having large data. MySQL takes a long time to update the table in case the table size is large.

The ALTER command should be used only when it is really necessary.

The AUTO_INCREMENT cannot be assigned with a lower value than the maximum of existing values.

Create a Database and Table

In this section, we will create the database and the table to be used to update the AUTO_INCREMENT attribute.

# Create the Database
CREATE SCHEMA `autoinc` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Create the Table
CREATE TABLE `autoinc`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(100) NOT NULL,
  `lastName` VARCHAR(100) NULL,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

To demonstrate the AUTO_INCREMENT operations, we will insert a few rows as shown below.

# Insert Rows
INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values('John', 'Dave', 'johndave007');
INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values('Vijay', 'Mallik', 'mallikvijay');
INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values('Ricky', 'Walker', 'rickyhunt');
INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values('Vikas', 'Roy', 'vikasroy');

AUTO_INCREMENT Read Examples

We can get the current AUTO_INCREMENT value for any table using the query as shown below. It might not reflect the value correctly since the INFORMATION_SCHEMA shows the approximate value which might not be the actual value.

# Syntax
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<database>' AND TABLE_NAME = '<table>';

# Example
SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'autoinc' AND TABLE_NAME = 'user';

# Result
---------------
AUTO_INCREMENT
---------------
5

Now wait for a minute and execute the below-mentioned queries.

INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values( 'Nick', 'Jones', 'nick' );

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'autoinc' AND TABLE_NAME = 'user';

# Result
---------------
AUTO_INCREMENT
---------------
5

It still shows the AUTO_INCREMENT value as 5, but the expected value is 6. This is what I meant with an approximate value.

We can also call the function LAST_INSERT_ID to get the AUTO_INCREMENT value less one immediately after executing the INSERT query. It might not be the best to get AUTO_INCREMENT value using LAST_INSERT_ID in most of the scenarios especially in production.

# Example
INSERT INTO `autoinc`.`user`(`firstName`,`lastName`,`username`) values('Nicolas', 'Dave', 'nicolas' );
SELECT LAST_INSERT_ID() as `id`;

# Result
---------------
id
---------------
6

After executing the above queries, the expected AUTO_INCREMENT value is 7, and calling LAST_INSERT_ID() shows 6 which is correct, but it might not be ideal to use LAST_INSERT_ID() to get the AUTO_INCREMENT value.

Another way to get the AUTO_INCREMENT value is by using the SHOW command as shown below. It also shows the approximate value which might not be the actual value.

# Syntax
SHOW TABLE STATUS FROM `<database>` WHERE `name` LIKE '<table>';

# Example
SHOW TABLE STATUS FROM `autoinc` WHERE `name` LIKE 'user';

# Result
------------------------------
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time		Update_time		Check_time	Collation		Checksum	Create_options	Comment
------------------------------
user	InnoDB	10	Dynamic		4	4096		16384		0		0		0		5		2020-05-26 20:01:31	2020-05-26 20:04:14	NULL		utf8mb4_unicode_ci	NULL

The value of Rows is 4 and Auto_Increment is 5 which is not the actual value. These are approximate values.

To get the exact value, we can use the command SHOW CREATE as shown below.

# Example
SHOW CREATE TABLE user;

# Result
Table	Create Table
------------------------------
user	CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastName` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `username` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

This shows the exact value of the AUTO_INCREMENT attribute.

AUTO_INCREMENT Update Examples

To update the value of AUTO_INCREMENT, we can use the ALTER query as shown below. It's easy to set the AUTO_INCREMENT value of an empty table since MySQL does not need to make a copy of the existing rows to execute the ALTER command. Also, we can either reset AUTO_INCREMENT to 1 or set it to any preferred value to start the sequence.

# Syntax
ALTER TABLE <table> AUTO_INCREMENT = <value>;

# Examples

ALTER TABLE `autoinc`.`user` AUTO_INCREMENT = 1;

ALTER TABLE user AUTO_INCREMENT = 1;

ALTER TABLE `autoinc`.`user` AUTO_INCREMENT = 100000;

Summary

This tutorial provided the queries with examples to get the value of the AUTO_INCREMENT attribute of an InnoDB table. It also provided examples to update the AUTO_INCREMENT attribute.