Updating AUTO_INCREMENT value of all tables in a MySQL database

user_159346 0
Viewed 55450

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

replication mysql
8 Answers

The Quickest solution to Update/Reset AUTO_INCREMENT in MySQL Database

Ensure that the AUTO_INCREMENT column has not been used as a FOREIGN_KEY on another table.

Firstly, Drop the AUTO_INCREMENT COLUMN as:

ALTER TABLE table_name DROP column_name

Example: ALTER TABLE payments DROP payment_id

Then afterward re-add the column, and move it as the first column in the table

ALTER TABLE table_name ADD column_name DATATYPE AUTO_INCREMENT PRIMARY KEY FIRST

Example: ALTER TABLE payments ADD payment_id INT AUTO_INCREMENT PRIMARY KEY FIRST

user_118687070
Related