regenerate the ID row in a MySQL Table with a Mysql's Script or PHP

Question

Hello, i have a database fill with information of the users who use my webpage. The table as many MySql tables have the ID parameters who is autoincrement. The issue is that when somebody eliminate his account from the site, in the database remain a jump in the sequence that i dont want cuz i have a script who fail if find some jump in the ID.

Ex.

ID   Name  PASS
1    Jhon  1234
2    Max   2233
3    Jorge 2232

If Max get out and a new user go in, this is what will happend.

ID   Name  PASS
1    Jhon  1234
4    NewU  1133
3    Jorge 2232

So what is the best way to erase some body from the data base in order to avoid this isuue, or if is not a way, its posible to do a PHP or MySql script who eliminate all the contents in the ID row and regenerate it in order? Thanks A lot! sorry for my english

Answer 1

I think not the issue with AUTOINCREMENT, but rather your script.

ALMOST NEVER do you want to restructure autoincrement numbers in a database. That defeats the purpose.

I would rather recomend you work on fixing your script.

This is all kinds of wrong, but from Reset a auto increment field?

Since autoincrement fields are typically used as keys linking to other tables, renumbering existing records is not done often. If you REALLY want to renumber them all, copy the records to a new table but leave off the autoincrement field. TRUNCATE the original table and copy back the original records, supplying NULL for the autoincrement field.

Using this approach, you will also have to manually update all foreign keys, links to these new IDs, which seems like a very big taks compared to fixing your script