MySQL: Reset the Next Value in AUTO_INCREMENT column

This MySQL tutorial explains how to reset sequences using the AUTO_INCREMENT attribute in MySQL with syntax and examples.

Description

You can reset the next value assigned by the AUTO_INCREMENT at any time using the ALTER TABLE statement in MySQL.

Syntax

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is:

ALTER TABLE table_name AUTO_INCREMENT = value;

table_name

The name of the table whose AUTO_INCREMENT column you wish to reset.

value

The next value that will be used in the AUTO_INCREMENT column.

Example

Let's look at an example of how to reset the next value assigned to an AUTO_INCREMENT column in a table in MySQL.

For example, if we had a suppliers table that was defined as follows:

CREATE TABLE suppliers
( supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

We could reset the next value in the sequence for the supplier_id field (which is the AUTO_INCREMENT field in the suppliers table) with the following ALTER TABLE statement:

ALTER TABLE suppliers AUTO_INCREMENT = 1;

This example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1 for the supplier_id column in the suppliers table. Now when a new record is inserted into the suppliers table, the supplier_id column will be assigned the value of 1 in the newly created record.