Advertisement
Advertisement


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.

2020/04/19
1
1285
4/19/2020 11:13:56 AM

Accepted Answer

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that 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, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

See How to Reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

2017/05/23
2082
5/23/2017 11:47:29 AM

ALTER TABLE tablename AUTO_INCREMENT = 1
2015/04/04

SET  @num := 0;

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

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

2014/05/04

Simply like this:

ALTER TABLE tablename AUTO_INCREMENT = value;

reference: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

2012/01/19

enter image description hereThere is a very easy way with phpmyadmin under the "operations" tab, you can set, in the table options, autoincrement to the number you want.

2014/05/03

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.

2014/09/16

Source: https://stackoverflow.com/questions/8923114
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]