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 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.

Tags