How to Reset Auto Increment in MySQL

This tutorial will introduce how to reset auto increment in the MySQL table.

Most businesses and organizations that use MySQL need to insert values into multiple tables simultaneously. While it is not possible to have all the fields of the records inserted to be non-empty, it might cause bugs or throw an error at the time of fetching these records based on a particular condition.

To resolve this issue, MySQL assists us with an auto-increment field. It helps us add or update a particular value in MySQL every time a new record is inserted. It is generally used for the column associated with the primary key.

As the value of this variable keeps increasing, it becomes essential for the analyst to have control over the value of this variable. It can be done with the ALTER TABLE statement.

Let us try to understand this in greater depth.

However, we create two dummy tables to work with before we begin. Here we create a table, student_dates_1, along with a few rows.

-- create the table student_dates_3
CREATE TABLE student_dates_3(
  stu_id int NOT NULL AUTO_INCREMENT,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_date date,
  primary key(stu_id)
);

Inserting Entries in the student_dates Table Using the INSERT Statement

The previous query creates a table with the name student_dates. Now with the help of the INSERT statement, let us try to add data for a few students. This operation can be done as follows:

-- insert rows to the table student_dates_3
INSERT INTO student_dates_3(stu_firstName,stu_date) 
 VALUES("Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
 ("Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
 ("Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
 ("Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
 ("Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y'));

The aforementioned code would enter the student data in the table student_dates.

SELECT * from student_dates_3;

Output:

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

Resetting the Value of AUTO_INCREMENT

To update the value of the AUTO_INCREMENT variable to a particular value, one can use the following syntax.

ALTER TABLE name_of_the_table AUTO_INCREMENT = x;

In the aforementioned query, x represents the updated value that needs to be added in the stated table. This logic can be used to update the variable’s value for the student_dates_3 table with the help of the following query.

ALTER TABLE student_dates_3 AUTO_INCREMENT = 100;

The primary key stu_id is automatically updated with the value set in the AUTO_INCREMENT variable if we add records. Now let us insert a value in the student_dates_3 table.

-- insert rows to the table student_dates_3
INSERT INTO student_dates_3(stu_firstName,stu_date) 
 VALUES("Rutvik",STR_TO_DATE('16-January-2001', '%d-%M-%Y'));

We will have the stu_id for the student named Rutvik as 100. This can be checked with the following query.

SELECT * from student_dates_3;

Output:

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19
6		Rutvik			2001-16-01

Thus with the help of the ALTER TABLE statement and the AUTO_INCREMENT keyword, we can efficiently update the value of the AUTO_INCREMENT field to automatically update values of the records in the column associated with the primary key.