MySQL adjust auto-incrementing id field value

You can adjust the value of an auto-incrementing id field in MySQL using the following syntax:

ALTER TABLE {table_name} AUTO_INCREMENT = {starting_value};

But you need to be careful that you use the `max(id)` from that table to avoid hitting an existing id value. To make sure, I use the follow meta-SQL to generate my script:

concat('ALTER TABLE {table_name} AUTO_INCREMENT = ', max(id), ';')
from {table_name};

Simply replace {table_name} with the appropriate table and run the ALTER TABLE SQL that this query produces.

To be precise, the starting value set will be incremented when a new row is added, so it can be equivalent or greater than the largest id value in the table.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store