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
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
It is not recommended that you try to keep your primary keys sequential. It is much better to rewrite your script to not have this dependency. Changing primary keys on your data is a bad idea for both data integrity and performance.
It is not recommended to re-arrange the primary keys in a table. As you have the table is users table. It may be related to another tables by foreign key. There may be reference of user table's primary key in another table. In case you re-arrange primary key in the table you also have to manage all related tables also. So try to fix the bugs in your script.