Reset ID autoincrement ? phpmyadmin
PhpMysqlPhp Problem Overview
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 ?
Php Solutions
Solution 1 - Php
ALTER TABLE `table_name` AUTO_INCREMENT=1
Solution 2 - Php
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 - Php
ALTER TABLE xxx
AUTO_INCREMENT =1;
or
clear your table by TRUNCATE
Solution 4 - Php
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.
Solution 5 - Php
I have just experienced this issue in one of my MySQL db's and I looked at the phpMyAdmin answer here. However the best way I fixed it in phpMyAdmin was in the affected table, drop the id column and make a fresh/new id column (adding A-I -autoincrement-). This restored my table id correctly-simples! Hope that helps (no MySQL code needed-I hope to learn to use that but later!) anyone else with this problem.