Not that I'm aware of. There may be a mySQL command for it, but I've not found it.
I had to do this once, and what I did was rename the table (phpMyAdmin), do a structure dump, create a new table with the original name, then delete the old table... bing, reverted auto_inc.
The downside, is that it's a completed empty table. Which, for me, wasn't a problem.
Mark Hensler
If there is no answer on Google, then there is no question.
Mark Hensler posted this at 08:46 — 22nd March 2003.
typically you dont want to reset the auto increment though as it provides a logical order to your records, unless you empty the table. emptying the table (DELETE FROM tablename) will reset the incrementer to zero.
resetting it manually with existing records, i assume, would be smart enough to skip existing id's, but dont count on your id's to have any meaning. (for example why would user id 3 who joined today come before user id 20 who joined 3 years ago?)
I did it on a table with no data in it. I just wanted to know how to do it so that if there are no records in the table and a user goes to add in info the auto increment value will be reset to 1. This could be the case after 13 entries (13 altogether) are deleted..
You do need to be careful. If you have tables linked you need to make sure that you delete the data in the other tables or you data will get very mixed up.
Things to ponder:
Why is it: SELECT * from tablename
and DELETE from tablename
It seems that it should be: DELETE * from tablename
I have typed that one in a few times.
Mark Irving I have a mind like a steel trap; it is rusty and illegal in 47 states
Mark Hensler posted this at 20:02 — 4th April 2003.
It kept bugging me that when a row gets deleted the autoindex increments anyways (instead of filling the lowest number, it proceeds from where it last left off)...it's probably safer this way (as a way to ensure the index is unique and not being used anywhere else), but it ends up making datafiles hard to read because the index numbers jump around (i.e., are not in sequential order)...
At any rate, the way to get phpMyadmin to reset the autoindex to the lowest number available, go to the "Structure" page, then click on "Operations" and then click on "Defragment Table". You can check to see that it worked by going back to the Structure page and looking for the box/line that says "Next Autoindex" under "Row Statistic".
I'd like to be able to defragment each time I do a delete on a table, so that my index numbers remain sequential - does anyone know how to do that?
Hi mijator, Welcome to TWF.
This is a very old post, you should really start your own thread asking your question.
Quick answer: not defrag but you can write a script which takes all the info into an array, clears the database row then reinstall the info, if you have an auto increment index the numbers will start at 1 again.
There is no real need to do this however as the info, if sorted by a loop will display the results sequential anyway.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.
Mark Hensler posted this at 08:43 — 22nd March 2003.
He has: 4,048 posts
Joined: Aug 2000
Not that I'm aware of. There may be a mySQL command for it, but I've not found it.
I had to do this once, and what I did was rename the table (phpMyAdmin), do a structure dump, create a new table with the original name, then delete the old table... bing, reverted auto_inc.
The downside, is that it's a completed empty table. Which, for me, wasn't a problem.
Mark Hensler
If there is no answer on Google, then there is no question.
Mark Hensler posted this at 08:46 — 22nd March 2003.
He has: 4,048 posts
Joined: Aug 2000
hehe.. maybe I should do more research before answering next time. Took only 30 seconds to find the answer via the search at mysql.com
"ALTER TABLE tbl_name AUTO_INCREMENT = 1;"
It was in the user comments, not in the official docs.
Mark Hensler
If there is no answer on Google, then there is no question.
nuk3 posted this at 08:49 — 22nd March 2003.
They have: 238 posts
Joined: May 2002
Thanks allot Mark
ROB posted this at 21:30 — 1st April 2003.
They have: 447 posts
Joined: Oct 1999
typically you dont want to reset the auto increment though as it provides a logical order to your records, unless you empty the table. emptying the table (DELETE FROM tablename) will reset the incrementer to zero.
resetting it manually with existing records, i assume, would be smart enough to skip existing id's, but dont count on your id's to have any meaning. (for example why would user id 3 who joined today come before user id 20 who joined 3 years ago?)
nuk3 posted this at 07:37 — 3rd April 2003.
They have: 238 posts
Joined: May 2002
I did it on a table with no data in it. I just wanted to know how to do it so that if there are no records in the table and a user goes to add in info the auto increment value will be reset to 1. This could be the case after 13 entries (13 altogether) are deleted..
ROB posted this at 09:51 — 3rd April 2003.
They have: 447 posts
Joined: Oct 1999
oh, in that case simply "DELETE FROM tablename;" will empty the table and reset the counters
nuk3 posted this at 13:35 — 4th April 2003.
They have: 238 posts
Joined: May 2002
Yep
mairving posted this at 15:26 — 4th April 2003.
They have: 2,256 posts
Joined: Feb 2001
You do need to be careful. If you have tables linked you need to make sure that you delete the data in the other tables or you data will get very mixed up.
Things to ponder:
Why is it:
SELECT * from tablename
and
DELETE from tablename
It seems that it should be:
DELETE * from tablename
I have typed that one in a few times.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Mark Hensler posted this at 20:02 — 4th April 2003.
He has: 4,048 posts
Joined: Aug 2000
There is no wildcard because you can't delete a single field from a recordset. That would be an UPDATE query setting the field to NULL.
mijator posted this at 23:29 — 10th March 2005.
They have: 5 posts
Joined: Mar 2005
It is possible...had to do it recently...
It kept bugging me that when a row gets deleted the autoindex increments anyways (instead of filling the lowest number, it proceeds from where it last left off)...it's probably safer this way (as a way to ensure the index is unique and not being used anywhere else), but it ends up making datafiles hard to read because the index numbers jump around (i.e., are not in sequential order)...
At any rate, the way to get phpMyadmin to reset the autoindex to the lowest number available, go to the "Structure" page, then click on "Operations" and then click on "Defragment Table". You can check to see that it worked by going back to the Structure page and looking for the box/line that says "Next Autoindex" under "Row Statistic".
I'd like to be able to defragment each time I do a delete on a table, so that my index numbers remain sequential - does anyone know how to do that?
Thx,
Mij
Busy posted this at 09:44 — 11th March 2005.
He has: 6,151 posts
Joined: May 2001
Hi mijator, Welcome to TWF.
This is a very old post, you should really start your own thread asking your question.
Quick answer: not defrag but you can write a script which takes all the info into an array, clears the database row then reinstall the info, if you have an auto increment index the numbers will start at 1 again.
There is no real need to do this however as the info, if sorted by a loop will display the results sequential anyway.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.