Reset Auto-Increment – MySQL

Good morning to all,
Here I going to demonstrate how to reset auto-increment value in MySql. Assign to auto-increment attributes to a column of a table, to generate unique identity for the new row. If new row is inserted it incrementsthe value one.

Whenever you insert a new row into a table, MySQL automatically assigns a sequence number to the auto-increment column. For example, if the table has 5 rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically inserts a new row with id value 6.

Some times you want to reset auto-increment value in mysql, first create table and insert the values to database after that we try to reset the AUTO-INCREMENT values in mysql.

	CREATE TABLE Employee_table (
		Employee_Id int(11) NOT NULL AUTO_INCREMENT,
		Employee_Name varchar(200) DEFAULT NULL,
		Employee_Age int(3),PRIMARY KEY (id)
	);

Insert some sample data into Employee_table.

INSERT INTO Employee_table(Employee_Name,Employee_Age)
			VALUES('Ammu',24),
			('Appu',21),
			('Achu',45);

Now Employee_table is created and three rows inserted into it.

The table like:

select * from Employee_table;

Employee_Id	Employee_Name	Employee_Age
1	Ammu	24
2	Appu	21
3	Achu	45

Let’s check Delete all the records in Employee_table.

delete FROM Employee_table

After delete all the data from Emplyee_table, now if you insert any new value it auto-increment the value to 4. For solving this problem we reset the auto-increment values in mysql

How to Reset Auto-Increment In MySql

To reset the auto-increment values in mysql table using three method. They are:

• Method 1: Alter Command
• Method 2: Truncate
• Method 3: DROP TABLE and CREATE TABLE statements.

Method 1: Alter Command

To reset MySql Auto-increment through Alter Command.

ALTER TABLE Employee_table AUTO_INCREMENT = 1;

It resets the auto-increment value to 1. So can specify any other value as well.

Method 2: Truncate

The TRUNCATE TABLE statement removes all the data of a table and reset auto-increment value to zero.

truncate table Employee_table

Method 3: DROP TABLE and CREATE TABLE statements

DROP TABLE and CREATE TABLE to reset the auto-increment column

DROP TABLE table_name;
CREATE TABLE table_name(…);

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.