Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

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.

  1. ALTER TABLE… AUTO_INCREMENT
  2. 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 John

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

2. 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

Up Next:

Read How to use IF condition in SQL SELECT Statement?

More Resources We Think You Might Like

SQL FAQ Feat

How to avoid SQL injection in PHP?

SQL injection is one of the most common vulnerabilities in a web application. We can avoid it in ...

  • How-Tos FAQs
  • December 15, 2018
SQL FAQ Feat

How to concatenate data from different rows of ...

Let us consider the following table: CREATE TABLE fruits (     ID INT,     NAME VARCHAR(50) ) INS...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to add a column with a default value to an ...

To SQL add a column with a default value is a simple operation in SQL. Let us set up a ‘student’ ...

  • How-Tos FAQs
  • December 17, 2018