Updating AUTO_INCREMENT value of all tables in a MySQL database

Question

Hello,

It is possbile set/reset the AUTO_INCREMENT value of a MySQL table via

ALTER TABLE some_table AUTO_INCREMENT = 1000

However I need to set the AUTO_INCREMENTupon its existing value (to fix M-M replication), something like:

ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1 which is not working

Well actually, I would like to run this query for all tables within a database. But actually this is not very crucial.

I could not find out a way to deal with this problem, except running the queries manually. Will you please suggest something or point me out to some ideas.

Thanks

Answer 1

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.

Answer 2

Assuming that you must fix this by amending the auto-increment column rather than the the foreign keys in the table decomposing the N:M relationship, and that you can pridict what the right values are, try using a temporary table where the relevant column is not auto-increment, then map this back in place of the original table and change the column type to auto-increment afterwards, or truncate the original table and load the data from the temp table.