Reset ID autoincrement ? phpmyadmin

Question

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 ?

score 160 · Accepted Answer · answered Jun 11 '10 at 01:49

160

ALTER TABLE `table_name` AUTO_INCREMENT=1

answered Jun 11 '10 at 01:49

Sev

15,401
9
56
75

12

Actually `ALTER TABLE tablename AUTO_INCREMENT = 0` worked best for me otherwise the new record was added with id = 2. – Neil Robertson Dec 12 '16 at 05:35
Mickey answer below helped me the best https://stackoverflow.com/a/16114913/1123816 – Jonathan Safa Sep 15 '17 at 16:55

score 90 · Answer 2 · answered Jan 16 '12 at 20:35

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.

score 17 · Answer 3 · answered Jun 11 '10 at 01:50

17

ALTER TABLE xxx AUTO_INCREMENT =1; or clear your table by TRUNCATE

answered Jun 11 '10 at 01:50

Chen

171
3

Mikeys4u · Answer 4 · 2013-04-22T08:08:11.890

13

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.

edited Apr 22 '13 at 08:08

answered Apr 19 '13 at 23:33

Mikeys4u

1,494
18
26

1

+1 good solution, since -in my case- if the table still contains some data (first 2 user) the auto inc will not be altered. But with your solutions, it will re-arrange all indexes. – Mohammed Joraid Jun 08 '13 at 20:31
4

"_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_"? Am I the only one to see, that this is madness? The OP asked (I think) about **empty** table! Your "_regular basis_" suggests about doing this regular on table full of data. How can you imagine that? What about relations (other tables, that uses ID from this table?) If you just drop the `id` and recreate it, you're completely changing ids of your records. You can, as well, stop using `id` column at all! – trejder Apr 03 '14 at 13:30
Every situation is different....I have only 8000 rows and my id got to 9 or 10 numbers long....there are no relations, and just picked the latest results (highest id). This is done once a month on a scheduled task, after 18 months never had a problem... – Mikeys4u Apr 03 '14 at 18:34
This worked perfectly for my situation! Thank you!!! – Jonathan Safa Sep 15 '17 at 16:54
1

This option would only be good to do on a regular basis if there's a constant turn over of data in the database and the id's are only relevant for a single session. or if the data is always being used in a last in/first out fashion. but like Trejder said if the id's are used to link or relate the data here to other tables or codes doing what you said will break everything. So I can see how in some situations it could be okay, but you gotta be careful as if you over use this you could cause your program to break in weird unexpected ways that likely will not leave meaningful errors to help out. – Kit Ramos Oct 05 '17 at 04:08

score -1 · Answer 5 · answered Dec 10 '12 at 11:09

-1

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.

answered Dec 10 '12 at 11:09

rpd

1,135
4
15
24

the sql that phpMyAdmin uses to do what your telling it to do is essentially Mikeys4u answer just above this. And you can see my comment up there as to why while this will be okay in some settings it's not a cure-all – Kit Ramos Oct 05 '17 at 04:14

Reset ID autoincrement ? phpmyadmin

5 Answers5

Linked