Updating AUTO_INCREMENT value of all tables in a MySQL database

Question

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

set @db = 'your_db_name';  SELECT concat('ALTER TABLE ', @db, '.', TABLE_NAME, ' AUTO_INCREMENT = 0;')  FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db AND TABLE_TYPE = 'BASE TABLE' 

Then copy-paste and run the output you get.