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.