[thelist] MySQL queries, quotes, and AUTO_INCREMENT

Dan CRACIUN dcsquare at myrealbox.com
Thu Apr 6 06:12:04 CDT 2006


Bernardo Escalona-Espinosa wrote:

  > Second problem: has to do with deleting entries of a table when you
> have the primary key as AUTO_INCREMENT.
> 
> Seems that when deleting rows, the primary keys of the deleted rows
> aren't automatically re-used when new rows are inserted. This creates
> major chaos and confusion. I "shifted up" (or down, if you will) the
> remaining rows to close the gap manually. Then when i inserted new
> rows it continued with the old sequence numbering, leaving a gap
> again. This increased the already existing chaos and confusion.

After you finish your operations on that table, copy the table to 
another table (structure+data), drop the original table then copy back 
the table on the old name. You'll have your auto-incremented ID without 
gaps.

Best,
Dan




More information about the thelist mailing list