How to reset AUTO_INCREMENT in MySQL?

Question

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

Answer 1

ALTER TABLE tablename AUTO_INCREMENT = 1

Answer 2

There are good options given in How To Reset MySQL Autoincrement Column

Note that ALTER TABLE tablename AUTO_INCREMENT = value; does not work for InnoDB

Answer 3

The best solution that worked for me:

ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

Its fast, works with innoDB, and I don't need to know the current maximum value! This way the auto increment counter will reset and it will start automatically from the maximum value exists.

Answer 4

I tried to alter the table and set auto_increment to 1 but it did not work. I resolved to delete the column name I was incrementing, then create a new column with your preferred name and set that new column to increment from the onset.

Answer 5

I googled and found this question, but the answer I am really looking for fulfils two criteria:

1. using purely MySQL queries
2. reset an existing table auto-increment to max(id) + 1

Since I couldn't find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

1. the table in question is InnoDB
2. the table uses the field id with type as int as primary key
3. the only way to do this purely in MySQL is to use stored procedure
4. my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
5. I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc; 
      EXECUTE stmt; 
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt; 

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

When I run, I simply select the stored procedure and press Run Selection

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/library/sql-queries/#mysql-reset-autoinc and adapted for StackOverflow

Answer 6

The highest rated answers to this question all recommend "ALTER yourtable AUTO_INCREMENT= value". However, this only works when value in the alter is greater than the current max value of the autoincrement column. According to the MySQL documentation:

You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, you can use ALTER TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

In essence, you can only alter AUTO_INCREMENT to increase the value of the autoincrement column, not reset it to 1, as the OP asks in the second part of the question. For options that actually allow you set the AUTO_INCREMENT downward from its current max, take a look at Reorder / reset auto increment primary key.