Reset ID autoincrement ? phpmyadmin [duplicate]
I was testing some data in my tables of my database, to see if there was any error, now I cleaned all the testing data, but my id (auto increment) does not start from 1 anymore, can (how do) I reset it ?
Solution 1:
ALTER TABLE `table_name` AUTO_INCREMENT=1
Solution 2:
You can also do this in phpMyAdmin without writing SQL.
- Click on a database name in the left column.
- Click on a table name in the left column.
- Click the "Operations" tab at the top.
- Under "Table options" there should be a field for AUTO_INCREMENT (only on tables that have an auto-increment field).
- Input desired value and click the "Go" button below.
Note: You'll see that phpMyAdmin is issuing the same SQL that is mentioned in the other answers.
Solution 3:
ALTER TABLE xxx AUTO_INCREMENT =1;
or
clear your table by TRUNCATE
Solution 4:
I agree with rpd, this is the answer and can be done on a regular basis to clean up your id column that is getting bigger with only a few hundred rows of data, but maybe an id of 34444543!, as the data is deleted out regularly but id is incremented automatically.
ALTER TABLE users DROP id
The above sql can be run via sql query or as php. This will delete the id column.
Then re add it again, via the code below:
ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
Place this in a piece of code that may get run maybe in an admin panel, so when anyone enters that page it will run this script that auto cleans your database, and tidys it.