Posted by Gleep on 05/06/06 06:28

Try to avoid situations where you need and autonumber id field to be sequencially perfect. However
you might be dealing with an odd situation.

I found that the best thing to do. Is to do a table backup. You don't have to back up the entire db
just the table in question.

first do a full backup of your table.
then do a data export of the same table. WHen exporting data I like to use the ~ as the separator
because the , may be contained in some data fields and scews up the field separation.
next open up that exported data in excel. Go to tools > import text file make sure to tell excell
the separator is ~
you will see all your data - hopefully the primary id autoincrement is the very first column. Do a
sort on that column adn then delete that column, save that file
now you delete or drop table from your db.
Then open up the saved exported table in a word processor and copy the sql that build the table
structure, copy and paste that code into the SQL section of phpadmin and go, this will recreate the
structure of the table.
Next go to the bottom of that page and you will see, insert text file, select that and you are
taken to a page/form to upload data. Make to set ~ as the separator and lower on the page select the
checkbox that says "local" or the upload sometimes won't work.
Then your data is reinserted and autonumberd starting from 0.

Note: if you have a huge amount of data in the data text file, you can only upload 2 megs at a time.
In situations like that, use you editor to split the file in half and upload each section.

This process is easy to do, sounds much worse that it really is, However there is potential for
disaster. Try practising on a home server first. get it down. If all else fails at least you have a
full and complete back up of the data and you can dump that table back and get back to where you
started.





On Wed, 26 Apr 2006 22:24:23 -0700, cover <coverlandNOSPAM914@yahoo.com> wrote:

>After a while of deleting records in a MySQL db, there gets to be the
>gaps in the id numbering system. i.e. 1, 2, 3, 6, 7, 12, and so on.
>Is there a way to renumber the id system in a table for 1, 2, 3, 4, 5,
>6, etc without manually going in and changing those numbers?

[Back to original message]