How to reset AUTO_INCREMENT in MySQL

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;

It's 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.

As of MySQL 5.6 you *can* use the simple ALTER TABLE with InnoDB:

ALTER TABLE tablename AUTO_INCREMENT = 1;

The documentation are updated to reflect this:

*[13.1.7 ALTER TABLE Statement][1]*

My testing also shows that the table is *not* copied. The value is simply changed.

[1]:

[To see links please register here]

It is for an empty table:

ALTER TABLE `table_name` AUTO_INCREMENT = 1;

If you have data, but you want to tidy up it, I recommend to use this:

ALTER TABLE `table_name` DROP `auto_colmn`;
ALTER TABLE `table_name` ADD `auto_colmn` INT( {many you want} ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`auto_colmn`);

The best way is remove the field with AI and add it again with AI. It works for all tables.

The auto-increment counter for a table can be (re)set in two ways:

1. By executing a query, like others already explained:

`ALTER TABLE <table_name> AUTO_INCREMENT=<table_id>;`

2. Using [Workbench][1] or another visual database design tool. I am going to show in Workbench how it is done - but it shouldn't be much different in other tools as well. By right clicking over the desired table and choosing `Alter table` from the context menu. On the bottom you can see all the available options for altering a table. Choose `Options` and you will get this form:

[![Enter image description here][2]][2]

Then just set the desired value in the field `Auto increment` as shown in the image. This will basically execute the query shown in the first option.

[1]:

[To see links please register here]

[2]:

Try to run this query:

ALTER TABLE tablename AUTO_INCREMENT = value;

Or try this query for the reset auto increment

ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL;

And set auto increment and then run this query:

ALTER TABLE `tablename` CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

You need to follow the advice from Miles M's comment and here is some [PHP][1] code that fixes the range in MySQL. Also you need to open up the *my.ini* file (MySQL) and change max_execution_time=60 to max_execution_time=6000; for large databases.

Don’t use "ALTER TABLE tablename AUTO_INCREMENT = 1". It will delete everything in your database.

```lang-php
$con = mysqli_connect($dbhost, $dbuser, $dbpass, $database);
$res = mysqli_query($con, "select * FROM data WHERE id LIKE id ORDER BY id ASC");

$count = 0;
while ($row = mysqli_fetch_array($res)){
$count++;

mysqli_query($con, "UPDATE data SET id='".$count."' WHERE id='".$row['id']."'");
}
echo 'Done reseting id';
mysqli_close($con);
```

[1]:

[To see links please register here]

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.

[![enter image description here][1]][1]

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

[![enter image description here][2]][2]

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

[![enter image description here][3]][3]

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

[To see links please register here]

and adapted for Stack Overflow.

[1]:

[2]:

[3]:

If you're using `PHPStorm's database tool` you have to enter this in the `database console`:

ALTER TABLE <table_name> AUTO_INCREMENT = 0;

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;