How to Change Auto Increment Value in MySQL

In this tutorial, we are going to see how to change Auto Increment value in MySQL. AUTO_INCREMENT starts at 1 by default, but sometimes you may want them to start at a different number. These numbers are called “sequences”. AUTO_INCREMENT is part of a table definition and is changed using ALTER TABLE statement.

Let’s take an example to see how it works. For this tutorial, we will use the table “Employee”.
 

> SELECT * FROM Employee;

+----------+-----------+--------+-------------------------------+
|   EmpID  |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|     1    |    Alex   |   25   | 819 Saint Francis Way         |
|     2    |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3    |   Jean    |   35   | 188 Clay Street Indiana       |
|     4    |    Bob    |   40   | 285 Java Lane Missouri        |
+----------+-----------+--------+-------------------------------+

EmpID is a column that increments automatically. If you use the statement INSERT to insert a new row into the table without specifying a value for the column EmpID, MySQL will automatically generate a sequential integer for EmpID starting at 1.

To change Auto Increment value in MySQL run the following query:

ALTER TABLE Employee AUTO_INCREMENT = 200;

To check if the Auto_Increment value changed, we will insert a new row in the table “Employee”, to see if the new row will have EmpID = 200.

INSERT INTO Employee (EmpID, Name, Age, Address) 
VALUES (NULL, 'Thomas', 22, '320 Java Lane Missouri ');

Display data in “Employee” table to check the new inserted row.

> SELECT * FROM Employee;

+----------+-----------+--------+-------------------------------+
|   EmpID  |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|     1    |    Alex   |   25   | 819 Saint Francis Way         |
|     2    |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3    |   Jean    |   35   | 188 Clay Street Indiana       |
|     4    |    Bob    |   40   | 285 Java Lane Missouri        |
|    200   |   Thomas  |   22   | 320 Java Lane Missouri        |
+----------+-----------+--------+-------------------------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More