You could drop the primary key column and
re-create it. All the ids will then be reassigned, I assume in the order
in which the rows were inserted.
However, I'm not sure why you'd want to do this, especially if you
have other tables that have a foreign key to this table. If so you would
need to update those fields as well, in which case dropping and
recreating the column wouldn't be a good solution. You could instead
remove the autoinc/primary key property, then create a new autoinc
primary key column. You then would have both the old and new ids which
you could use to update other data elsewhere.
you may start you increment through below the statement
alter table foo AUTO_INCREMENT = 1
TRUNCATE TABLE some_table
This will reset the Auto Increment on the table as well as deleting all records from that table
