How to initialize an AUTO_INCREMENT column in MySQL
- How-Tos FAQs
- February 1, 2019
The value of AUTO_INCREMENT can be reinitialized using the below methods.
- ALTER TABLE… AUTO_INCREMENT
- TRUNCATE TABLE
Let us consider the table below.
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50)
);
INSERT INTO student(student_name)
VALUES ('Jack');
INSERT INTO student( student_name)
VALUES ('Rose');1. ALTER TABLE… AUTO_INCREMENT
We can use this method if we need to set the value of AUTO_INCREMENT to a new value that is greater than the current value. We cannot set it at less than the current value.
-- Reset AUTO_INCREMENT to 100
ALTER TABLE student AUTO_INCREMENT = 100;
INSERT INTO student( student_name)
VALUES ('John');
-- This will not work since value of new AUTO_INCREMENT is less than current value
ALTER TABLE student AUTO_INCREMENT = 1;
-- Output
# student_id student_name
--------------------------------
1 Jack
2 Rose
100 John2. TRUNCATE TABLE
If we need to reset the AUTO_INCREMENT to 1 or some value less than the current value, we only have the option of TRUNCATE TABLE. This operation will delete all the data from the table and reset the AUTO_INCREMENT to 1. We can use this only if the table has test data or unnecessary data.
TRUNCATE TABLE student;
INSERT INTO student( student_name)
VALUES ('John');
SELECT * FROM student;
-- The student_id is reset to 1
# student_id student_name
--------------------------------
1 John