Search code examples
mysqlsqlidentification

MySQL; How to reset id and set new, start at

The id's in one of my MySQL tables actually start from 34567 (34568, 34569, 34570…).

How can I reset all these id's and set 123 as start value: 123, 124, 125, 126…?

Or is there a way to make every id -34444: 34567-34444=123?


P.S. ALTER TABLE table_name AUTO_INCREMENT = 124; isn't what I'm looking for.

Solution

  • As others have said, you probably shouldn't worry about the values of the ids. By definition, they shouldn't have any real meaning anyway.

    Having said that, you should be able to just update them as you would any other column:

    update table_name
set id = id - 34444

    You could then reset the starting AUTO_INCREMENT value so that the next row inserted has a sequential id:

    ALTER TABLE table_name AUTO_INCREMENT = <whatever the maximum id is + 1>;

    Be aware that doing that will rebuild the table, so it could take a while if it has many rows.