How to reset AUTO_INCREMENT in MySQL

How can I ***reset*** the `AUTO_INCREMENT` of a field?

I want it to start counting from `1` again.

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.

ALTER TABLE `table_name` DROP `id`;

ALTER TABLE `table_name` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`) ;


Shortly,First we deleted id column then added it with primary key id again...

As of MySQL 5.6 the approach below works faster due to [online DDL](

[To see links please register here]

) (note `algorithm=inplace`):

```alter table tablename auto_increment=1, algorithm=inplace;
```

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 8 documentation][1]:

> You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and 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 AUTO_INCREMENT column value plus one.

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

[To see links please register here]

.

[1]:

[To see links please register here]

SET @num := 0;
UPDATE your_table SET id = @num := (@num+1);
ALTER TABLE your_table AUTO_INCREMENT =1;

Simply like this:

ALTER TABLE tablename AUTO_INCREMENT = value;

Reference: *[13.1.9 ALTER TABLE Statement][1]*

[1]:

[To see links please register here]

I suggest you to go to [Query Browser][1] and do the following:

1. Go to schemata and find the table you want to alter.

2. Right click and select copy create statement.

3. Open a result tab and paste the create statement their.

4. Go to the last line of the create statement and look for the Auto_Increment=N,
(Where N is a current number for auto_increment field.)

5. Replace N with 1.

6. Press **<kbd>Ctrl</kbd> + <kbd>Enter</kbd>**.

*Auto_increment* should reset to one once you enter a new row in the table.

I don't know what will happen if you try to add a row where an *auto_increment field* value already exist.

[1]:

[To see links please register here]

ALTER TABLE news_feed DROP id

ALTER TABLE news_feed ADD id BIGINT( 200 ) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id)

I used this in some of my scripts. The id field is dropped and then added back with previous settings. All the existent fields within the database table are filled in with the new auto increment values. This should also work with [InnoDB][1].

**Note that all the fields within the table will be recounted and will have other ids!!!.**


[1]:

[To see links please register here]

![Enter image description here][1]

There is a very easy way with [phpMyAdmin][2] under the "operations" tab. In the table options you can set autoincrement to the number you want.

[1]:

[2]:

[To see links please register here]