MySQL adjust auto-incrementing id field value
Nov 12, 2020
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:
select
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.