0

I have one MYSQL table.

In that table there is one column name SRNO is its primary key and auto increment also.

I have around 25000 records in that table but some records between the table is deleted.

For example record no 10 is deleted etc.

I want a SQL script to run mysql to regenerate all auto increment field so that it start from 1 again and end at last record to avoid that missing records.

Thanks

Jeffrey Blake
  • 9,659
  • 6
  • 43
  • 65
air
  • 6,136
  • 26
  • 93
  • 125

1 Answers1

0

This is generally not considered a good idea, as you can seriously mess with referential integrity this way. If this is an aesthetics thing, I'd recommend leaving it as-is. That being said, here's what you do:

  • create a new table with the same schema as the old one, and set your auto-increment to 1.
  • insert all records from the old table into the new one, except for the auto-increment column.
  • rename the original table to something else
  • rename the new table to the original name

this will effectively "reseed" your table.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254