How to Reset Auto Increment in MySQL

MySQL allows you to create auto increment columns in database tables. They have an initial value of 1 and automatically increment by 1 for each new row inserted into table. Auto increment columns are typically used for primary key columns. Sometimes you may need to reset auto increment in MySQL tables. This is especially true if there have been too many insertions and deletions in your database tables and you want to simply start the column value from 1 again. In this article, we will learn how to reset auto increment in MySQL.

How to Reset Auto Increment in MySQL

Let us say you have the following database table.

CREATE TABLE sales (
    id int NOT NULL AUTO_INCREMENT,
    sale int
    sale_date datetime
    PRIMARY KEY (id)
);

In the above table the column id’s value starts from 1 and increments by 1 at a time for each new row inserted into your table.

If you want to reset this column’s value to 1, just run the following command.

ALTER TABLE tablename AUTO_INCREMENT = 1

Here is the SQL query to reset id column in sales table to 1 again.

ALTER TABLE sales AUTO_INCREMENT = 1

On the other hand if you want to start this column from another value such as 100, replace 1 above with 100.

ALTER TABLE sales AUTO_INCREMENT = 100

But there are a couple of important things to keep in mind before you reset auto increment column.

First of all, for InnoDB databases, you cannot set the auto increment value that is lower than or equal to the highest current value of the column. For example, if the maximum value of id column is 100 then you cannot reset auto increment value to anything less than or equal to 100, even if you have deleted all other rows in your table and it has only 1 row.

Secondly, please note that when you reset auto increment column, MySQL will create another empty table with same structure and new auto increment value, copy all rows from original table to the new table, drop original table and then rename the new table. So this can be very time consuming if your table is large. Of course, recent MySQL versions such as MySQL 8+ have optimized this query and now it is considerably fast even for large tables, but it might be slow on older versions.

Moving on, you can also reset auto increment value to a dynamic one if you want. Let us say you have another table orders(id, order_name, order_amount) and you want to reset the auto increment of sales table to maximum value of id column in orders tables.

You can do so using prepared statements.

SELECT @max := MAX(ID)+ 1 FROM orders;

PREPARE stmt FROM 'ALTER TABLE sales AUTO_INCREMENT = ?';
EXECUTE stmt USING @max;

DEALLOCATE PREPARE stmt;

In the above statements, we first calculate the maximum value of id column in orders table and store it in @max variable. Then we run the ALTER TABLE statement to reset auto increment for sales table. In that statement we use @max variable.

Depending on your MySQL version, some people may get the following error.

You have an error in your SQL syntax ... near '?' at line 1

In such cases, use the following stored procedure instead.

CREATE PROCEDURE reset_autoincrement
BEGIN
      SELECT @max := MAX(ID)+ 1 FROM orders;
      set @alter_statement = concat('ALTER TABLE sales AUTO_INCREMENT = ', @max);
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END

In this article, we have learnt how to reset auto increment in MySQL table. You can customize these queries as per your requirement. But please keep in mind the couple of things we have mentioned about resetting auto increment above.

How to Reset Auto Increment in MySQL

How to Reset Auto Increment in MySQL

Related posts:

Leave a Reply Cancel reply