Is it possible to reset all auto increment in a schema in one query? if not even just by per table will do. btw I am using MySQL server 8 thanks in advance :)

  • 1
    This is not normally something to do with AUTO_INCREMENT; please explain the use case. We may have a simpler way to achieve the purpose. – Rick James Mar 17 at 22:13

Autoincrement is seeded by certain number by ALTER TABLE query that can operate one and only one table at the time. So you have to run the ALTER TABLE mytable AUTO_INCREMENT=1; for each and every table in your scheme having the autoincremented columns.


If the tables already have rows, you can't do it.

If you want to throw away all the data, then you could do TRUNCATE TABLE for each table.

Or you could get all the table definitions via mysqldump --no-data and make sure there are DROP TABLE for each. Then have a script that runs the output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.