ncw
asked on
Reindex MySql table
I have a MySql table with an auto-increment id field, and in the table I have 4 records that do not change and have ids 1 to 10. The table also contain another 20+ records with id's eg 11-31. The last 20 records will be regularly deleted and re-iniserted with slightly different data, is there anyway of resetting the autoincrement back to the last record id+1, using Php without creating a new table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should reset the autoincrement
$q = "alter table test AUTO_INCREMENT = 2";
$r = mysql_query($q);
$q = "alter table test AUTO_INCREMENT = 2";
$r = mysql_query($q);
Deleted and reinserted with slightly different data? So why not just update the rows instead of all that trouble?
ASKER
Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.
Deleting and reinserting data causes the id to start at the next auto-incremented id unless you recreate the table or alter the auto-increment start value, as far as I know.
Exactly. My point is, why delete and reinsert at all when you can just update the rows to reflect the slightly different data?
ASKER
ALTER TABLE tbl AUTO_INCREMENT = 100;