MySQL: (Re)set the auto-increment value of a table

| Posted: - Last updated:
| Tagged general blog databases mysql development
| ~200wrds (~1min)

Sometimes it’s necessary to set the starting point of a MySQL auto-increment value.

Normally, MySQL starts auto-incrementing at 1. But let’s say you want to start at 10.000, because you want at least a five figure number. You can use the following query to set the MySQL auto-index:


If you want to delete all records from your table and restart auto-index at 1, you might be tempted to run a DELETE query, followed by the above example, setting the auto increment value to 1. There is a shortcut, however:


This will basically reset the table, deleting all data and resetting the auto increment index. Do not that the truncate command is a hard-reset option. For instance, any triggers “ON DELETE” will not be fired when using truncate.

My site is free of ads and trackers. I record privacy-respecting usage statistics with Fathom.

Was this post helpful to you? Why not ☕ Buy me a coffee or use Brave Browser to send a tip.