Multiple ways to Reset Auto Increment column to 1 in MySQL

Sometimes, We want to reset the auto-increment column value to 1 for the Mysql table.

In this tutorial, We are going to learn multiple ways to

MySQL provides AUTO_INCREMENT to the column of the table to primary keys. It increments its value by 1 whenever a row is inserted into the table.

Sometimes, We want to reset the auto_increment column value to 1.

#How to set my auto-increment value to start from 1 in MySQL?

There are multiple ways to reset the AUTO_INCREMENT column in MySQL.

One of the approaches is to delete the data using truncate and drop the table using

TRUNCATE TABLE tablename
drop TABLE tablename

Note: truncates deletes data. all rows in a table, drop delete the schema. This deletes entire data in a table. Sometimes, table data is important, then we can choose other approaches.

Another way using modify the table using alter DDL script

If the table does not has data or is empty, Then run the below command

ALTER TABLE tablename AUTO_INCREMENT = 1;

If the database has data in it, then you have to use the below scripts Drop the table column using the below query, if this column has foreign key relation constraints, Then it does not work.

ALTER TABLE tablename DROP column;

Next, add or create a column with the below query

ALTER TABLE tablename ADD column INT() NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (column);

Another approach, some versions of MySQL work with a single line as given below

alter table tablename auto_increment=1, algorithm=inplace;

#How to set initial value and auto increment in MySQL?

This script adds an initial value of 10001 to MySQL database table

Initially create a table with auto_increment key and no default value is specified

create table employee(
id int(5) auto_increment,
name varchar(50),
PRIMARY KEY (id)
)

Next, Set the initial value for a table column using the below query

ALTER TABLE employee AUTO_INCREMENT=10001;

if the table is not defined with auto_increment, Then run the below queries in the defined order Add the column with AUTO_INCREMENT

alter table employee add column id int(5) NOT NULL AUTO_INCREMENT FIRST

next set default value that starts from 10001 s

ALTER TABLE employee AUTO_INCREMENT=10001;

#Conclusion

Posted multiple approaches to reset the auto_increment column value reset to 1 in MySQL tables.