How can I reset the AUTO_INCREMENT of a field?
I want it to start counting from 1 again.
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.
In a table with the Aria storage engine the auto_increment value can be set to any value, even lower than that of the current maximum. The next insert however will use the next available value (max + 1) ignoring the set value. If set to a higher value it will continue to use and increment from that. The documentation is not particular clear on that but this was observed with Mariadb 10.11.3
See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.