how to reset value of auto incremented id to a specific value in mysql? i am migrating data from one db to another. A table is getting populated from combination of 4 tables. I am using stored procedures to populate new table. When i run it first time with some condition(where clause) it populates the table but when i run it second time with another condition, it skips few values in Id(auto incremented).
The following should do what you want where x is a value greater than the maximum value that appears in the column

ALTER TABLE table_name AUTO_INCREMENT = x


The cause of the skipped values is probably either failed inserts (where the insert fails a unique key check or similar) or insert on duplicate update queries both of which increment the auto_increment without creating a row.

If this is a problem for your migration you may be better off creating two copies of your new table table_name and table_name_temp.  Use your stored procedures to populate table_name_temp ignoring the gaps in the auto increment. Once table_name_temp is fully populated you can populate table_name with an insert select query from the table_name_temp of the table.

INSERT INTO table_name SELECT &lt;all columns except the auto increment&gt; FROM table_name_temp;


This will create a table with a continuous auto_increment