How To Reset Auto-increment in MySQL?

In this tutorial, we will see how can we reset the value of the auto-increment column in MySQL using multiple methods.

Before We Start

The term “reset auto-increment” can have two different meanings. First, you are looking for resetting the value of the auto_increment column from x to y. On the other hand, you might be looking to remove the auto_increment property from the column and reset it to the normal form.

In this tutorial, we will see methods to solve both issues.

We can reset the value of the auto_increment column to any desired value. However, the new value can not be smaller than the current highest auto_increment value as it is not allowed for the InnoDB.

On the other hand, if you want to reset the auto_increment value back to 1 then you might require to delete all data from your table. However, it is not recommended.

Reset Auto-Increment Value Examples

Before moving toward the methods to reset the auto_increment value, we will create a table and insert some data into it.

CREATE TABLE cars(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
price INT
);

INSERT INTO cars(name,price)VALUES("Toyota",200),("Tesla",150),("Maruti",300);Code language: SQL (Structured Query Language) (sql)

The table has been created successfully. Let’s display the data now.

SELECT * FROM cars;

As you can see, the value of the id column is increasing automatically because it is set to auto_incerment property.

Reset Using Alter Table

The first method we have is by using the ALTER TABLE statement.

Note that, we can reset the value of the auto_incremtn column to whatever you want. However, the new value must be greater than the current highest auto_increment value.

Let’s try inserting a new row into the cars table after updating the auto_increment id to 5.

The syntax to reset the auto_increment value is-

ALTER TABLE table_name AUTO_INCREMENT = value;Code language: SQL (Structured Query Language) (sql)

As stated earlier, the value can be anything greater than the current highest auto_increment value.

Let’s update the auto_increment value and insert a new row.

ALTER TABLE cars AUTO_INCREMENT = 5;

INSERT INTO cars(name,price)values("Ford",120);

INSERT INTO cars(name,price)values("Porsche",420);Code language: SQL (Structured Query Language) (sql)

Here, we have updated the auto_increment value and then inserted two rows to check if the auto_increment value is going the right way.

Let’s print the table data to check if it is working.

SELECT * FROM cars;Code language: SQL (Structured Query Language) (sql)

As you can see, we have got new auto_increment values.

Reset Auto-Increment Using Truncate Table

The simplest way to reset the value of auto_increment id to 1 is to use the TRUNCATE TABLE statement.

The TRUNCATE TABLE statement will delete all data from the table and reset the auto-increment value back to 1.

Removing Auto-Increment Attribute

To remove the auto_increment attribute, we can use the ALTER TABLE statement.

Here, we will remove the auto_increment attribute from the ‘cars’ table.

First, let’s see the table description where you can clearly see the table column has the auto_increment property.

DESC cars;Code language: SQL (Structured Query Language) (sql)

 ALTER TABLE cars MODIFY id INT;Code language: SQL (Structured Query Language) (sql)

Here, we have simply modified the id column and have re-written it without the auto_increment attribute.

Let’s see the table description now.

DESC cars;Code language: SQL (Structured Query Language) (sql)

As you can see, there is no auto_increment attribute now to the id column.

Conclusion

In this tutorial, we have learned how to reset the auto_increment value in various ways. We also learned to remove the auto_increment attribute from the column.