JIYIK CN >

This tutorial shows you how to reset the auto increment in a MySQL table.

Most businesses and organizations using MySQL need to insert values ​​into multiple tables simultaneously. While it is not possible to make all fields of the inserted records non-null, it may cause errors or raise errors when retrieving these records based on specific conditions.

To solve this problem, MySQL provides us with an auto increment field. It helps us to add or update a specific value in MySQL every time we insert a new record. It is usually used for columns associated with primary keys.

As the value of this variable increases, the analyst must control the value of this variable. This can be ALTER TABLEdone using the statement.

Let us try to understand this a little deeper.

However, before we get started, we'll create two virtual tables to work with. Here, we create a table student_dates_1, and 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)
);

Use INSERTthe statement student_datesto insert an entry into the table.

The previous query created a student_datestable called . Now INSERTwith the help of the statement, let's try to add data for some students. This 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 above code will student_datesenter the student data in the table.

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

Reset AUTO_INCREMENTthe value of

To AUTO_INCREMENTupdate the value of a variable to a specific value, you can use the following syntax.

ALTER TABLE name_of_the_table AUTO_INCREMENT = x;

In the above query, xrepresents the updated values ​​that need to be added to the said table. This logic can be used to update student_dates_3the variable values ​​of a table with the help of the following query.

ALTER TABLE student_dates_3 AUTO_INCREMENT = 100;

If we add a record, the primary key stu_idis automatically updated to AUTO_INCREMENTthe value set in the variable. Now let's student_dates_3insert a value into the 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 set the for Rutvikthe student named to . This can be checked with the following query.stu_id100

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

Therefore, with the help of ALTER TABLEthe INSERT statement and AUTO_INCREMENTthe UPDATE keyword, we can effectively update AUTO_INCREMENTthe value of the field, thereby automatically updating the values ​​of the records in the columns associated with the primary key.