How to restart the AUTO_INCREMENT in a MySQL database

Other Questions MySQL
1

I’m looking for guidance on how to reset the AUTO_INCREMENT value of a column in my MySQL table. Specifically, I want it to start at 1 again after removing several records.

CREATE TABLE items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(100),
    cost DECIMAL(10,2)
);

-- After several records have been added and some deleted, I need the item_id to begin from 1 again.
-- What is the correct method to reset the AUTO_INCREMENT value?

I’ve attempted a few solutions, but I’m uncertain about the correct approach. Any tips on how to reset it would be greatly appreciated!

2

The ALTER TABLE approach mentioned works, but there’s an important caveat to consider. If you still have existing records in your table, MySQL will automatically adjust the AUTO_INCREMENT value to be one higher than the current maximum ID value, regardless of what you specify. So if your highest item_id is currently 25, setting AUTO_INCREMENT = 1 will actually result in it being set to 26. To truly restart from 1, you’d need to either truncate the table completely (TRUNCATE TABLE items) which removes all data but resets the counter, or manually update your existing IDs first. I learned this the hard way when trying to reorganize a testing database and couldn’t figure out why the counter wouldn’t reset properly despite running the ALTER command multiple times.

3

you can do ALTER TABLE items AUTO_INCREMENT = 1; and it restarts the count. i had this same problem few weeks ago, just be sure to back up your data incase!

4

Another approach worth mentioning is using the TRUNCATE command if you want to completely clear the table and reset the AUTO_INCREMENT simultaneously. Running TRUNCATE TABLE items; will remove all rows and automatically reset the AUTO_INCREMENT back to 1 in a single operation. This is more efficient than DELETE followed by ALTER TABLE, especially for larger tables. However, keep in mind that TRUNCATE cannot be rolled back and will reset any table statistics, so make sure you have backups if needed. I’ve used this method several times during development when I needed to reset test data completely. The main difference from DELETE is that TRUNCATE doesn’t fire triggers and is generally faster, but it’s an all-or-nothing approach.