Blog

In this Chapter, we will explain how to MySQL Reset the Next Value in AUTO_INCREMENT column 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_nameThe name of the table whose AUTO_INCREMENT column you wish to reset.valueThe 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.TIP: Because there can only be one AUTO_INCREMENT field in a table, you only need to specify the table name (and not the field name) in the ALTER TABLE statement when resetting the next value in the sequence.