How to Reset Auto Increment in MySQL

MySQL database features Auto Increment that generates a unique sequential ID for each table row. It is a column constraint that allows you to uniquely identify each row in a table. It is commonly applied to primary key fields of database tables. Often database programmers and software developers need to reset auto increment in MySQL. In this article, we will learn how to do this.

What is Auto Increment in MySQL

Auto Increment is a feature available in all database systems, including MySQL, that automatically generates unique IDs for each row in a table. It is a column property that can be set at the time of table creation, or afterwards, by modifying column properties. It generates unique sequential number for each row in a table to uniquely identify it. It is often used for primary key columns of the table.

If you delete a row, its auto increment ID will not be reused. Updating row contents does not change Auto increment value of the row. By default, auto increment value starts from 1 with an increment of 1. Depending on your requirement, you can not only change the starting value but also the increment value.

Why Reset Auto Increment in MySQL

Sometimes database developers and administrators need to reset auto increment’s starting value or increment or both. This may be because of some programming problems or because there have been too many insertions and deletions and your auto increment values are messed up and hard to understand.

For example, let us say you have 5 rows in a table with auto increment column having values 1, 2, 3, 4, 5. Let us say you just deleted a couple of latest rows in your table. Next, when you insert a row in this table, the auto increment column will assign value of 6 to this column and not 4. In this case, you will need to reset auto increment column. This is shown below.

How to Reset Auto Increment in MySQL

There are mainly 3 different ways to reset auto increment in MySQL. We will look at it one by one. Let us say you have the following MySQL table orders with id column having AUTO_INCREMENT constraint.

mysql> create table orders(
     id int auto_increment,
     primary key(id),
     product_name varchar(255),
     order_date date
);

Let us say you insert the following data in the above table.

mysql> insert into orders(product_name,order_date) 
       values('A','2024-10-28'),
             ('B','2024-10-29'),
             ('C','2024-10-30');

mysql> select * from orders;
+----+--------------+------------+
| id | product_name | order_date |
+----+--------------+------------+
|  1 |      A       | 2024-10-28 |
|  2 |      B       | 2024-10-29 |
|  3 |      C       | 2024-10-30 |
+----+--------------+------------+

As you can see, the ID column has been auto populated with unique sequential numbers. When you insert another row in the above table, MySQL will automatically assign id=4 for this new row. But if you delete a row and add a new row then ID will still be assigned value 4. What if you do not want it to be this way? We will see several ways to control the value of auto increment below.

1. Using ALTER Table Statement

ALTER TABLE statement allows you to make changes to the modify the structure of tables and its columns. It can be used to add, remove or update columns. Here is its syntax.

ALTER TABLE table_name AUTO_INCREMENT = new_value;

Let us say you delete row with id=3.

mysql> delete from orders where id=3;

mysql > select * from orders;

Now if you insert a new row to this table, then the ID column will have value of 4 and not 3.

mysql> insert into orders(product_name,order_date) 
       values('D','2024-10-30');

mysql> select * from orders;
+----+--------------+------------+
| id | product_name | order_date |
+----+--------------+------------+
|  1 | A            | 2024-10-28 |
|  2 | B            | 2024-10-29 |
|  4 | D            | 2024-10-30 |
+----+--------------+------------+

Instead, you can reset the auto increment value of ID column to 3, after you delete row with id=3.

mysql> delete from orders where id = 4;

mysql> ALTER TABLE data AUTO_INCREMENT = 3;

Now let us try to insert a new row into data table.

mysql> insert into orders(product_name,order_date)        
       values('D','2024-10-30');

mysql> select * from orders;
+----+--------------+------------+
| id | product_name | order_date |
+----+--------------+------------+
|  1 | A            | 2024-10-28 |
|  2 | B            | 2024-10-29 |
|  3 | D            | 2024-10-30 |
+----+--------------+------------+

Now you will see that the auto increment column has id=3 and it has resumed auto numbering from 3.

2. Using Truncate Table

Another option is to delete all data from your table. Obviously, you can copy the data to another table and copy it back afterwards, thereby resetting your auto increment column.

To delete all rows of a table, use TRUNCATE table command. Here is its syntax.

TRUNCATE TABLE table_name;

It will delete all rows of the table and reset the auto increment column to 0. When you insert a new row to this empty table, it will be assigned id=1.

mysql> truncate table orders;

mysql> select * from orders;
Empty set (0.00 sec)

Now let us try inserting 3 rows, you will see that the ID column starts from 1.

mysql> select * from orders;
+----+--------------+------------+
| id | product_name | order_date |
+----+--------------+------------+
|  1 | A            | 2024-10-28 |
|  2 | B            | 2024-10-29 |
|  3 | D            | 2024-10-30 |
+----+--------------+------------+

Generally, if you delete one or more rows from a table with an auto increment column and then insert some rows in it, you will see that auto increment does not reset to 1. It will continue from its previous maximum value.

But when you use truncate table command to delete all rows from a table, it resets the auto increment. For example, let us try deleting all rows of the above table using delete table command.

mysql> delete from orders;

Now let us insert the above 3 rows once again.

mysql> insert into orders(product_name,order_date)
            values('A','2024-10-28'),
                  ('B','2024-10-29'),
                  ('D','2024-10-30');

mysql> select * from orders;
+----+--------------+------------+
| id | product_name | order_date |
+----+--------------+------------+
|  4 | A            | 2024-10-28 |
|  5 | B            | 2024-10-29 |
|  6 | D            | 2024-10-30 |
+----+--------------+------------+

As you can see, the auto increment value has not been reset. So you should only use TRUNCATE table statement for emptying a table, if you want to reset auto increment column.

3. Using Drop Table with Create Table

In the above method, only the data is deleted but the table’s structure remains intact. You can also use DROP TABLE statement to completely delete the entire table and recreate it using create table.

mysql> drop table orders;

mysql> create table orders(
         id int auto_increment,
         primary key(id),
         product_name varchar(255),
         order_date date
    );

The above method will create a new table with auto increment column starting from 1.

Please note, when you reset auto increment to a specific value, MySQL will check if the auto increment column has that value or not. If you set auto increment to a value that is already present in the column, then MySQL will automatically start after the maximum value of that column. For example, if ID column has values 1, 2, 3, 4, 5 and you reset auto increment column’s value to 3, MySQL will not give any error. But when you insert a new row, ID will be assigned value 6.

Conclusion

In this article, we have learnt several ways to reset auto increment column in MySQL. If you do not want to delete any of the rows of your table, then use ALTER TABLE statement. If you are planning to empty the table and repopulate it, then use TRUNCATE TABLE statement.

Also read:

How to List Users in MySQL
How to Remove Item from JavaScript Array
How to Get Character Set of MySQL