Updating AUTO_INCREMENT value of all tables in a MySQL database

by

Using:

ALTER TABLE some_table AUTO_INCREMENT = 0

…will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column.

To run this over all the tables, you’ll need to use MySQL’s dynamic SQL syntax called PreparedStatements because you can’t supply the table name for an ALTER TABLE statement as a variable. You’ll have to loop over the output from:

SELECT t.table_name
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.table_schema="your_database_name"

…running the ALTER TABLE statement above for each table.

Leave a Comment