Posted by Jerry Stuckle on 05/06/06 15:43

Gleep wrote:
> 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?
>
>

This procedure doesn't work if you have foreign keys. You need to change those,
also.

And yes, having holes in the sequence should not be a problem.

BTW - this should be in comp.databases.mysql - it's not a PHP question.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]