Reset AUTO INCREMENT in MySQL

AUTO_INCREMENT is a useful feature provided in MySQL. It works as follows: it generates a numerical sequence to a column for every new row inserted.
AUTO_INCREMENT generates unique numbers for identification purposes.
We can manually define the starting of this sequence set by auto_increment and reset this value to some other value later. Whenever we reset the auto_increment column’s value to a new value, the sequence is reset again so that the next automatically generated value follows succession.

In this article we will look into:

• How to reset AUTO_INCREMENT in MySQL
• Truncate table reset AUTO_INCREMENT in MySQL
• Reset AUTO_INCREMENT by drop column in MySQL
• Reset AUTO_INCREMENT after Delete in MySQL
• How to reset AUTO_INCREMENT in MySQL workbench

We will start by creating a table, inserting some data into it, and then do a select * to view the inserted data.

# create table 
CREATE TABLE sales_data (
    sale_person_id INT AUTO_INCREMENT,
    sale_person_name VARCHAR(255),
    commission_percentage INT,
    no_products_sold INT,
    PRIMARY KEY(sale_person_id)
);
# insert data
 INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
 VALUES("Aditi",10,200);
 INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
 VALUES("Furan",5,300); 
 INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
 VALUES("Veronica",10,250);
 INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
 VALUES("Atharv",25,150); 
# select *
SELECT * FROM sales_data;

Output :-

How to reset AUTO_INCREMENT in MySQL

We will look into the syntax followed by an example.

Syntax:-

ALTER TABLE <table_name> AUTO_INCREMENT = <value> ;

Where <table_name>  is the name of the table you want to reset the auto increment for a column.<value> is the value you want to reset for the sequence.

Let us have a look at the example. We will be resetting the auto_increment value for the above-created table sales_data to start from 1000.

ALTER TABLE sales_data AUTO_INCREMENT = 1000;

We get the message : ALTER TABLE sales_data AUTO_INCREMENT = 1000 0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0 0.016 sec

Now insert a few more rows and do a select * on sales_data to see the change.

INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Debolina",35,500); 
INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Erick",20,350); 
INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Rasmus",25,355); 
SELECT * FROM sales_data;

Output :-

As we can see in figure 1.2, we were able to reset the auto_increment value successfully, and now all the new rows will be having sale_person_id to be incremented by one starting from 1000.

Truncate table reset AUTO_INCREMENT in MySQL

What if you want to remove all the previous data from a table and then reset the auto_increment value?

The solution is to truncate the table and then set the desired auto_increment value. Observe the below code and output.

TRUNCATE TABLE sales_data;
ALTER TABLE sales_data AUTO_INCREMENT = 1;

We will again insert some data and select * on the sales_data table to see the change.

INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Debolina",35,500); 
INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Erick",20,350); 
INSERT INTO sales_data (sale_person_name,commission_percentage,no_products_sold) 
VALUES("Rasmus",25,355); 
SELECT * FROM sales_data;

Output:-

Note: We can change the auto_increment value to any integer here and not just 1.

Reset AUTO_INCREMENT by drop column in MySQL

Another easy way to reset the auto_increment value is to drop columns and reset the value. In the below example, we will be using the table sales_data created above with rows, as shown in figure 1.1. Observe the below query.

ALTER TABLE sales_data DROP sale_person_id ;
ALTER TABLE sales_data ADD sale_person_id INT PRIMARY KEY AUTO_INCREMENT FIRST;

In the above queries, we are dropping the sale_person_id column itself and then again adding the column, making it the primary key and setting its auto_increment value to start from ‘1’.

Reset AUTO_INCREMENT after Delete in MySQL

What if the most recent entries get deleted, and we would like to reset the auto_increment value? 

We can reset the auto_increment value back to start just after the row’s last id in the table. For example, if the last row had the value of id as ‘7’ and rows with id ‘8’ and ‘9’ were deleted. Inserting a new row to the table will provide the value ’10’ to id unless we execute the below code.

ALTER TABLE sales_data AUTO_INCREMENT=1;

After executing this line, the id of new rows will start from ‘7’ instead of ’10’.

Have a detailed understanding on how to reset auto_increment with examples from : RESET AUTO_INCREMENT AFTER DELETE.

How to reset AUTO_INCREMENT in MySQL workbench

In case we do not want to write queries, we can also set the auto_increment value using the MySQL workbench, we can do it by the below steps.

STEP1: Right-click on the table and select the option Alter Table.

STEP2: Select the Options button.

STEP3: Set the value for Auto Increment and click Apply.

We hope this article helped to build a good understanding of how to reset auto_increment. Good Luck !!