How can I reset the AUTO_INCREMENT
of a field?
I want it to start counting from 1
again.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
alter table yourTableName AUTO_INCREMENT=1; truncate table yourTableName; After doing the above two steps, you will get the primary key beginning from 1.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero.
SET @num := 0; UPDATE your_table SET id = @num := (@num+1); ALTER TABLE your_table AUTO_INCREMENT =1;
I think this will do it
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 can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With